SQL Server 2008 Plan Guides

So here’s how it’s done..

So here’s how it’s done..I rarely know more about SQL Server than my friend Beatrice so I was surprised when I mentioned plan guides and she hadn’t heard of this new feature. I have to say this is not a big mainstream thing and it takes me back query hints in SQL Server 2000.

Essentially you get the optimiser to generate a plan for you and then apply this to the query so that it always gets used every time that query is subsequently run.  It might be a good way of getting the best performance out of a third party application you can’t change.

So here’s how it’s done..

SET STATISTICS XML ON
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM
Production.WorkOrder AS w
INNER JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE
p.ProductSubcategoryID > 4
ORDER BY
p.Name, DueDate;
GO
SET STATISTICS XML OFF
GO

The important bit is the query plan (double click on the xml to see this)..

You can get the SQL handle for the plan like this …

SELECT
*
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset) AS qp
WHERE
qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

and create a plan from it with this…

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT
@plan_handle = qs.plan_handle,
@offset = qs.statement_start_offset
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset) AS qp
WHERE
qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle
@name = N'MyPlanGuide',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
GO

There are event classes to see if its being used or missed e.g. you might have changed the schema for example and these  are cunningly named as

• plan guide successful
• plan guide unsuccessful

You can check your plan guides using fn_validate_plan_guide(plan_guide_id), for example

USE AdventureWorks;
GO
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO

You can see if a plan guide is in use if you run the query again and look at the properties of the query plan (press F4):

The plan guide will also show up under programmability in Management Studio:

and to get rid of it you’ll need to run 

EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[MyPlanGuide]'

Finally the full detail is here in the SQL Server TechCenter

Technorati Tags: SQL Server 2008,plan guides,query optimisation