Thursday, 7 February 2008

Navision Slow on SQL Server

Many users experienced Navision getting slower and slower as time goes by. There are many factors that can affects performance of Navision on SQL Server. It can be hardware bottleneck, database design problem or even coding design problem. Below are the few areas that you can look into if your Navision on SQL Server is getting slower.

1) Run Optimize function
This is the easiest thing you can do. The Optimize function will drop and rebuilt indexes for your tables. Index has very great impact on SQL Server performance. If you experienced slow in Navision but never do optimization, do it as soon as possible. Don't wait. The Optimize function can be accessed from File --> Database --> Information. Select the table that need to be optimized and click on the Optimize button.

The Optimize function will rebuilt the keys in the selected table and all the related SIFT Tables. Besides rebuilding indexes, the Optimize function will also remove all records with zero values in all the sum fields in SIFT tables. This can free up space and improve speed in updating SIFT records.

I have seen people complaining Navision is very slow but when I asked them, have you ever run the Optimize function from Navision or do any SQL Server Reindex. Surprisingly, they said we never run the Optimize function and we never do SQL Server Reindex since day one we implement Navision. I asked them to schedule a downtime and run the optimize. After run the Optimize function, they find the performance improve significantly.

Interesting Forum discussions on Navision slow on SQL Server:
Mibuso - slow navision on SQL


Navision-SQL Server Performance Specialist
If you really cannot solve your Navision SQL Option slow problem after all efforts, maybe you can consult this specialist. There are the specialist in Navision-SQL Server performance troubleshooting. I cannot comment much on their services as I have never tried. I am lucky enough to get my Navision back to an acceptable speed after some optimazation and reindexing.
http://www.sqlsunrise.com/

3 comments:

ganix said...

My Server is NAV 5.0 and MSSQL 2005 Developer Edition.
Database size is 24 GB.
How to Optimizing mssql 2005

Help us!

wan said...

I guess optimize function for Nav 4 and Nav 5 are the same. You can follow the suggested step as in Nav 4.

Anonymous said...

Ӏ really like yоur writing style, ցreat info, thɑnks for posting
:D.