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

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

 

 Description of sp_create_plan_guide

http://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.


clip_image002[15]

If you don’t have the AdventureWorks database you can download it from:

http://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

clip_image002[17]

 

clip_image002[19]

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 use the sp_create_plan_guide_from_handle stored procedure

 

Understanding plan guides

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

 

sp_create_plan_guide_from_handle

http://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

clip_image002[17]

 

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

clip_image002[21]

 

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

clip_image002[23]

 

Posted by: Sergey Pustovit – SQL Server Escalation Services

 

Comments (7)

  1. Hi Sergey,

    Your post was most interesting and I wish I came upon it last year (2007) 🙂  Your example for SQL server 2005 is excellent and totaly in line with my thinking.  I think the whole Microsoft documented process of extracting XML from traces and cutting and pasting things into sp_create_plan_guide is ridiculously complex and unnecessary as you have demonstrated in your example.

    In our case it is not sufficient however to look for an SQL pattern in cache as our server runs thousands of queries and they are all very similar (Siebel).  Our problem is that some queries are initially compiled with certain parameter values which are not representative of the majority of query calls.  This results in sub-optimal execution plans which persist and create performance bottlenecks.  We were excited about the introduction of plan guides as it seemed to promisse a solution to our problem only to be disapointed with the cumbersome creation and management practices.

    Similar to you creating a plan guide from the cache entry, I have developed a script which allows the ‘freezing’ of a known ‘good’ plan straight from cache, based on the plan handle.  This is essentially identical to the SQL 2008 method, however, it works fine in 2005. (We are not in a position to move to 2008 for a while yet).  I’m including the script so that other 2005 sites may benefit.

    I have also developed a tool/method to monitor poorly performing queries and identify alternate/better performing execution plans.  Let me know if you are interested in more details.

    regards

    Peter Kupsovsky

    SQL Server DBA

    pkupsovsky@bigpond.com

    create procedure [dbo].[EAM_DBA_FreezeExecPlan]

    (@planHandle varbinary(64) = NULL

    ,@PlanName sysname = NULL

    ,@debug int = 0)

    /***********************************************************************************************************************************************

    *

    *

    *       Name:           EAM_DBA_FreezeExecPlan

    *       Author:         Peter Kupsovsky

    *       Date:           2008-02-13

    *

    *       Purpose:        Create a plan guide from currently executing plan in cache (freeze plan SQL 2008)

    *

    *       Input:          plan Handle

    *

    *       Output:         Plan Guide is created for query pointed to by supplied handle

    *

    *       Revision Control

    *       ———————————————————————————————————————————————-

    *       |  Revision     |  Date         |  Changed By   |  Comments            

    *       ———————————————————————————————————————————————-

    *       |  0.1          |  2008-13-02   |  PK           | Initial Release

    *

    **************************************************************************************************************************************************/

    as

    begin

    if @planHandle is NULL

    begin

           return

    end;

    declare @statement nvarchar(max),

           @parms nvarchar(max),

           @plan nvarchar(max),

           @level int,

           @pos int;

    set @parms = NULL;

    set @level = 0;

    set @pos = 1;

    select 1

           from sys.dm_exec_sql_text(@planHandle)

    if @@rowcount = 0

           select @planHandle = sql_handle from sys.dm_exec_query_stats

           where plan_handle = @planHandle;

    — cannot use cross apply in compatibility mode 80 for Siebel so must code around it

    –select

    —      @statement = t.text,

    —      @plan = p.query_plan

    —      from sys.dm_exec_query_stats qs

    —      cross apply sys.dm_exec_sql_text(qs.sql_handle) t

    —      cross apply sys.dm_exec_text_query_plan(qs.plan_handle,0,-1) p

    —      where plan_handle = @planHandle;

    select

           @statement = [text] from sys.dm_exec_sql_text(@planHandle)

    select

           @plan = convert(nvarchar(max),query_plan) from sys.dm_exec_query_plan(@planHandle)

    select @statement = replace(replace(t.text,’&gt;’,’>’),’&lt;’,'<‘)

           from sys.dm_exec_sql_text(@planHandle) t

    if @@rowcount = 0  — handle does not return any plans

     begin

           print ‘The Handle supplied does not link to an existing plan’

           return

     end;

    — strips out parameters if present

    if substring(@statement,1,1) = ‘(‘ — is the first thing a parameter?

           begin

           set @level = @level + 1 — we are into parm extraction

           while @level > 0

                   begin

                   set @pos = @pos + 1;

                   if substring(@statement,@pos,1) = ‘(‘

                           set @level = @level + 1;

                   if substring(@statement,@pos,1) = ‘)’

                           set @level = @level – 1;

                   end — while

           set @parms = substring(@statement,2,@pos-2); — pull out the parameters

           set @pos = @pos + 1; — move statement start to one place after last partameter bracket BUT ONLY if we had parms!!!

           end; — if

    set @statement = substring(@statement,@pos,len(@statement));

    set @plan = ‘OPTION(USE PLAN N”’+@plan+”’)’;

    if @debug > 0

     begin

           select @parms;

           select @statement;

     if @debug > 1

           return;

     end;

    exec sp_create_plan_guide

    @name = @PlanName,

    @stmt = @statement,

    @type = N’SQL’,

    @module_or_batch = NULL,

    @params = @parms,

    @hints = @plan;

    end — procedure

    DECLARE @RC int

    DECLARE @sqlHandle varbinary(64)

    DECLARE @PlanName sysname

    DECLARE @debug int

    — TODO: Set parameter values here.

    set @planHandle = 0x060006005E131C2CB881C807000000000000000000000000

    set @PlanName = ‘AdventureWorksEmployees’

    EXECUTE @RC = [EAM_DBA_FreezeExecPlan]

      @planHandle

     ,@PlanName

     ,@debug

  2. sqlke@live.com says:

    Hello Peter.

    Thanks for the comment!

    This makes sense.

    If you will use the example above you may

    also change search condition

      where text like ‘%Employee%’

    to

      where plan_handle = ….

    to use plan handle as a search criteria, however you have to pull plan handle first anyway.

    Thanks again,

    Sergey.

  3. Great post, thanks!

    The IT Operation

    (www.theitoperation.com)

  4. Great post, thanks!

    The IT Operation

    (www.theitoperation.com)

  5. Name says:

    Very Informative post…!

    Also the explanation was easy to understand.

    Great work.

  6. Dinesh Vishe says:

    easy to understand