patternsqlMinor
Parameter sniffing work arounds
Viewed 0 times
sniffingworkparameterarounds
Problem
I have used two methods to get around parameter sniffing issues in the past:
1) Use
2) Reassign parameter values to local variables and use those instead of the parameters
From what I understand, the end result of both of these is the same - a new execution plan that is optimized for the current query/params is created and used.
If this is true, are there any differences between these two methods or are they essentially the same? Is one preferable over the other?
1) Use
WITH RECOMPILE2) Reassign parameter values to local variables and use those instead of the parameters
From what I understand, the end result of both of these is the same - a new execution plan that is optimized for the current query/params is created and used.
If this is true, are there any differences between these two methods or are they essentially the same? Is one preferable over the other?
Solution
WITH RECOMPILE
This is a bit of a sledgehammer. It recompiles every statement in the module. Using
OPTION (RECOMPILE) on the statements that are parameter-sensitive is a more targeted solution.- Reassign parameter values to local variables and use those instead of the parameters
This has the same effect as
OPTIMIZE FOR UNKNOWN i.e. a plan is generated based on average values. The plan does not recompile on each execution.For more information please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.
Context
StackExchange Database Administrators Q#20115, answer score: 4
Revisions (0)
No revisions yet.