Translate index plan advice into proper Dynamics NAV key

greysgreys Member Posts: 27
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

Answers

  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • greysgreys Member Posts: 27
    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?
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
Sign In or Register to comment.