• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

sp_control_plan_guide & sp_create_plan_guide... what is this and how to using these procedure

After few time reading SQL2005 BOL I found this new system sp ( sp_control_plan_guide & sp_create_plan_guide) it seems to me we can force the user query to use this plan but I'm not sure how good and effective it is, can someone tell me when should I use and plan for this one and on which circumtances this sp very useful to be use?
0
motioneye
Asked:
motioneye
  • 4
  • 2
3 Solutions
 
imitchieCommented:
a different reading to BOL
http://www.sql-server-performance.com/articles/per/using_plan_guides_p1.aspx

basically, if you have queries that you suspect (or know) are going to perform badly, you can plan it youself and have SQL server follow that plan
0
 
imitchieCommented:
they become useful only if your database size grows to millions of records. I had a query that linked 17 tables with criteria on 3 of them. SQL server is terrible at figuring out those - it relies on statistics. but keeping them up to date is a cost, so most people do it once a day/week, depending.

a classic example is when data is streamed in at 1% at the very end clustered index.  the statistics will reflect that using the date index is great for >= [last statistics update time] because it yields.. 0 records to begin with. however as the day progresses, you get up to 1% density on that index. however, if I were searching for a customer with 0.5% density, it should have used that index.

there are enough examples in the link and BOL for you to work through, but basically, if you don't understand it enough to use it, you probably don't need it. SQL Query Planning is OK most times
0
 
motioneyeAuthor Commented:
I just wonder when should I use it.. so it sounds more related with statistics update,,, rather than changing the code from application perspective to sql server plan.

take  a look at this articles http://www.sql-server-performance.com/articles/per/using_plan_guides_p1.aspx

what I thought is if the query from the application run badly, we can force the application to use the query in plan guide with a similarity...
But after my readings from the link above.. i dont see the diff between the query code and plan code.. that is why seeking more info in EE
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
imitchieCommented:
you don't change the query code. you force a plan for SQL to use when it encounters the Query code... if that makes sense
only use with extreme prejudice, if you know that you will do a better job than SQL Server
0
 
motioneyeAuthor Commented:
Oh..
I get what I mean.. basically u were says sometimes the statistics is out of date the sql misght use wrong query plan thus skow down the system..so by creating plan, we'll force sql to use the plan instead of letting sql deciding which plan that should be use when the query extracting data...

am I rite??
0
 
imitchieCommented:
yes that's right. sometimes even with the right statistics, there are always exceptional cases where you can do better than a machine
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now