NAV SQL Tuning - Covering Indexes
SQL and NAV is hot these days. More and more knowledge becomes available and more companies and freelancers start helping customers tuning their databases.
Most of the problems exist in "older" databases. Especialy early version 3 databases have difficulties performing.
Fortunalely version 5 of NAV is very much pre-tuned on SQL so you are not likely to run into big issues until the database reaches a big size or some customisations come into business.
This blog is not about what you can do with indexes, maintenance or code changes but specificaly about "covering indexes". Something not very heard of in the NAV world.
What is a Covering Index; A covering index means an index that includes all columns in a specific query. In NAV that would mean the requested fields in the Where Clause and the Order by clause, and in some cases the Select clause.
In NAV (and AX) almost al queries are transformed into a Select * from... This means that SQL has to return all values in the table instead of only the required columns, resulting in a bookmark lookup to the Clustered index for every query. Every query? No.
There are some exeptions to this.
1. Select TOP NULL From...
This query is the result of an ISEMPTY statement in C/AL. If you are only interested in if a record is there but not in the contents this is your friend. An example in standard NAV is Reservation Management where this is used. Unfortunately this has to be tuned as there is only one C/AL statement for both Trade Documents, Journals and Manufacturing.
Reports also tend to create TOP NULL Queries
2. Select COUNT (*) From
This statement is generated by the COUNT statement in C/AL. If there is a covering index for all of the fields in the filter you can speed up this statement.
3. & 4 Select SUM
This is generated from either FLOWFIELDS or CALCSUMS in NAV. When there is a SIFT table for the SUM it will generate a SUM on this table, otherwise it will generate a SUM on the actual table.
When we go to a customer, one of the steps in the tuning process it SIFT tuning. SIFT tuning is a real give-or-take process where you need to fine-balance for read and write performance. Sometimes you just cannot disable a SIFT level.
A solution can be a covering index on a SIFT Table.
Good luck on your Tuning Projects.
Mark Brummel
MVP - Microsoft Dynamics NAV | Certified SQL Perform Consultant
More information
http://www.sql-server-performance.com/covering_indexes.asp
http://www.sqlskills.com/blogs/kimberly/