Translate index plan advice into proper Dynamics NAV key

greys

Hi there,

I've been suggested by some monitoring tool on SQL Server to add a new key to one of my Dynamics NAV-tables. The suggestion is as followes:

Missing index (impact 18.3375): CREATE NONCLUSTERED INDEX [<Name of Missing Index,sysname,>] ON [TESTDATABASE].[dbo].[Schedule] ([Employee Role],[Duty Option]) INCLUDE ([Employee ID],[Employee Type],[Description],[Active]).

I'm having difficulties translating this into a proper Dynamics NAV 2017-key. Does anyone have a suggestion? I understand that I have to add both fields Employee Role and Duty Option to a new key in the Schedule table. But I'm not sure what to do with the other fields mentioned after the INCLUDE-statement. Do they also have to be included in the key?

Greys

bbrown

The "INCLUDE" part is not directly support by NAV keys. You would need to handle that directly in SQL. Whether or not you want to maintain keys\indexes outside of the NAV development environment is a whole other topic.

Keep one thing in mind with monitoring\analysis tools. They are providing their suggestions based on the current workload. That is the SQL statements currently being executed on the database. They make no determination if that workload is correct. The solution isn't always to add the suggested index. Sometimes it's to adjust the code to improve the workload.

greys

Thanks for your reply. I will first investigate if that workload is indeed expected/correct.

If that IS the case, would it suffice to add a NAV-key on the fields [Schedule] ([Employee Role],[Duty Option] and ignore the fields after INCLUDE?

bbrown

Adding the NAV-key would likely help. The INCLUDE avoids the need for SQL to retrieve the complete record to get the "non-key" fields. Since they are "included" with the index.

Adding indexes(keys) helps reads and hurts writes. Considering how a table is used is important in deciding what (if any) keys to add or disable.