Solve Parameter Sniffing By Using Optimize for Unknown

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

Jed Perlowin on 10/1/2009

A little more info on how this works as well as when to use it and when not to use it would have made this much more valuable.

jaiganesh on 10/1/2009

good.it would be better if he explained much detail on executing the stored procedure

Christian Mark Jensen on 10/2/2009

Perhaps a little mention of statistics and recompilation when using this option. Christian Mark Jensen DBA

paul kay on 10/2/2009

useful technique

Steve Harris on 10/2/2009

Very helpful. Maybe mention the other option hints that can be used in the SELECT statement.

Plamen Ratchev on 10/2/2009

Here is a compare of this technique to other methods to solve parameter sniffing with more details: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html. In essence when OPTIMIZE FOR UNKNOWN is used the optimizer uses statistical data to pick the execution plan. This may or may not be the best choice. This method does not force recompilation, but depends on statistics.

R James Reichard on 10/2/2009

Could not understand the speaker. Would be nice to see the end results of the example query.

Christian Bahnsen on 10/2/2009

Thanks. Great hint.

Ralph Schwehr on 10/2/2009

had a hard time to understand him...

Derek Czarny on 10/5/2009

Should have shown how the Optimize for Unknown improves the query performance.

James Knapp on 10/5/2009

Hard to understand. Need to slow down when you talk.

Tonci on 10/5/2009

Good explanation of to why use this option. Thank you, Tonci.

ShaheedulHaq Saad on 6/12/2010

nice, but definitely language problem

Keith Wiggans on 6/14/2010

It would have been nice to have seen a comparison of the difference in execution plans caused by utilizing this query hint. Otherwise, A+ video. The author's accent is great.

Jorge H Serres on 9/16/2010

1st bad pronounciation and too many mistakes while speaking....CONFUSING

17EB042629 on 12/13/2011

difficult to understand the thick indian accent.