in

SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

SQL Server 2008 Plan Guides from Cache

Uhoh... I can see some junior developers going crazy with this. One of the things that kept plan guides from being over used was the fact that they are kind of hard :) Well, Microsoft built their empire making hard stuff easy. They do it again with sp_create_plan_guide_from_cache.

Let's look at this BOL sample.

 

USE AdventureWorks;

GO

SELECT WorkOrderID, p.Name, OrderQty, DueDate

FROM Production.WorkOrder AS w

JOIN Production.Product AS p ON w.ProductID = p.ProductID

WHERE p.ProductSubcategoryID > 4

ORDER BY p.Name, DueDate;

GO

-- Inspect the query plan by using dynamic management views.

SELECT * FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

GO

-- Create a plan guide for the query by specifying the query plan in the plan cache.

DECLARE @plan_handle varbinary(64);

DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp

WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

 

EXECUTE sp_create_plan_guide_from_cache

    @name =  N'Guide1',

    @plan_handle = @plan_handle,

    @statement_start_offset = @offset;

GO

-- Verify that the plan guide is created.

SELECT * FROM sys.plan_guides

WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

GO

 

--Let's verify it actually worked.

--Click the xml link

--Save as a .sqlplan, reopen in SSMS and then hit f4

set statistics xml on

go

SELECT WorkOrderID, p.Name, OrderQty, DueDate

FROM Production.WorkOrder AS w

JOIN Production.Product AS p ON w.ProductID = p.ProductID

WHERE p.ProductSubcategoryID > 4

ORDER BY p.Name, DueDate;

GO

So when would you use this? I would say hardly ever hopefully. However, it could solve the really tough problems.

Let's say you have a 3rd party application that generates adhoc dynamic SQL. You cannot modify the code or schema. Index changes are not supported. Sometimes parameter sniffing causes unpredictable performance. Sound like a nightmare? Welcome to most CRM apps.

Other scenarios that come to mind are when best practices are not or cannot be followed. Let's say you just cannot update stats often enough with a large enough sample on a very very VERY large table to get a consistently optimal plan. Use a plan guide!

Here are some other times that the optimizer might have trouble and a plan guide may be a good option.

  • Use of local variables
  • Modifying stored proc parameters.
  • Ascending keys
  • Complex queries with table variables

There are usually better solutions than plan guides so save them for times that best practices are not an option. sp_create_plan_guide_from_cache makes using plan guides so much easier. Put it in your toolbox!

 

**Cross Posted from http://statisticsio.com **

Comments

No Comments

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at http://statisticsio.com. He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at http://linkedin.com/in/jasonmassie or jmassie@terremark.com
Powered by Community Server (Commercial Edition), by Telligent Systems