Solved

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

Posted on 2007-11-21
6
640 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

8 Experts available now in Live!

Get 1:1 Help Now