Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I have used two methods to get around parameter sniffing issues in the past:

1) Use WITH RECOMPILE
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?

share|improve this question
How would reassigning parameter values to local variables make any difference? The root issue is that the execution plan of the queries in the procedure created after the first call will potentially be different than subsequent calls. Whether the execution plan is derived directly from proc parameters or local variables won't matter. – Thomas Jun 28 '12 at 16:19
2  
If it's using local variables it generates/uses a new query plan. Here is one link: pratchev.blogspot.com/2007/08/parameter-sniffing.html if you google you will find tons of others that discuss this work around. – Abe Miessler Jun 28 '12 at 16:23
2  
@AbeMiessler - No the end results are not the same, reassigning to variables works the same as OPTIMIZE FOR (UNKNOWN). The statement is compiled without looking at specific parameter values at all (except if the statement ends up getting a recompile anyway in which case the values of the variable will be used) – Martin Smith Jun 28 '12 at 16:27
@AbeMiessler - I agree with Martin. Using local variables doesn't solve the root issue of a stale or suboptimal execution plan for the parameters passed. – Thomas Jun 28 '12 at 16:36
@AbeMiessler - As a curiosity, what is your goal in trying to get around parameter sniffing? Is it to optimize for a particular parameter set regardless of what happens on the first call? – Thomas Jun 28 '12 at 16:40

migrated from stackoverflow.com Jun 28 '12 at 19:01

1 Answer

up vote 1 down vote accepted

If you can anticipate typical values, run your sproc with such values whenever you (re-)create it. An execution plan will be created based on these values, and stored for future use. Most of my sproc .sql files end with an EXEC command with reasonable values, for exactly this purpose (that, and to identify basic errors in the code).

Of course this won't help if your sproc is very long running, enough that you can't reasonably execute it during production hours. Note that it's not enough to start the job, it has to complete successfully for a plan to be stored.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.