Microsoft has released Cumulative Update Package 7 for SQL2008 SP1 (KB797065). In this update there is a new feature (trace flag 4136) that let us control wether or not the Query Optimizer should do parameter sniffing (KB980653).
You may remember all the debates on the forums about index hinting and parameter sniffing in NAV 4.0 when SQL2005 was introduced. This was a big headache for the NAV Development Team and they tried different approaches to solve this communcation problem with SQL Server. The latest we got in NAV is pre processing that prepares the cursor to optimize it for an unknown where clause which should speed it up.
With Update 7 it looks as we can get rid of this problem. But does it really have any effekt on performance? To get a first opinion on this I performed a tiny little test on a standard 2009 Cronus database on my laptop. I know there’s not a lot of data in this database, but at least it gives us a hint on the impact of the new trace flag.
I set up profiler to monitor all events in the database and run the function to create a requisition worksheet. Without T4136 i landed on an average of 4,6 seconds and 30 500 reads after several runs.
Then I stopped the server, added the trace flag, started up again and run several requisition worksheet calculations again. Now I got an average on 3,5 seconds and 27 802 reads.
The difference between the two scenarios may not seem much and need to be confirmed on further testing on larger amounts of data, but it is interesting to see that we get 10% less reads in my little test.