Hi,
I have been informed that to improve performance with Navision on SQL, you can disable keys on tables that are not used. Is there any easy way of finding out which keys are not used?
Regards
Andrew Lees
The short and simple answer is : “If you don’t know, then don’t touch”, you really need to contact your NSC to do this. (Since you don’t have info in your profile, I am guessing you to be an end user, but I may be wrong).
Basically keys serve a number of purposes. On the Navision Database, you can only deactivate (please don’t delete them) keys that are not used, since you will simply get an error if it needs them.
Keys that are used in code can not be deactivated in either database, since the function probably would not work if you did so, although many functions would work, but would just be much much slower.
Keys that are used to define Sum Index Fields can be deactivated in SQL but not in Navision. Since SQL can create the key adn the index on the fly as required. If the key is quite complex, but not used often, or if only the initial part of the key is significant, then the performance gains from not having to create the sum index fields outweighs the peformance loss of recalculating the Sum Index on the fly. Thus deactivating the key can help.
But you really need to know the system design well before you can expect to get big gains out of this.
On a side note, when PC&C originally planned to release version 4 of Navision (that never happened, and it was replaced by Financials). The server was designed with background indexing. So that when a write transaction occured, only the primary key was created. Then when the server was idle, background indexing of secondary keys would occur. Also if a client requested a key that wa not currently indexed, it would also force an indexing. This meant that posting was dramatically faster. Also since a typical posting (say Sales Ship and Invoice of Items) craeted many entries to the same table, a major benefit of indexing was that the calculation of SumIndexes would be bulked, and again faster.
I was very disapointed when this didn’t get released. At the same time, the version was also being designed to be able to use up to 100% commit cache, so basically you could run the database in RAM with the security of Navision’s full Versioning principle. And to top it all off, it would then be possible to export on thread of a version as a backup, and thus have incremental backups, which would have meant having a sequential restore occuring a few minutes behind live. Ahh the good ole days… Hmm I am probably a bit off topic and rambling now… sorry.
Anyway, point is all these wonderfull things that people want out of SQL, were possible in Navision over 10 years ago, they just for some reason never happened.
Or you can re-index the keys regularly in SQL QA.
Disable, not so sure, turn off “Maintain in SQL” maybe.
I think I saw a program that ran through all the object as checked to see if a key was used.
I think you had to export as Text and then import to the program. Think it was on mibuso.
The mentioned tool you could find here: http://www.mibuso.com/dlinfo.asp?FileID=755
But this thing is just investigating the C/AL source code, looking for some properties or SETCURRENTKEY and stuff; thus you could find out which Keys are not used by the code, but this tells nothing about which Indexes SQL Server actually uses!
Key and Index Optimization is a complex and time consuming task and requires deep knowledge of some SQL Server mechanisms. Further, it depends on which version of SQL Server and NAV you use, the utilities are different.
To start, I recommend to use the “MS Dynamics SQL Resource Kit”. With these utilities you can make an analyze of the current key and index structure (incl. SIFT) from NAV site. Look into the tables with a high number of records and high “Costs per Record” first. Compare the NAV keys, sometimes there are the same fields used, just in a different order; mostly you can disable for some of them the property “Maintain SQL Index”.
For tables with a relatively low number of records you could disable all SIFT Levels (“Maintain SIFT” = No). Talking about SIFT, it’s also a good advice to disable all “buckets” except the most detailed one (all other could be generated from this). Especially when it’s about Date-type buckets - you get one bucket per period type - you basically just need the “Day”-level, just in specific cases also the “Month”.
If you use SQL Server 2005, there are statistics about index usage, where you could see which index is used how often. This info then could be used to get rid of the unused stuff.
If you have advanced skills, also when it’s up to program some TSQL scripts, you could REALLY optimze the indexes. E.g. by setting optimized fillfactors on basis of the “data/index growth” within a period (for example: using the “NAV database sizing tool”), moving indexes to a dedicated filegroup, and more.
As mentioned, that’s just for a start … but fiddling with this would require also some trying, changing, replacing, etc…
And of course “Maintenance” is crucial: periodic re-indexing, statistic update, etc…
You’ll find plenty of advice in this forum.
Good Luck!