Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-21
6
Medium Priority
?
671 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 2000 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 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

971 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