Solved

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

Posted on 2007-11-21
6
644 Views
Last Modified: 2008-02-01
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
Comment
Question by:motioneye
  • 4
  • 2
6 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20332718
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 500 total points
ID: 20332727
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
 

Author Comment

by:motioneye
ID: 20333249
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 500 total points
ID: 20333285
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
 

Author Comment

by:motioneye
ID: 20333403
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20333555
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now