Plan Guides on SQL Server

A bulletproof implementation method

A Plan Guide is a way to add a hint to a SQL statement if you can’t change the SQL statement at the source. This performance trick is hidden deep into the performance toolbox and shouldn’t be the first weapon of choice. I found that implementing planguides a bit of a delicate excercise , both on Oracle and SQL server. A small and insignificant copy/paste error seems to leave the planguide inoperable. I therefor use the following methode for SQL server which consists of 5 steps:

(1) retrieve the SQL_handle from the cache
(2) retrieve the executionplan and store this in a planguide
(3) store the planguide in a file
(4) edit the executionplan in this file
(5) drop the old planguide and load the new one from the edited file.

So let’s have a look at those steps in turn:

(1) retrieve the SQL_handle from the cache

Update: I gave an SQL statement that assumed the same session. This one is better:

With SQLHandleview as
(
SELECT SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text,
qs.sql_handle
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) st
)

select * from SQLHandleview
where
statement_text like ‘%delete from device_context where dev_varnam =%’

Of course, you should adjust the query to find the query you’re looking for in the Where clause.

(2) retrieve the executionplan and store this in a planguide

declare @plan_handle varbinary(64);
declare @offset int;
declare @plan_name nvarchar(128);

select @plan_handle = qs.plan_handle
, @offset = qs.statement_start_offset
, @plan_name = ‘pg_’ + convert( varchar(128), qs.sql_handle, 1 )
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 qs.sql_handle = (0x02000000B9B415076207F052F2B4D7FB13AA4A5041C20CFC00000000000000000000000)
and qp.dbid = DB_ID();

execute sp_create_plan_guide_from_handle
@name = @plan_name,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;

Insert your SQL_handle in the code.

(3) store the planguide in a file

Store the PlanGuide into a file

(4) edit the executionplan in this file

/****** Object:  PlanGuide pg_0x0200000066091D031E28B462D77B6F735FC1351A92C3A2C80    ******/

EXEC sp_create_plan_guide

@name = N'[pg_0x0200000066091D031E28B462D77B6F735FC1351A92C3A2C80000]’,

@stmt = N’select d.dwnld_seq, d.sys_id, d.dwnld_stat_cd from sl_dwnld d where d.dwnld_stat_cd =  @P0  and d.sys_id like  @P1  order by dwnld_seq’,

@type = N’SQL’,

@module_or_batch = N’select d.dwnld_seq, d.sys_id, d.dwnld_stat_cd from sl_dwnld d where d.dwnld_stat_cd =  @P0  and d.sys_id like  @P1  order by dwnld_seq’,

@params = N’@P0 nvarchar(4000),@P1 nvarchar(4000)’,

@hints = N’option ( optimize for ( @P0 = ”EC” ) )’

As you can see, I inserted an OPTIMISE FOR hint into the SQL text.

(5) drop the old planguide and load the new one from the edited file.

Script/File option

In order to activate the planguide you should drop the present plan from the cache:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

One thought on “Plan Guides on SQL Server

Leave a Reply

Your email address will not be published.