Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 677
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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