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.

Comments (6)

  1. This post is the first in a planned series to describe various "modern" methods for troubleshooting performance

  2. This post is the first in a planned series to describe various "modern" methods for troubleshooting

  3. fabio78 says:

    Hi,

    I’ve upgraded my Navision installation from 4.0 sp3 to 5.0 without performance issue, then from 5.0 to 5.0 sp1 with very very performance issue…

    I’ve read that 5.0 sp1 uses Index Hint by default, but by trace from my Database I don’t see any hint.

    Can this issue caused by new sql Pre-Processing way to execute queries?

    From sys.processes I’ve also seen that there are a lot of processes waiting for pageiolatch_sh, but with 5.0 I don’t have waiting for resource…

  4. Hello fabio78

    5.0 SP1 does not use index hint by default. It is difficult to say if your performance issues are caused by updating to 5 SP1. One reason could be, if you are running on SQL2000? Then check this recent blog:

    http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/06/03/microsoft-dynamics-nav-5-0-sp1-and-sql-server-2000.aspx

    If you are running on SQL2005, then please let me know a few more details about your performance issues, and I will see if I can think of anything,

    Lars

  5. Kris Manche says:

    Hi,

    Straight forward executable upgrade from 4.0.2 to 5.0.1, performance has degraded. WTF?

    Using SQL 2005 Ent.

    Nothing else has changed except the Executable upgrade. Have done this before without issue, this is the first time performance went backwards. Seems to mostly have been affected in add-ons.

    Thanks in Advance

Skip to main content