|
Comments
|
|
interesting.
|
|
Lonnie Meinke on
4/10/2009
I'm unfamiliar with plan guides and parameter sniffing....needed a little more background.
|
|
|
Creating a plan guide seems like an option to possibly solve a performance problem without having to make a stored procedure change and go through change control to get it into production. It would be nice to explain some other uses ir reasons to use this feature. It seems that with more complex stored procedures using this feature could be hard to do. I get the idea from this video that you need to include all the SQL from the stored procedure that may have performace problems. This means there will be a lot of plan guides to manage. This could be problematic. However, this feature does make for another tool to help with keeping things running smoothly.
|
|
|
This was really interesting. I liked how the presenter gave some background, although for some novices it might have been helpful to define what a table scan is and what the query hint that was used here will do along with where to find more information on other query hints. I also wasn't sure if doing this would fix the problem once the SP had already been run, since I've never used query hints (I'm a developer not a DBA).
|
|
|
incomplete video
|
|
|
This could have been much better if the typing was done before hand so the concentration was focused on the topic points. Thank you for showing me this feature.
|
|
|
Don, one of the best uses for plan guides in my opinion is when dealing with third-party software. As a DBA or developer many times you have to solve a problem where the vendor does not allow you to change their code. In that case you can simply apply a plan guide and tune a query without changing code.
|
|
|
SUBRATA, there is a more complete explanation with more examples on my blog: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html.
|
|
|
Liked the video.
One problem I see is, your demo was for simple use-case scenario. In this very video your SQL (within "GetCustomerOrders" as well as "sp_create_plan_guide") was a simple SELECT statement, but in real-life scenarion SQL code may not be as simple as it looks (or atleast there are multiple SQL statements within an SP). How do we handle that within "sp_create_plan_guide"?
Do we cut-and-paste the code from User Defined SP to code within "sp_create_plan_guide"? That can be quite an risky proposition (code duplication). What solutiuons we have for those scenario
|
|
|
Prasanna, plan guides apply to a single query, so if your stored procedure has multiple queries you would have to create multiple plan guides (since a plan guide is used to apply a hint to a single query). But in general you should not have to apply hints to all queries in the stored procedure. See the article "How SQL Server Matches Plan Guides to Queries" on how the query text is matched in the plan guide: http://technet.microsoft.com/en-us/library/ms189296.aspx. Also, "Designing and Implementing Plan Guides": http://technet.microsoft.com/en-us/library/ms189854.aspx
|
|
|
perhaps mention the cost of the recompile and plan guide.
|
|
|
The cost of the recompilation can be a small fraction of the total cost compared to the gains of solving parameter sniffing. However, a stored procedure that is executed very frequently can turn into a bottle neck because of recompilations. But in that case another approach (like OPTION OPTIMIZE FOR) can be used (which does not force recompile).
|
|
Ahmad Elayyan on
8/22/2009
good
|
|
|
very good. would like to see mention of costs in diufferent options such recompiling or what other hints can be supplied. Can you use optimise for in the hint for example?
|
|
|
4B47653576, yes, you can use OPTION OPTIMIZE FOR in the hint.
|