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.
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…).