Navision Planet

Dynamics NAV(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.

Run Optimize function

This is the easiest thing you can do. The Optimize function will drop and rebuilt indexes for your tables. SQL Server performance can very much affected by the Index. 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 these specialist. There are the specialist in Navision-SQL Server performance troubleshooting. I cannot comment much on their service as I never tried before. I am lucky enough to get my Navision back to an acceptable speed after some optimazation and reindexing.
For More Information Visit: SQLSunrise

SumIndexFields and SQL Server
When you add SumIndexFields to a key in Navision table, Navision will create additional table in SQL Server, which is known as SIFT table to store the pre-calculated value for the SumIndexFields. SIFT table is a term used in Navision. In fact, the SIFT table is just an ordinary table in SQL Server. The SIFT table is named with the following naming convention:

Cronus$17$0

Cronus$17$1

Cronus$17$2

Cronus$17$3

If order for Navision to maintain a SIFT table for a key, the key must meet the following 3 criterias:

1) Enabled

2) Contains SumIndexFields

3) MaintainSIFTIndex is selected

If either one of these criterias is not met, Navision will not maintain a SIFT table for the key. Now, let's do a very quick experiment. I am going to disable the MaintainSIFTIndex option for the 2nd SIFTIndex key and see what will happens to the SIFT tables in SQL Server.

Once I disabled the MaintainSIFTIndex option and save the table, one SIFT table has been removed from the SQL Server.

Please note that I diabled the MaintainSIFTIndex option for the 2nd SIFTIndex key but the SIFT Table that removed by Navision is Cronus$17$3, which was originally the SIFT table for the 4th SIFTIndex key. This shows that the internal id in the SIFT Table naming will always in sequence. Let's have a look at the SIFT table's structure that I captured before and after disabling the MaintainSIFTIndex option.

Cronus$17$1 and Cronus$17$2 before disabling MaintainSIFTIndex option

Cronus$17$1 and Cronus$17$2 after disabling MaintainSIFTIndex option

After disabling the 2nd SIFTIndex key, SIFT Tabel for the 2nd SIFTIndex key has been removed. The 3rd SIFTIndex key has been created as Cronus$17$1 while the 4th SIFTIndex key has been created as Cronus$17$2. I am not sure whether Navision has recreated the SIFT tables or just rename them.

SIFT helps in returning summed values very fast but it will slow down data insertion and modification. Whenever you do an INSERT, UPDATE or DELETE on tables with MaintainSIFTIndex enabled, Navision (more specifically, triggers in SQL Server table) will need to update the SIFT tables. This will add a lot of overhead to the server, which will cause slowdown the server performance. If you disable the MaintainSIFTIndex option, Navision will not give you error. If the MaintainSIFTIndex option is not enabled, Navision will calculate the value from the source table with the SELECT SUM sql statement. You system will still work. So, how ? Is that means we cannot enable the MaintainSIFTIndex option in Navision SQL Server Option ?
The answer is No. To get the maximum benefits from the SIFT technology, you can to enable the MaintainSIFTIndex selectively. Using SIFT tables can improve response time significantly on source tables that contain many records. However, on tables with not many records, the response time is similar as you are calculating it from the source table. As a general rules of thumb, do not maintain SIFT Indexes on small tables and temporary tables (eg. Sales Line, Purchase Line, Warehouse Activity Line, etc.) because it is not worth the additional overhead to maintain the SIFT tables but not getting significant performance improvement on small tables.