|
Comments
|
|
it is good
|
|
|
Again: Looking at the execution plans would make it much clearer.
|
|
|
Excellent lesson thanks. How do I store my favorites to my account?
|
|
|
When the new local variable is set the passed in parameter, the passed in parameter is named incorrectly.
|
|
|
ok
|
|
|
seemed to stop before explaining the point?!
|
|
|
seemed to stop before explaining the point?!
|
|
|
Show the bad plan?
|
|
|
Very interesting, but I would like to know a little bit more -- maybe should I always be doing this, or when should I do this...
|
|
|
Nice and clean, even with the typo
|
|
|
Good one. Want to see some more of this kind..
|
|
|
There is no explanation why, what is happenning now with exec plan.
|
|
|
He spelled customerid wrong. He wrote cutomerid
|
|
|
Jed, as noted parameter sniffing in general is a good thing and you should not worry about it unless you have performance problems. In this example using local variables in effect disables parameter sniffing, which will generate execution plan based on statistics, and that may not be best. You can read more details and different approaches on my blog: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html.
|
|
|
Carsten, Hansjörg, Aleksandar, here are the two possible execution plans:
Index/Table scan:
StmtText
--------------------------------
|--Clustered Index Scan(OBJECT:([dbo].[Orders].[PK_Orders]), WHERE:([dbo].[Orders].[CustomerID]=[@customerid]))
Index seek:
StmtText
--------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([dbo].[Orders].[OrderID]))
|--Index Seek(OBJECT:([dbo].[Orders].[CustomerID]), SEEK:([dbo].[Orders].[CustomerID]=[@customerid]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dbo].[Orders].[PK_Orders]), SEEK:([dbo].[Orders].[OrderID]=[dbo].[Orders].[OrderID]) LOOKUP ORDERED FORWARD)
If you execute the stored procedure first for very selective customer with only a few orders, the index seek plan is generated. All subsequent stored procedures use the index seek plan, even if it is for large set of orders which can benefit from the table scan plan. Or the opposite, if the large set of orders is executed first then the table scan plan is generated and then used even for a small set of orders that can benefit from the index seek plan.
|
|
Kamen Angelov on
7/11/2009
Will be good to append something about Level method
|
|
Kamen Angelov on
7/11/2009
Very useful!
|
|
|
Nice little twist to parameter sniffing. Good to know..
|
|
|
It would be good to give more examples of when and why you'd want to do this, as well as the performance impact of doing so.
|
|
|
Caleb, you have to use these types of techniques only when you encounter parameter sniffing. The performance impact is that this method in effect disables parameter sniffing and the plans are generated based on statistical data, which may not be the best in all cases.
|
|
|
no proof that this technique works
|
|
|
Josh, the proof is that SQL Server does not sniff local variables and therefore cannot create optimized plans for those variables, resulting in plan based on statistics. Read this article by Ken Henderson for more details: http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx.
|
|
Henk Schreij on
8/20/2009
you made a typo: forgot the s :-)
|
|
|
If, as stated, the query plan is determined upon the first invocation, the query plan results should be the same whether a parameter or local var is used. If not, then recompilation is required.
|
|
|
Arturo, when a local variable is used SQL Server cannot use the parameter values to generate plan and the plan is based on the statistics. When the parameters are used directly the plan is generated based on the parameter values. This is why the plan can be different.
|
|
|
Good one!!!
|
|
|
Back in 2005, I figured it out but could not name this scenario. Thank you for providing name 'parameter sniffing' for it.
|
|
|
good
|
|
|
This was good to a point. It does not state what is good about parameter sniffing. Does this local variable technique force an execution plan to be created every time?
|
|
|
I didn't really understand why making that change would improve things, but if I ever see code like that I'll know it might have been done for a reason.
|
|
|
It would have been excellent if you would have shown us the execution plans and the profiler traces.
|
|
|
Neil, using local variables does not force plan recompilation. It simply disables parameter sniffing and generates plan based on statistics (not on the passed parameter value). See here for more details and examples: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html
|
|
|
Team FAB, this technique will improve performance only if there is performance issue due to parameter sniffing (that means if there was plan created that is inefficient for some parameter values). In essence using local variables disables parameter sniffing and the optimizer generates plan based on statistics (not on the passed parameter value).
|
|
|
Had never even thought of this method of variable usage!
|
|
|
Uncorrected typo in the demo, but the point and method is clear. Good job.
|
|
|
Great to learn something so simple that can have a huge impact.
|
|
|
Very interesting approach.
|
|
|
Very informative and will be very helpful to me
|
|
|
in sq
|
|
|
There was a typo (@cutomerid instead of @customerid)
|
|
|
Cannot understand all his words due to accent.
|
|
|
This is somewhat misleading. Just because parameter sniffing occurs does not mean statistics are not being used. Isn't the real issue whether or not it makes sense to reuse the cached query plan?
|
|
|
26168E2B7B, the cached query plan will be reused with parameter sniffing and when you use local variables to disable parameter sniffing and generate plan based on statistics. But the plans will differ (most likely) and one can be very inefficient.
|
|
Cosmin Tornea on
6/25/2010
cool
|
|
|
I wish the author had provided examples by executing the stored procedure before and after the changes.
|
|
Charlie Bruno on
6/26/2010
I learned something new. Thank you.
|
|
|
Have you tested the performance difference using and without using local variables . Can you demonstrate?
|
|
|
Sat, testing performance differences matters only if you do have performance problems due to parameter sniffing. Yes, I have tested and resolved many cases where local variables helped to improve performance. The answer is in the query plans generated when using local variables and without local variables. This is what makes the difference.
|
|
|
Great tip, as usual. Welcome back, haven't seen any posts from you lately.
|
|
|
Further explantion and query plan examples would have been helpful
|
|
|
rated sometime before.
viewed again on 3-21-11
|
|
|
Would have liked to have seen the query plan before the change and the query plan after the change.
|
|
|
more explanation...wit some examples
|
|
|
more explanation...wit some examples
|
|
|
more explanation...wit some examples
|
|
|
more explanation...wit some examples
|
|
|
more explanation...wit some examples
|
|
|
Great tip! Simple change with potentially huge performance impact. Thank you.
|
|
|
Spelled @customerid wrong in line 8.
|
|
|
Something bad to do... there is another alternatives to solve this problem and this is the worst...
|
|
|
a good start about local variables
|
|
|
awesome tip. Would have been nice to actually see the plans to visualize the effect.
|
|
|
There was a typo ("@cutomerid").
|
|
|
I still don't understand what this topic was about. Very confusing. Doesn't even seem to make sense.
|
|
Plamen Ratchev on
12/15/2011
Fabiano, this method is one of the best known solutions for SQL Server 2000. For newer versions there are other related videos: http://www.sqlshare.com/solve-parameter-sniffing-by-using-optimize-for-unknow_532.aspx, http://www.sqlshare.com/solve-parameter-sniffing-by-using-optimize-for_533.aspx, http://www.sqlshare.com/solve-parameter-sniffing-by-using-option-recompile_534.aspx, http://www.sqlshare.com/solve-parameter-sniffing-by-using-recompile_536.aspx
|
|
|
SQL2008 has also introduced 'OPTION (OPTIMIZE FOR UNKNOWN)' which is another option.
|
|
|
interesting tip
|