Solve Parameter Sniffing by Using OPTION 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 OPTIMIZE RECOMPILE.

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

Howard Rosen on 7/27/2009

you are unclear on the usage of with recompile and the question you asked

Sara Karasik on 7/27/2009

the video was great, and the information necessary and beautifully written. I hate to say this, however. I found the foreign accent very difficult to follow.

James Moore on 7/27/2009

Good to know feature. Thanks!

Tim Harms on 7/27/2009

I like hearing about ways to make my code more efficient. Is there any significant added overhead from using OPTION (RECOMPILE) with a query in an SP?

Jamshid Nouri on 7/27/2009

excellent demo

Plamen Ratchev on 7/28/2009

Tim, the overhead of using OPTION (RECOMPILE) is the CPU usage for the recompilation. If the stored procedure is executed frequently that can create significant CPU activity. In general you should not use this option unless you detect performance problems due to parameter sniffing.

yousef on 8/1/2009

what if we use a where clause which utilizes the primary key?does the same rule apply?I mean in this case we know the query plan is 99% the same.IMHO this rule is true as long as we use columns which are not unique,am i right?

Plamen Ratchev on 8/2/2009

Yousef, if your parameter is on the primary key, then most likely you will not experience parameter sniffing performance issues, because there is a single row match in all cases.

Farid on 12/9/2011

test is wrong

David Overall on 12/9/2011

great

Rolan Logan on 12/9/2011

Great learning tool! Thank you!

Russell Tye on 12/10/2011

Great explanation...I loved it!!!

MohanD on 12/10/2011

Good one... you should have also mentioned about when it is the CON's of using this statement i.e. discussed the situations when NOT to use the OPTION (Recompile). A comparision video of Option(Recompile) & with (recompile) would have been far better. Thanx,

A Alagu Ganesh on 3/30/2012

Good