|
Comments
|
|
simply great
|
|
|
Hidden Gem
|
|
|
.
|
|
|
The way the presenter ROLLS his R's is so heavy as to be very distracting and hard to understand. His English is good except for that. He seems like a bright guy, and paying attention to his R's shouldn't be that big a deal, so why doesn't he do it?
Also, your question page won't scroll down in Firefox.
|
|
|
Works only for a singular case, does not address multiple parameters with multiple values per run, in those cases you have to use recompile.
|
|
Jed Perlowin on
4/23/2009
Very interesting, but I would like to have seen a more in depth explination of why this helps. I'm not sure I understood the reason for the advantage of using the OPTIMIZE FOR clause.
|
|
|
should have shown what we do today to fix the problem. that is put the param in a temp var then use that in the where clause
|
|
|
Matt/Jeff, here is an article that I wrote which explains in greater detail parameter sniffing and different solutions: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html. The OPTIMIZE FOR query hint has limited scope but it can be useful tool in case optimization has to be done for a known value (or in case of SQL Server 2008 for UNKNOWN which will use statistical data).
|
|
|
Mark, there is a video that demonstrates using local variables to solve parameter sniffing: http://www.jumpstarttv.com/solve-parameter-sniffing-by-using-local-variables_531.aspx. But that is just one method, which may not work in future versions of SQL Server because SQL Server supports recompilation at statement level and is capable of sniffing local variables.
|
|
|
would be good to demo the bad plan vs the good plan
|
|
|
good speaker
|
|
|
great hint...
|
|
|
Great information - pramater sniffing has been a problem for us in some SQL 2000 back ended applications that are moving to 2005, so this is valuable.
|
|
|
It would have been nice to show the differences in the execution plans with different parameters. The author does mention different parameters could cause different execution plans but it's nice to see the differences and how Optimze For will force an execution plan.
|
|
|
The obvious next question is what happens if the chosen optimize for value ID gets deleted from the table? BOL isn't much help.
(In sql 2000, we resolved an occurrence of this by reassigning the input parameter variable to a local variable in the sproc.)
|
|
|
John, the value that is used in OPTIMIZE FOR is simply a hint for the optimizer to generate a plan. As long as other values have similar distribution they can all benefit from the plan. Even if the value is deleted the plan can be efficient for the other values. Using local variables is a technique that actually disables parameter sniffing (because the optimizer does not currently sniff local variables) and the generated plan is not optimized for the input parameter values.
|
|
|
A bit hard to understand with the accent unfortunately. Also, during the beginning of the presentation, it would have been nice to see the different query plans resulting from parameter sniffing as he was explaining the issue.
|
|
|
Nice Demo, very precise. If you could have demoed the same with multiple parameters, that would have given a picture about HOW TO use/define multiple parameters with "OPTION" part of the syntax (2nd last line of your code)
|
|
|
Prasanna, the syntax of OPTIMIZE FOR is: OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] ). As you can see you simply need to list all variable values separated by a comma, like OPTION ( OPTIMIZE FOR (@var1 = 'abc', @var2 = ‘def’) )
|
|
|
Very good!!!!!!!!!!!1
|
|
|
The correct answer for the definition of parameter sniffing is poorly worded
|
|
Gökhan Gülbiz on
9/19/2011
May be more useful with showing metrics.
|
|
|
Elegantly explained. I also was not aware of the "optimize for" option. Thanks.
|
|
|
Not really useful without functioning example
|
|
|
I am pretty new to SQL Server and really didn't follow what he was tring to say here.
|
|
|
Could hardly understand what he was saying
|
|
Clyde Turner on
9/20/2011
Presenter was slightly hard to understand. Had to watch the video 5 or 6 times to get a full understanding.
|
|
|
did not see the benefits demonstrated
|
|
|
Would have been nice to see the sp in action to see the benefits.
|
|
Maurice Ivory on
2/22/2012
I got it!!
|