Disable Parameter Sniffing in SQL

I just found this in the information about Cummulative Update 7 for SQL2008 SP1:

Microsoft SQL Server 2008 Service Pack 1 (SP1) Cumulative Update 7 introduces trace flag 4136, which can be used to control the “parameter sniffing” process. When you enable trace flag 4136, the “parameter sniffing” process is disabled.

This trace flag is ignored when one of the following conditions is true:

  • The parameter is specified by using the “optimize for (@p = )” query hint.
  • The query uses the “option(recompile)” query hint.
  • The query is a part of a stored procedure that uses the “with recompile” option.

<<

Since parameter sniffing has been quite an issue for NAV since it was introduced it’s interesting to see that MS is now offering a way to disable it globally for the whole server instance. It will be interesting to see if there will be any follow up from the NAV Team in coming builds to require this trace flag to be set and stop using prepare statements as today.

Hi Lars,

thanks a lot for this info! This sounds great - I’ll check this out ASAP [Y]

Actually you can already disable the cursor preparation in NAV by adding a value to the “diagnostics” in “$ndo$dbproperty”:

update [dbo].[$ndo$dbproperty] set [diagnostics] = [diagnostics] + 1048576

As far as I know this requires NAV 5.0 SP1 Update 1 or higher.

Yes. That disables cursor preparation from NAV, but SQL still does parameter sniffing.

With this new trace flag in SQL together with disabled cursor preparation in $ndo$dbproperty the Query Optimixer should work like back in the SQL2000-days as I se it (at least when it comes to parameter sniffing…).

Thanks Lars,

this could be a good reason to get some clients to move to 2008.

By the way, can you let me know which is the trace flag that disables Dynamic Cursors [:P]

Ha Ha [:)]

Yes. Wouldn’t it be nice to put something like “CURSORTYPE=FFD” into $ndo$dbproperty…

I’ve done a small test now and has put the result on my blog.