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.
MVP - Microsoft Dynamics NAV | Certified SQL Perform Consultant
Hi there, I enjoy reading all of your post.
I like to write a little comment to support you.
Very informative post. Thanks dynamicsuser.net for taking the time to share your view with us. wish you luck!
And how to put indextuning in the maintenance plan?
I suppose it is only possible with some SQL-scripts?
FillFactor and Rebuilt(Reindex) are the basics that need to be in every Maintenance plan.
Indextuning is just to make it even faster.
What about indexes FillFactor and rebuilt? What can we gain or lose by playing with them?
Mark, good idea to post about this topic ;°)
Not only the statement 'covering indexes' is clear now, but also a basic principle that if very important to be able to tune a NAV on SQL...
Mark great post, and great information, keep it coming.