![]() ![]() This may be unknown to you, but a query can use a trace flag as a hint to change the behavior of the query optimizer. Disable Parameter Sniffing for a Specific SQL Server Query SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable the "parameter sniffing" process.īefore using this method take a look at the next one, it’s far less drastic. You can read more about this and the patch level required to use this flag on this KB article: ![]() But if you want, starting the instance with trace flag 4136 set will disable parameter sniffing. As I said before, parameter sniffing is not a bad thing per se and is very useful in most cases to get the best plan. Maybe this is the worst choice you can make. We shouldįurthermore, recompilation will increase CPU load and in heavy concurrent systems it could be as problematic as the issue we are trying to solve.Īs an example, below is the sample code to create our previous stored procedure withĭisable SQL Server Parameter Sniffing at the Instance Level If the problem is a single query inside the stored procedure code then performing a recompilation of the entire procedure is not the best approach. If the problem is that the optimizer uses a plan compiled with parameters that are no longer suitable then a recompilation will create a new plan with the new parameters right? This is the simplest solution, but not one of the best.
0 Comments
Leave a Reply. |