|
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.
|
|
|
I would prefer properly spoken english - it is important for foreigners
|
|
|
Wouldn't it be easier/cleaner to just create local variables in the SP?
|
|
|
nice
|
|
|
why not just alter the procedure to add the option to recompile
|
|
|
Thank you for this topic, it was new to me.
|
|
|
it would be nice if you could understand the presenter.
|
|
|
not clear why option(recompile) solves the problem?
|
|
|
Really good... will pay the attention more...
|
|
|
Option RECOMPILE course the procedure to be recompiled every time and slow down performance.
Use internal variables to reassign parameters values
|
|
|
Really useful presentation!
|
|
|
very useful.
|
|
|
I wasn't clear until now about the Parameter Sniffing, Thanks.
|
|
|
The select statement didnt match the tsql within the stored proc. hence, the plan guide would not work but its what shown in this video. not accurate at all.
|
|
|
sorry, my post is inaccurate. i mislook and thought the plan guide would not work, but it'll work.
|
|
|
can you tell how to identify it? how can i tell that the query is not optimized?
|
|
|
Showing the use of sp_create_plan_guide was great. The solution that was shared could have been better.
|
|
|
would like to have an example with/without the plan guide
|
|
|
Would have been nice to see an execution plan showing before and after of the sp_create_plan_guide.
|
|
Maurice Ivory on
2/16/2012
it was hard to pay attention to this one.
|