One important area in performance tuning is the setup of keys and indexes. A key in NAV is by default maintaned as an index in SQL Server
Indexes help the user to get data out of the database fast, but needs to be updated as soon as data is changed which cost performance. Therefore index tuning is an art of prioritizing between read and write performance. Indexes defined on big tables and that is never or seldom used are especially interesting since these indexes have a bad cost/benifit ratio (often updated but rarely used). Which keys/indexes that are used differs from installation to installation depending on which functionality that is used and usage patterns.
I’m not going to dig deep into the art of index tuning in this article. Instead I’m going to talk about an easy to use standard feature in NAV that makes it easy to do some basic performance optimization of Your NAV database. I see this as a first step in index tuning that can be performed before calling in the experts on performance tuning.
You can scale high with Dynamics NAV if run it on a well designed hardware, use the application functionality with performance in mind (dimensions, change log and such) and do some basic index tuning with Key Groups. If You do this and also set up some basic index and statistics maintenance in SQL Server You have come a long way.
Key Groups is a standard feature that’s often overseen when it comes to setup and performance tuning of Dynamics NAV.
Key Groups are collection of keys grouped together in functional areas. Microsoft has started to use Key Groups more in later versions to make it easier to choose which keys/indexes that should be activated. Unfortunately there is no documentation about the Key Groups avaliable from Microsoft so this blog post is somewhat an attempt to compensate that fact.
I did a study on a Swedish 5.0SP1-database and there where 80 keys marked with key group which mean that these keys can be activated/deactivated by changing the status of the corresponding key group. One might argue that it’s safer to just inactivate the SQL index and leave the key enabled in NAV to ensure the functionality in NAV. That’s for You to decide, but Key Groups are easy to use, self documenting and can be managed with a customer license. If You deactivate a group that later on is needed it’s just a matter of activating the Key Group again. But as allways: TEST Your functionality in a test environment before running off to deactivate Key Groups in Your database.
How to do it
If You choose File -> Database -> Information and press ”Tables” You get a list of all the tables in the database (that list is also valuable information. If You copy and paste all rows in Excel and sort on No. of records You get a good picture of where Your data is).
In the table list You press ”Key Groups” and get this list
By pressing ”Enable” or ”Disable” You change the corresponding keys. The abbrivations give some hints of the purpouse of the keys but there’s no description of which keys are involved
Functional areas by Key Group
The table below describes in which areas the keys in each key groups are used.The Active/inactive flag is a bit tricky. I often see that all key groups are active in a database, but when I look at the keys that are marked with a key group they can be disabled. The information above about active/inactive are because of that based on the actual keys. It could also be that You are missing Key Groups completely. Then just add them to the table, but remember that the list below is from SE5.0SP1. If You are using an older version all of these Key Groups does not exist as properties on the keys in Your database.
Keys in Key Groups
Which keys that belong to which Key Group is described in this document.
Take a look at the keys in the group Dim(Setup). Those are keys on really heavy tables and honestly; how often do You really rename a dimension? Those keys are only used when renaming dimensions. Otherwise they only hurt performance.
So what have we learned?
Keys/indexes that You don’t need should be disabled. An easy way to do this is by using Key Groups. Use the list above and a test database to disable the key groups that You don’t need and test run Your processes.
If You choose to use the table designer and leave the keys enabled to NAV but disable the maintenance of the corresponding index in SQL Server, You can use this information to find which keys You should work on.