Plan guides (plan freezing) in SQL Server 2005/2008

 

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:

 

General information

https://msdn.microsoft.com/en-us/library/ms187032(SQL.90).aspx

 

 Description of sp_create_plan_guide

https://msdn.microsoft.com/en-us/library/ms179880(SQL.90).aspx

 

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:

https://www.codeplex.com/SqlServerSamples

 

 

 

use AdventureWorks

go

 

--- Cleaning cache for this sample

dbcc freeproccache

go

 

--- Running query first time to get plan generated for freezing

set statistics xml on

exec sp_executesql

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

--- Based on query pattern creating a plan guide - freezing plan

declare @sql_text nvarchar(max),

        @sql_xml_plan nvarchar(max)

 

select

@sql_text=

    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),

@sql_xml_plan =

    convert(nvarchar(max),sqp.query_plan)

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%'

 

if @sql_text<>''

begin

       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

end

 

 

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 on

exec sp_executesql

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

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

GO

 

and try running the query again.

 

set statistics xml on

exec sp_executesql

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

 

 

To clean up your server after this demonstration:

 

--- Rebuild disabled index to enable it

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD

GO

 

--- Drop plan guide

EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';

 

 

SQL Server 2008

 

In SQL Server 2008 plan guides feature has been improved.

In addition to sp_create_plan_guide you can also usethe sp_create_plan_guide_from_handle stored procedure

 

Understanding plan guides

https://msdn.microsoft.com/en-us/library/ms190417(SQL.90).aspx

sp_create_plan_guide_from_handle

https://technet.microsoft.com/en-us/library/bb964726.aspx

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:

 

use AdventureWorks

go

 

--- Cleaning cache for this sample

dbcc freeproccache

go

 

--- Running query first time to get plan generated for freezing

set statistics xml on

exec sp_executesql

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

 

--- Based on query pattern creating a plan guide - freezing plan

declare @plan_handle varbinary(1000)

 

select @plan_handle = plan_handle

from sys.dm_exec_query_stats qs

       cross apply sys.dm_exec_sql_text(qs.sql_handle) sqt

where text like '%Employee%'

 

select @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

 

set statistics xml on

exec sp_executesql

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

 

Click on ShowPlanXML hyperlink

 

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

 

To clean up your server after this demonstration:

 

--- Drop plan guide

EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';

 

 

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