Systems Engineering and RDBMS

Plan Freezing for Plan Guides in SQL Server 2008

Posted by decipherinfosys on February 11, 2009

With SQL Server 2005, Microsoft had introduced the feature of plan guides – these allow us to have a much better control over the query plans and thus have more predictability and better performance for some of those queries that are tough to tune either because they are canned queries in a vendor application or the data distribution could be heavily skewed for the indexed columns leading to un-predictable results.  In a way, these allow us to simply influence the plan for the optimizer and either use a fixed query plan or force a plan by the usage of hints without changing the query in the code.  Other RDBMS like Oracle also have had such features – in Oracle, it is called a stored outline or optimizer plan stability – you can read more on this feature in Chris Foot’s posts here and here.

In SQL Server 2005, even though this feature was introduced and was very helpful, it still had a lot of limitations.  You can read more on plan guides in this technet article.  These were a bit cumbersome to use because of the issues around the formatting of the SQL statements and they supported only the SELECT statement queries.  In SQL Server 2008, the support has been extended to Update, Delete, Insert and the new Merge statements as well.  In addition, SQL Server 2008 introduces the new feature called plan freezing.  Using this feature, we can directly create a plan guide for any query plan that exists in the cache.  The system stored procedure that allows us to do this is:

sp_create_plan_guide_from_handle

And here is the syntax for it from BOL:

sp_create_plan_guide_from_handle [ @name = ] N’plan_guide_name’
, [ @plan_handle = ] plan_handle
, [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]

So, we pass in a plan handle and the starting position of the statement in the batch.   Let’s take this up with an example:

CREATE TABLE TEST1 (COL1 INT PRIMARY KEY, COL2 INT)
CREATE TABLE TEST2 (ID INT IDENTITY PRIMARY KEY, COL3 INT, COL4 INT, CONSTRAINT FK_COL3_TO_COL1 FOREIGN KEY (COL3) REFERENCES TEST1(COL1));

SET NOCOUNT ON
GO
insert into test1 values (1, 100);
insert into test1 values (2, 100000);
insert into test1 values (3, 1000);
insert into test1 values (4, 10);

insert into test2 values (1, 10000);
insert into test2 values (2, 100);
insert into test2 values (3, 1011);
insert into test2 values (3, 102);
insert into test2 values (2, 103);
insert into test2 values (4, 105);
GO

create index test2_ind_1 on test2 (col4, col3)
GO

Now, let’s run a simple query:

SELECT col1, col2
FROM dbo.test1 as T1
INNER JOIN dbo.Test2 AS T2
ON T1.COL1 = T2.COL3
WHERE T2.COL4 > 10;
GO

And before we get the plan handle and the statement start offset value, let’s look at the plan that was generated to ensure that is the one that we want to keep:

SELECT query_plan
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 col1, col2%’;

This is a pretty simple query but you get the idea.  If you do not like what you see, then add hints to the query to help generate your perfect plan.  Once that plan has been generated, you can then simply retrieve the plan handle and the statement start offset values and then proceed further.

Now, assuming that is the plan that we want to keep, let’s look at the plan cache now and retrieve the handle and the statement start offset values and then use those values to create the plan guide:

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 col1, col2%’;

EXECUTE sp_create_plan_guide_from_handle
@name =  N’Test_Guide’,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
GO

Now that the plan guide has been created, we should verify that it is in place:

SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N’SELECT col1, col2%’;

And then you can easily see through multiple different executions, that the same plan will be used henceforth for that query.  No more cumbersome issues related to formation of the plan guides.  And additional benefit is the added support for the other DML statements.

2 Responses to “Plan Freezing for Plan Guides in SQL Server 2008”

  1. […] was what we had mentioned in that post – using the “optimize for” query hint, usage of plan guides, recompiling (not advisable but can be suitable in certain scenarios), using a constant (again, not […]

  2. Peter Kupsovsky said

    Hi,

    I would like to add and share a method I have developed for SQL 2005 that allows you to ‘freeze’ plans based on the plan handle. Essentially what this script does is extract and format all the parameters required for sp_create_plan_guide from cache rather then go through the cumbersome method described in the documentation (via SQL Trace). The end effect is that you can ‘freeze’ a plan based on the plan handle

    create procedure [dbo].[EAM_DBA_FreezeExecPlan]
    (@sqlHandle varbinary(64) = NULL
    ,@PlanName sysname = NULL
    ,@debug int = 0)
    /***********************************************************************************************************************************************

    *
    *
    * Name: EAM_DBA_FreezeExecPlan
    * Author: Peter Kupsovsky
    * Date: 2009-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 | 2009-13-02 | PK | Initial Release
    *
    **************************************************************************************************************************************************/

    as

    begin

    if @sqlHandle 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(@sqlHandle) — the routine can run on SQL or Plan handle, this checks what we got and gets what it needs

    if @@rowcount = 0
    select @sqlHandle = sql_handle from sys.dm_exec_query_stats
    where plan_handle = @sqlHandle;

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

    select
    @plan = query_plan from sys.dm_exec_text_query_plan(@sqlHandle,0,-1) — this doesn’t work on my home machine, can’t find the dmv – possibly because not enterprive version??

    — @plan = convert(nvarchar(max),query_plan) from sys.dm_exec_query_plan(@sqlHandle) — this works everywhere

    select @statement = replace(replace(t.text,’>’,’>’),'<‘,’ 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

    As you can see it is quite simple. Off course You need to know what to look for in cache. We use sys.dm_exec_query_stats and sys.dm_exec_sql_text to find problem queries. Mostly a sub optimal plan is created when reusable queries run with non typical parameter values and thus create a bad execution path. Simply forcing the bad plan out generally results in obtaining a good plan in the new compilation. This process may have to be repeated a few times. Once a good plan is produced freezing it into a plan_guide removes this problem for good (or until the query morphs (we are running a third party product and queries sometime change with new releases.)) It is important to continue reviewing query performance to identify problems early.

    below is a script that will force a query out of cache, again based on plan handle as a parameter:

    create procedure [dbo].[CPSF_DBA_ClearCachedPlan]
    (@sqlHandle varbinary(64) = NULL
    ,@debug int = 0)

    as

    begin

    if @sqlHandle is NULL
    begin
    return
    end;

    declare @statement nvarchar(max),
    @parms nvarchar(max),
    @level int,
    @pos int;

    set @parms = NULL;

    set @level = 0;
    set @pos = 1;

    select 1
    from sys.dm_exec_sql_text(@sqlHandle)

    if @@rowcount = 0
    select @sqlHandle = sql_handle from sys.dm_exec_query_stats
    where plan_handle = @sqlHandle;

    select @statement = replace(replace(t.text,’>’,’>’),'<‘,’ 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));

    if @debug > 0
    begin
    select @parms;
    select @statement;
    if @debug > 1
    return;
    end;

    exec sp_create_plan_guide
    @name = N’ForcedRecompilePlanGuide’,
    @stmt = @statement,
    @type = N’SQL’,
    @module_or_batch = NULL,
    @params = @parms,
    @hints = N’OPTION(RECOMPILE)’;

    exec sp_control_plan_guide N’DROP’, N’ForcedRecompilePlanGuide’;

    end — procedure

    enjoy🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: