Making NAV use a certain index on SQL Server

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:

  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!

 

  1. 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

Comment List
  • 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.

  • Well, actually, in NAV terms ... with indexhinting, you can USE SETCURRENTKEY to be sure to take the key you want, despite what SQL Server would suggest.  That's the whole idea of index hinting: SQL Server doesn't take the key you want it to take .. so you hint that key.

    About the two routes: David's suggestion is the way to fix that.

  • IMHO indexhinting will not do the trick for one simple reason: SetCurrentKey is only significant for sorting and not for selection. The index to use therefor does not depend on the active key but on the fields on which is filtered.

    The indexhint option actually gets "in the way" when there are 2 routes to the same screen, i.e. Customer Ledger Entry screen can be reached through the Navigate functionality (where the filter will be on "Document No.") and from the Customer form (where the filter will be on "Customer No."). The resulting screen has the SetCurrentKey set to "Customer No.", "Posting Date" so for the "Document No." route with index hinting to the Customer No. index will still result in a freeze.....

    And still, users are allowed and will use the function to change the Sorting Order in this screen (effectively changing SetCurrentKey) with similar performance impact.

  • Yes of course you are right.

    Its an old trick I used to use in flow fields to force Navision to pick the key I wanted it to use instead of letting it pick the first one it finds.

  • The setrange is not nesesairy because index hint only uses the setcurrentkey.

  • Please let me know the results.

  • That is indeed a way to do it.  Quite a creative tip, thanks.  I will test it out an see what mr. profiler says ;°)

  • You can't create double keys of course, so what I normally do is add a new boolean field to the table, then create a new key including that field, and then filter SETRANGE(MyNewField,FALSE,TRUE);

  • That's indeed a way to avoid that the key is taken for pieces of code you don't want.  But if you want it to use an existing key ... then you'll have to watch out.  As you know, you can't create double keys... .

  • Hey Eric, nicely written, I am sure its not just you two that didn't know about Index Hinting, so i think this blog will help a lot of people.

    Thanks for sharing this.

    By the way, if you have problems with SQL using the wrong key in a specific case (e.g., say a form with lots of records), have you tried creating a NEW key specifically for that situation and sorting the form on that, then apply the Hint just to that new key, so that the hinting will not affect other areas of code?

Related
Recommended