SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1
One of the major changes in Microsoft Dynamics NAV version 5 SP1 (in relation to performance on SQL Server), is a new way to send queries to SQL Server. In previous versions of NAV, we some times saw SQL Server 2005 optimizing query plans for extreme parameter-values, which - when re-used from cache for queries with other parameter-values - could cause long response time. A behaviour which is described in more details in KB 935395 on PartnerSource (login required). Some of the updates for NAV version 4 introduced new features to give better control of the query plans that SQL Server makes, such as index hints and Recompile-option.
SP1 for NAV version 5 has restructured the way that queries are sent to SQL Server, with the aim that SQL Server will now make query plans that are optimized for average parameter-values rather than extreme parameter-values. It is also a method which lets SQL Server make the plan, without forcing it in a certain direction with index hints or recompile-option.
Before NAV SP1, a typical query could look like this:
declare
@p1 int
set
@p1=180150033
declare
@p3 int
set
@p3=2
declare
@p4 int
set
@p4=1
declare
@p5 int
set
@p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "Demo Database NAV (5-0)"."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_","Posting Date","Entry No_" ',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int', '1105' ,'', '1753-01-01 00:00:00:000' ,0
select
@p1, @p3, @p4, @p5
Notice the parameter-values in bold ( '1105' ,'', '1753-01-01 00:00:00:000' ). SQL Server will make a query plan based on running the query with these parameter-values. It will then cache this plan, and use the same plan for other queries which are identical, but have different parameter-values.
In SP1, the query above will look like this:
declare @p1 int
set @p1=1073741861
declare
@p5 int
set
@p5=12290
declare
@p6 int
set
@p6=8193
exec
sp_cursorprepare @p1 output,N'@P1 varchar(20),@P2 varchar(20)',N'SELECT * FROM "W1500SP1RTM"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_">@P2 ORDER BY "G_L Account No_","Posting Date","Entry No_" ',1,@p5 output,@p6 output
select
@p1, @p5, @p6
And then another query:
declare
@p2 int
set
@p2=180150031
declare
@p3 int
set
@p3=2
declare
@p4 int
set
@p4=1
declare
@p5 int
set
@p5=2
exec
sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''
select
@p2, @p3, @p4, @p5
So before, we had one query. In SP1 we have two! So what's the benefit of that?
If you look at the first query from SP1, notice that it is a sp_cursorprepare statement, and not sp_cursoropen. So the actual query is not run at this point. More importantly, the first query does not contain the parameter-values. This is the query for which SQL Server makes the query plan. Not having the parameter-values, SQL Server makes the plan based on its statistics about the data in the table. Only after this, NAV then executes the statement in the second query (sp_cursorexecute).
This method guarantees that SQL Server's query plan will not be affected by the parameter-values. It means that some times, SQL Server is prevented from making the optimum query plan for a certain set of parameter-values. But remember that the query plan will be re-used for other parameter-values. So at the expense of having a few highly optimized queries, the method will give well optimized queries with better consistensy.
Another cost of this method, is of course that now NAV sends 2 queries instead of 1, requiring an extra roundtrip to SQL Server. But this only happens the first time the query is run. If the same query is run again, NAV will only run the second query (sp_cursorexecute).
One side effect of this is, that tracing a query in SQL Profiler becomes different. With SP1 you will see a lot of queries like the second one above, which does not show what NAV is actually doing. Take a look at the second query again:
exec sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''
With SP1 you will see a lot of queries like this, and then wonder what the actual query is. To find out, you need to use the cursor ID, and then find the original sp_cursorprepare statement, which will contain this line:
set @p1=1073741861
and then the actual query.
In summary, this method is designed to give persistently, good overall performance, and to avoid sudden drops in performance that could be the result of cached query plans on SQL Server.
Lars Lohndorf-Larsen
Escalation Engineer
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.