Solve Parameter Sniffing by using RECOMPILE

Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. This video shows you how to limit the performance effects of parameter sniffing by utilizing the RECOMPILE option.

Plamen Ratchev has over two decades experience in the software architecture and development field. He has enjoyed building solutions for UNIX, DOS and Windows platforms, with main focus on SQL Server since version 4.21. Plamen is founder of Tangra, specializing in relational database applications analysis, implementation, and tuning. His particular interest is in design patterns, performance a...

Satyabodhi Densmore on 7/7/2009

Should also mention the WITH RECOMPILE option of CREATE PROCEDURE.

mike stucchi on 7/7/2009

wish it was a bit more detailed possibly with the trace showing the benefit. still very good

mike stucchi on 7/7/2009

wish it was a bit more detailed possibly with the trace showing the benefit. still very good

Abdallah on 7/7/2009

Where is the testing?

Jamshid Nouri on 7/7/2009

nice demo

Tom Hamilton on 7/7/2009

thank you

Murali krishna MV on 7/7/2009

It given an idea that there is a option using recompile. Missing is Why with WITH RECOMPILE? What are the impacts without recompile?

Naveed Khawaja on 7/8/2009

Code had a mistake, and would have been nice to see the actual execution

Perry Wiggers on 7/8/2009

nice

Carsten Hynne on 7/8/2009

It would be much better, if we actually saw the execution plan resulting from an atypical parameter and the changed plan as well as the actual impact of using the wrong execution plan.

Plamen Ratchev on 7/8/2009

Satyabodhi, yes you can create stored procedure using WITH RECOMPILE, but the point here was to show solution after the procedure is created. In essence it does the same.

Plamen Ratchev on 7/8/2009

Murali, there are other videos that discuss the other options to solve parameter sniffing. Here is one: http://www.jumpstarttv.com/solve-parameter-sniffing-by-using-option-recompile_534.aspx. You can also see all methods at a glance on my blog: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html.

Mahesh Kansara on 7/8/2009

The where clause has variable @local_Customerid which is not declared anywhere

Dipak Saha on 7/8/2009

Good topic.

Orlando on 7/11/2009

who knew that a cached query plan from execution #1 of a new proc could be so harmful to later executions...I imaging with recompile is not something you want to use as a strategy however...so when is the next part that explains how to mitigate those two factors?

Ray on 7/15/2009

difficult to understand need to clarify reason for use better

Plamen Ratchev on 7/16/2009

Orlando, in general you should not worry about parameter sniffing unless you have performance problem. Then you have to decide on a solution. I listed a few solutions in detail on my blog here: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html.

Plamen Ratchev on 7/16/2009

Ray, as already noted, parameter sniffing is a good thing. But in some case it may cause performance issues. Only in those cases you have to worry. Here is a great white paper on plan caching issues with details: http://technet.microsoft.com/en-us/library/cc966425.aspx.

Jonathan Winer on 7/24/2009

It would have been nice to show other places the WITH RECOMPILE hint could be used. i.e.(within the sp itself)

A Alagu Ganesh on 8/4/2010

Good. Thanks & regards, A Alagu Ganesh.

A Alagu Ganesh on 8/4/2010

good