This is going to be an article about "Index Hinting".
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:
@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!
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
GRANT SELECT ON [$ndo$dbconfig] TO public
INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item
Ledger Entry";Key="Item No.","Variant Code";Search Method="-
name>;Key=<keyfield1,keyfield2,...>; Search Method=<search method
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.
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.
Application's Designer Guide / Hynek Muhlbacher / Mark Brummel
I think using sp_create_plan_guide with OPTION (RECOMPILE) may be the best solution.
SQL don't select the right index because of plan cache and/or OPTION (FAST xx).
In sql2005 the recompile is more selective than in sql200 and that may be one of the reasons for this problem.
Using Index Hints will forse SQL to use ineffective index.
In my NAV 4.03 running sql2005 I have found 18 selects with bad performance because of wrong index. After insert of selects in sp_create_plan_guide performance is fine.
I this way you keep recompile on a very low level.