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/
This is a very common question these days, mainly because of the "big" 5.1 release scheduled end of this year.
Many of us have been looking forward to having a more fancy looking client for a long time, especialy sales people since other ERP products are starting to become mory flashy.
For a lot of old NAV users, a direct upgrade to the new 5.1 client can be a step that is just a little to big to take.
So why not upgrade in smaller steps. Since the "roletailored" client (that is what they call it these days) requires a SQL engine that may seem like the obvious first step. If you are running on NAV 4.0 SP1 or higher that can be a serious option. However there is a better plan.
NAV 5.0 is one of the best releases to run on SQL server. There are many C/AL, SIFT and index changes in the base product to avoid the bacis errors we see from day to day. So doing an "old fashion" upgrade to 5.0 on C/SIDE can actualy be the most obvious step to take.
The choice of directly moving to SQL server is up to you as an end user. If you think it is to big a risk, then maybe a go-live on C/SIDE can be more wise. After running OK for some months you can safely move to SQL with C/SIDE as a failover.
So now we are running on SQL with NAV 5.0. Are we ready to move to 5.1. YES. From here it is not such a big step anymore. Please keep in mind that 5.1 still has a number of formchanges in order for the transformation tool to be able to transform.
Please keep in mind while you are upgrading to 5.0 that you might want to move back to using the standard NAV forms in order to quickly migrate to the new page object. You might also want to make your own forms more gui guidelines compliant since the transformation tool is based on the gui guidelines.
Does upgrading to 5.0 has other benefits: Yes, definately. 5.0 is supported by Vista. New PC's at your office are pre-installed with this operating system. Also 5.0 has some very fancy office-xml and sharepoint features out of the box.
Good luck with making your decision.
Mark Brummel
Liberty Grove Software