Performance in SQL

xavigepe

Hello. I have a customer working with Navision 4.0 SQL version for one year and a half. When database has become larger some tasks are getting too slow (such as displaying inventory in item's card). Is Optimize the only way to improve performance in SQL?. Is there any other options or whitepapers to read about?.

Thanks so much,

rajpatelbca

hi,

look at this link you might get some idea.

http://www.mibuso.com/forum/viewtopic.p ... erformance

bbrown

Search this site. You will find lots of advice/discussion on the subject. If you still have questions then try to post more specific questions. This is too broad and complex an issue.

As a short answer, yes there is more that can be done. These range for hardware configuration to code design to database maintenance.

xavigepe

Thanks so much rajpatelbca, in the link there are some ideas I should use.

xavigepe

Most performance problems occur in the sales order form when changing from one order to the next one (clicking the NEXT navigation button). We don't have additional code when changing record. How can I check what is happening? Could be code coverage useful?

Thanks,

davmac1

If they are on maintenance they can upgrade to NAV 5 SP1 executables.
You can fo through the SQL database documentation from Microsoft which has some great tips.
Plus you could pay one of the SQL Server experts in Europe to spend some time with you and show you how to tune their site.

kine

Codecoverage is not useful in this. You need to use Client Monitor to see what is going on.But first, try to ead through some documentation like Performance Troubleshooting guide and some posts on this forum...

David_Singleton

xavigepe wrote:

...We don't have additional code when changing record. ...

It does not need to be 'Code" per se, it could be flow fields or a number of other things.

Dmytro_Sitnik_[MSFT]

xavigepe wrote:

Hello. I have a customer working with Navision 4.0 SQL version for one year and a half. When database has become larger some tasks are getting too slow (such as displaying inventory in item's card). Is Optimize the only way to improve performance in SQL?. Is there any other options or whitepapers to read about?.

Thanks so much,

You should consider upgrading to 5.0 SP1. It has a lot of performance improvements for NAV SQL.

jlandeen

I've seen this become a problem several times and if upgrading to the latest & greatest version is not possible I would certainly look at the number of flowfields that are being displayed on the form.

As all of the those flowfields have to be calculated at run time there's a lot of summing going on over a lot of data to perform those calculations and update the form when you move from record to record. In the past i've pulled all of the flow fields off the form and put them onto a seperate form (like a statistics form) that the user has to click a button or function key to open. This allows users to switch between simple sales order forms easily and then if they need more details they can easily get to them.

Even if this helps it's still important to run regular maintenance so that the keys and SIFT tables are optimized.

David_Singleton

"Dmytro wrote:

"]

xavigepe wrote:

Hello. I have a customer working with Navision 4.0 SQL version for one year and a half. When database has become larger some tasks are getting too slow (such as displaying inventory in item's card). Is Optimize the only way to improve performance in SQL?. Is there any other options or whitepapers to read about?.

Thanks so much,

You should consider upgrading to 5.0 SP1. It has a lot of performance improvements for NAV SQL.

If the issue is flow fields, then wont 5.0 SP1 be slower?

Marije_Brummel

"Dmytro wrote:

"]

You should consider upgrading to 5.0 SP1. It has a lot of performance improvements for NAV SQL.

SP1 is definately NOT faster in all scenario's

We did some deep testing for customers who were considering.

Problem is that SP1 has VSIFT which is a dramatic change.

It is much better and recommended (by me ) to move to the last 4.0 SP 3 runtime which has the bugfix for the index scans.

Al of the rest can be solved with normal SQL tuning.

In fact I want to make a bold statement and request: Ractivate the sift levels for the indexed views! that way we are much much more flexible and some flowfields which are causing nightmares now will be back fast.

davmac1

according to the NAV blog site, SQL Server 2005 will have much better performance than SQL Server 2000.
Does this hold true in your testing?

kine

Yes, definitely, mainly if running on 64bit... but sometime it is hard to compare, because migration to MS SQL 2005 are in common connected to migrating to new HW with more RAM and CPU and better HDDs... (and you need to take into account different requests for optimizing the indexes...)

kriki

[Topic moved from Navision forum to SQL Performance forum]

Marije_Brummel

davmac1 wrote:

according to the NAV blog site, SQL Server 2005 will have much better performance than SQL Server 2000.
Does this hold true in your testing?

As Kamil sais the performance gain is usualy caused by SQL2005 x64 having access to more resources.

SQL2000 and SQL2005 x86 does not have much difference. 2005 can even cause unwanted features in index scanning.

bbrown

Mark Brummel wrote:

davmac1 wrote:

according to the NAV blog site, SQL Server 2005 will have much better performance than SQL Server 2000.
Does this hold true in your testing?

As Kamil sais the performance gain is usualy caused by SQL2005 x64 having access to more resources.

SQL2000 and SQL2005 x86 does not have much difference. 2005 can even cause unwanted features in index scanning.

What about SQL 2000 x64 vs. SQL2005 x64?