I have seen a number of (good) arguments that some issues with Dynamics NAV on SQL2005 are caused by a bad query optimizer in SQL2005, and that the SQL teams need to fix this. After all, some issues we see on SQL2005 did not exist in SQL2000. This blog tries to argue that SQL2005 is maybe more clever than SQL2000, and tries to give some input into the discussions about whether SQL2000 is more clever than SQL2005, and whether there is anything to fix in SQL2005 regarding this specific issue.
The scenario belows is the situation where the reuse of a cached query plan causes a Dynamics NAV client to hang while it is browsing forms.
Here is a repro scenario which will show why Dynamics NAV ends up causing a clustered index scan on SQL2005, while the same scenario on SQL2000 did not cause any such scans. It is based on a W1 5.00 demo database, and it requires a Solution Developer's license to run it. Run the steps on a SQL2000 and SQL2005 database and you will see where the differences are between these two platforms:
1. Create a new G/L Account, No 11052. Create 50.000 new records in table 17. For this purpose, it doesn't matter if you post these entries or just create a codeunit to insert the records.3. Run table 17 from Object Designer, and change the G/L Account No. to 1105 for the first 3, and the last 3 entries4. On SQL Server, update statistics on this table:update statistics [CRONUS International Ltd_$G_L Entry]5. Run Dynamics NAV with Maximized forms.6. In Dynamics NAV, go to "Chart of Accounts" and drill down on the new account 1105 and you should see 6 entries. Make sure to place the cursor on the first entry. Then close the drill-down to go back to the "Chart of Accounts".7. On SQL Server, run DBCC FREEPROCCACHE. This will clear out any cached query plans.8. Start a profiler trace - include the following events (on top of the default ones) On SQL2005: Performance:Showplan Text, on SQL2000: Performance:Execution Plan Stored Procedures:SP:CacheHit Stored Procedures:SP:CacheInsert9. In Navision, drill down on account 1105. Then move the cursor with arrow-down, until you get to the last entry. Then move back up to the top again with arrow-up.10. Stop the profiler trace.
On SQL2005, you should see one of the last entries causing a relatively large number of reads. In my tests 2079 reads. This is the offending query. The same query on SQL2000 causes much fewer reads. In my tests 126 reads.
The query looks like this:SELECT * FROM "W1500"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_"<@P4 ORDER BY "G_L Account No_" DESC,"Posting Date" DESC,"Entry No_" DESC ',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int','1108','1108',''2007-12-31 00:00:00:000'',52761
Notice that the last parameter value is 52761. So the part of the query to focus on here, in fact reads:WHERE "Entry No_" < 52761
Then take a look at the execution plan. SQL 2005 uses the index [CRONUS International Ltd_$G_L Entry$0], which is the clustered index ("Entry No_"). SQL2000 uses the index [CRONUS International Ltd_$G_L Entry].[$1], which is the index which begins with "G_L Account No_". So based on this query it is not strange that SQL2005's plan is causing many more reads that SQL2000's plan.
Here is an important point to make: Neither SQL2000 or SQL2005 compiled the query plan for this query. You can see by the presense of SP:CacheHit events in the profiler trace, that the plan was taken from the plan cache. So in order to find out why the two versions of SQL makes different plans, we need to go to the place where the plan was made.
Go to the SP:CacheHit event and look at the data. Then go backwards in the trace until you find the SP:CacheInsert event with the same data. This is the place where the query plan was made. The query in this place looks like this:
SELECT * FROM "W1500"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_"<@P4 ORDER BY "G_L Account No_" DESC,"Posting Date" DESC,"Entry No_" DESC ',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int','1108','1108',''2006-12-31 23:59:59:000'',1
This time, the last parameter value is 1 (not 52761)! So this time, the part of the query to focus on is:WHERE "Entry No_" < 1
Remember that "Entry No_" is also the clustered index.
So here is the question: What is the best possible query plan for this query? And I think the answer is easy for this scenario: Use the clustered index to "scan" this one record! The number of Reads in the trace should also confirm this. In my tests, SQL2005 did 21 reads. SQL2000 did 245 Reads.
So in this case, SQL2005 makes a better plan than SQL2000!
The way that query plans are cached and reused has not changed between SQL2000 and 2005. The following points are valid for both versions:
1. When a query plan is designed, SQL will take the parameter values into consideration (In this example, whether the last parameter is 1 or 52761). This is also called parameter sniffing.2. When a query plan is reused from cache, the parameter values are NOT taken into consideration. The Query that the plan is valid for is converted into a hash-value. SQL simply looks in the plan cache if a plan exists for that hash-value, and then reuses the plan if there is. If SQL also had to revalidate the plan against the current parameter values, then this would to some extend negate the whole purpose of reusing cached plans (performance).3. SQL's query optimizer does not have any kind of risk-assessment when it designs a query plan. There are no mechanisms in place to consider "If I put this plan into cache, and it was reused with other parameters, what is the potential damage?"
These behaviours are fundamental to current and previous version of SQL, and most likely to future versions as well.
So, for this scenario we can see that: - When the plan was made, SQL2005 made the most optimized plan. - The behaviour of caching plans and reusing them are the same on both SQL2000 and SQL2005.
Without going into too many details here about how to troubleshoot a situation like this, there are various ways to handle it. The main methods for Dynamics NAV are:
- Index hints:
In this situation, if the query had included an index hint on the $1 index ("G_L Account No_"), then SQL2005 would not have chosen the clustered index as it did. The behaviour would have been like on SQL2000, and the problem query (2079 reads) would not have happened. For more details about Index Hinting in Dynamics NAV, check thhe blog "Index Hinting in Platform Update for Microsoft Dynamics NAV 4.0 SP3 KB940718".
- Recompile hints
Adding a Recompile hint to a query is a way to tell SQL Server to make a new query plan, and not take one from cache. In this way you may get query plans that are better optimized for the current parameter values, but it also adds an overhead to SQL Server because making a new query plan always takes longer than re-using a cached one.
On SQL Server, you can use index hinting to force the server to use a particular index when executing queries for FINDFIRST, FINDLAST, FINDSET, FIND('-'), FIND('+'), FIND('=') and GET statements. Queries generated by the form runtime will be affected by index hints in the same way.
Index hinting can help avoid situations where SQL Server’s Query Optimizer chooses an index access method that requires many page reads and generates long-running queries with response times that vary from seconds to several minutes. Directing SQL Server to use a specific index can give instant 'correct' query executions with response times of milliseconds.
In Microsoft Dynamics NAV, index hinting is turned on by default and the application automatically uses this functionality to improve performance.
If you need to switch off or customize index hinting to fit your implementation, you must create a SQL Server table to store the configuration parameters. The parameters you enter into this table will determine some of the behavior of Microsoft Dynamics NAV when it is using this database.
In the database create a table, owned by dbo:
CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
GRANT SELECT ON [$ndo$dbconfig] TO public
(You can add additional columns to this table, if necessary. The length of the config column should be large enough to contain the necessary configuration values, as explained in the following, but need not be 512.)
The default value is IndexHint=Yes.
You can disable index hinting at any level of granularity.
There are two ways of using index hinting in your application:
· You can leave index hinting turned on and disable it in specific places.
· You can turn off index hinting and enable it in specific places.
Index hinting has been shown to optimize performance in the following scenarios:
1. Index hints prevent SQL Server from using an out of date query plan, such as a clustered index scan.
2. Index hints prevent SQL Server from scanning smaller tables and escalating locks to table locks.
SETCURRENTKEY must correspond to the filter that you want to place on the table.
GLEntry.SETCURRENTKEY(GLEntry.AccountNo);//Should be added to the code if not already presentGLEntry.SETRANGE(GLEntry.AccountNo,’1000’,’9999’);GLEntry.FINDSET();
Example: In the following C/AL code, index hinting is turned on but SETCURRENTKEY has not been used:
GLEntry.SETRANGE("G/L Account No.",'2910');GLEntry.FINDSET;
This will generate the following SQL query:
SELECT TOP 500 * FROM "W1403"."dbo"."CRONUS International Ltd_$G_L Entry" WITH (READUNCOMMITTED, INDEX("CRONUS International Ltd_$G_L Entry$0")) WHERE (("G_L Account No_"=@P1)) ORDER BY "Entry No_" ','2910'
Note that without a SETCURRENTKEY, Microsoft Dynamics NAV will hint the SQL index which corresponds to the primary key in the G/L Account table. This is not the best key to use for this query.
Conversely, in the following C/AL code, hinting is turned on and SETCURRENTKEY has been used:
GLEntry.SETCURRENTKEY("G/L Account No.");GLEntry.SETRANGE("G/L Account No.",'2910');GLEntry.FINDSET;
SELECT TOP 500 * FROM "W1403"."dbo"."CRONUS International Ltd_$G_L Entry" WITH (READUNCOMMITTED, INDEX("$1")) WHERE (("G_L Account No_"=@P1)) ORDER BY "G_L Account No_","Posting Date","Entry No_" ','2910'
Now, because the C/AL code specifies which key to use, Microsoft Dynamics NAV hints the corresponding index from the code, which ensures that the right index is always used.
If you turn index hinting off, Microsoft SQL Server will define the index automatically.
Index hinting is turned on by default but you can disable index hinting for a specific company, table or index.
Here are a few examples that illustrate how to disable index hinting by executing a statement in query analyzer:
INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=No;Company="CRONUS International Ltd."’)
('IndexHint=No;Company="CRONUS International Ltd.";Table="Sales Header')
('IndexHint=No;Company="CRONUS International Ltd.";Table="Sales Header";Index="1"')
As mentioned earlier, you can disable index hinting for the entire system and then enable it where appropriate for your application.
The index hint syntax is:
IndexHint=<Yes,No>;Company=<company name>;Table=<table name>;Key=<keyfield1,keyfield2,...>; Search Method=<search method list>;Index=<index id>
Each parameter keyword can be localized in the "Driver configuration parameters" section of the .stx file.
The guidelines for interpreting the index hint are:
• If a given keyword value cannot be matched the entry is ignored.
• The values for the company, table, key fields and search method must be surrounded by double-quotes to delimit names that contain spaces, commas etc.
• The table name corresponds to the name supplied in the Object Designer (not the Caption name).
• The Key must contain all the key fields that match the required key in the Keys window in the Table Designer.
• Search Method contains a list of search methods used in FIND statements:
• The index ID corresponds to a SQL Server index for the table: 0 represents the primary key; all other IDs follow the number included in the index name for all the secondary keys. Use the SQL Server command sp_helpindex to get information about the index ID associated with indexes on a given table. In this example we are looking for index information about the Item Ledger Entry table:
sp_helpindex 'CRONUS International Ltd_$Item Ledger Entry'
When Dynamics NAV executes a query, it checks whether or not the query is for the company, table, current key and search method listed in one of the IndexHint entries. If it is, it will hint the index for the supplied index ID in that entry.
• If the company is not supplied, the entry will match all the companies.
• If the search method is not supplied, the entry will match all the search methods.
• If the index ID is not supplied, the index hinted is the one that corresponds to the supplied key. This is probably the desired behavior in most cases.
• If the company/table/fields are renamed or the table's keys redesigned, the IndexHint entries must be modified manually.
Here are a few examples that illustrate how to add an index hint to the table by executing a statement in Query Analyzer:
('IndexHint=Yes;Company="CRONUS International Ltd.";Table="Item
Ledger Entry";Key="Item No.","Variant Code";Search Method="-
This hint will use the $3 index of the CRONUS International Ltd_$Item Ledger Entry table for FIND('-') and FIND('+') statements when the Item No.,Variant Code key is set as the current key for the Item Ledger Entry table in the CRONUS International Ltd. company.
('IndexHint=No;Company="CRONUS International Ltd.";Table="Item
The index hint entry is disabled.
This will hint the use of the Item No.,Variant Code index of the CRONUS International Ltd_$Item Ledger Entry table for FIND('-') and FIND('+') statements when the Item No.,Variant Code key is set as the current key for the Item Ledger Entry table in the CRONUS International Ltd. company.
This is probably the way that the index-hinting feature is most commonly used.
('IndexHint=Yes;Company=;Table="Item Ledger Entry";Key="Item
No.","Variant Code";Search Method="-+";Index=3')
This will hint the use of the $3 index of the CRONUS International Ltd_$Item Ledger Entry table for FIND('-') and FIND('+') statements when the Item No.,Variant Code key is set as the current key for the Item Ledger Entry table for all the companies (including a non-company table with this name) in the database.
Ledger Entry";Key="Item No.","Variant Code";Search Method=;Index=3')
This will hint the use of the $3 index of the CRONUS International Ltd_$Item Ledger Entry table for every search method when the Item No.,Variant Code key is set as the current key for the Item Ledger Entry table in the CRONUS International Ltd. company.
Note: It requires a developer license to change C/AL code in Microsoft Dynamics NAV. It is highly recommended to have the changes evaluated and implemented by a Microsoft Dynamics NAV partner.