SQL Server 2005
The plan guides were first introduced in SQL Server 2005.You may find detailed information about the plan guides in SQL Server 2005 here:
Description of sp_create_plan_guide
This feature can be useful when you are trying to enforce the optimizer using a specific execution plan without changing the original query.
In SQL Server 2005, however, you have to specify the statement exactly as it was submitted to the SQL Server. Sometimes it can be difficult
since you have to capture a SQL Profiler trace and copy the SQL statement from there. Also there are certain rules that have to be applied.
For example, the SQL parser does ignore spaces and escape characters inside the query text, but does not allow it at the end, so it may add
complexity to capturing the actual statement.
In SQL Server 2005 query statistics will appear in DMVs including the T-SQL statement. In order to make it easier you may create a plan guide
based on query statistics DMVs directly.
Here is the script that demonstrates this.
NOTE: Before you execute the examples below please make sure you have the AdventureWorks database installed and the compatibility level for it set to 90.
If you don’t have the AdventureWorks database you can download it from:
--- Cleaning cache for this sample
--- Running query first time to get plan generated for freezing
set statistics xml on
N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'
set statistics xml off
--- Based on query pattern creating a plan guide - freezing plan
declare @sql_text nvarchar(max),
substring(sqt.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(sqt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1),
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) sqt
cross apply sys.dm_exec_query_plan(qs.plan_handle) sqp
where text like '%Employee%'
select @sql_text, @sql_xml_plan
set @sql_xml_plan = 'OPTION(USE PLAN '''+@sql_xml_plan+''')'
exec sp_create_plan_guide @name =N'MyPlan_Guide_1'
, @stmt = @sql_text
, @type = N'SQL'
, @module_or_batch = NULL
, @params = NULL
, @hints = @sql_xml_plan
You may check the plan guide is created by querying sys.plan_guides catalog view
select * from sys.plan_guides
Now execute the query again.
--- This time we will see USEPLAN=1 and plan guide name in XML plan output
set statistics xml off
Click on ShowPlanXML hyperlink
SQL Server Management Studio 2005 will show you XML. Look at <StmtSimple> and <QueryPlan> tags.
As you can see SQL Server picked the created plan guide and USEPLAN option.
WARNING! You should be careful using the plan guides in SQL Server 2005. In case if metadata and/or
data distribution has been changed the optimizer will not be able to use the plan guide anymore and the
query will fail with the following error:
Msg 8698, Level 16, State 0, Line 1
Query processor could not produce query plan because USE PLAN hint contains plan that could not be
verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful
plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by
SQL Server for the same query.
To demonstrate this, disable the existent index that is used in the plan guide above
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE
and try running the query again.
To clean up your server after this demonstration:
--- Rebuild disabled index to enable it
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD
--- Drop plan guide
EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';
In SQL Server 2008 plan guides feature has been improved.
In addition to sp_create_plan_guide you can also use the sp_create_plan_guide_from_handle stored procedure
Understanding plan guides
So now you can create a plan guide based on the actual plan handle without pulling the T-SQL statement text
Here is the example:
declare @plan_handle varbinary(1000)
select @plan_handle = plan_handle
exec sp_create_plan_guide_from_handle 'MyPlan_Guide_1', @plan_handle=@plan_handle
As you can see creating the plan guide is easier now, and you may also easily copy and paste the plan handle from the
DMV output and manually pass it to sp_create_plan_guide_from_handle
Run the query again
In SQL Server 2008 SQL Server Management studio will take you directly to the graphic execution plan automatically
Then right click on the execution plan page and choose Properties
Another difference in SQL Server 2008 from SQL Server 2005 is with the optimizer behavior if the metadata has been changed.
If this is the case, then the plan guide can not be used anymore. The SQL Server 2008 optimizer will silently skip the plan guide
and continue with query execution with whatever execution plan is the best. This makes the use of plan guides more robust and
less dangerous than in SQL Server 2005.
To monitor the plan guides behavior you may use two new SQL Profiler events in SQL Server 2008
Posted by: Sergey Pustovit – SQL Server Escalation Services