Making NAV use a certain index on SQL Server

Waldo

You can read this on my blog as well: http://dynamicsuser.net/blogs/waldo/archive/2007/08/21/making-nav-use-a-certain-index-on-sql-server.aspx

I'm ashamed that I did not know about this feature until for a few weeks ago. Why? Because it has been in the bible for a long time (fyi, "the bible" is "The Application Designer's Guid" aka the w1w1adg.pdf on the \doc folder on your CD). How long? I've seen it in the ADG of 3.10 … so it's been there for a long long time. Just see Chapter 30.6. Hynek Muhlbacher brought this to our attention when Mark (Brummel) and I were working on a tuning.

Anyway … I just wanted to let you know that it exists … that it works … and that you can have very positive AND very negative results with it. I'll try to explain.

There are two ways for using Index Hinting with Dynamics NAV:
1.
Use the default Plan Guides that come with SQL Server 2005. This is not something you want (as NAV Developer who likes to "KISS"). This is quite hard to maintain:
You need to know which combination of query and parameters to implement it for
This is an example of the syntax:

use [NavisionDB]

exec sp_create_plan_guide

@name = N'CustLedgerEntry_Guide1', 

@stmt = N'SELECT * FROM "[NavisionDB]"."dbo"."[CompanyName]$Cust_ Ledger Entry" WHERE (("Customer No_"=@P1)) AND "Customer No_"=@P2 AND "Posting Date"=@P3 AND "Currency Code"=@P4 AND "Entry No_">@P7 ORDER BY "Customer No_","Posting Date","Currency Code","Entry No_" OPTION (FAST 10)',

@type = N'SQL',

@module_or_batch = NULL, 

@params = N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 varchar(10),@P5 varchar(20)',

@hints = N'OPTION (RECOMPILE)'

Good luck - I'm not using this!

2.
You als got the NAV-way to do it. Simple and fairly straight forward:
Create a table "$ndo$config" with this statement:

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)

GRANT SELECT ON [$ndo$dbconfig] TO public

Insert the index hints with this syntax (this is an example):

INSERT INTO [$ndo$dbconfig] VALUES

('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item

Ledger Entry";Key="Item No.","Variant Code";Search Method="-

+";Index=3')

The index syntax is:

IndexHint=<Yes,No>;Company=<company name>;Table=<table

name>;Key=<keyfield1,keyfield2,...>; Search Method=<search method

list>;Index=<index id>

For the key fields: you have to sum up all key fields that you want the use. I.e. the NAV key you want to use for sorting your data (SETCURRENTKEY).

For further information I would like to forward you to the ADG.

Now you might wonder why and when you need this, and that is very hard to say. When you're doing a performance analysis, you check the SQL statements in the profiler and it's execution plans. Now, sometimes, you notice that SQL Server just doesn't take the correct index, and no matter what index you create, and how many statistics updates you do, SQL just won't take that index. Plus, the results in the profiler are absolutely ridiculous. Well, then you can try index hinting. And when you see it gets the right index, and that the results are better, you succeeded.

BUT

There is a warning I have to add. By using many index hints, you're going to "simulate" native behaviour. Why? You're going to use the same key as you specify in your "SETCURRENTKEY" statement. Every time you're in that company, for that table, and using that sorting, SQL will take that key, no matter what your filters are… .

So beware of this.

Sources:
Application's Designer Guide / Hynek Muhlbacher / Mark Brummel