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
(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.
In order to activate the planguide you should drop the present plan from the cache:
DBCC FREEPROCCACHE (plan_handle_id_goes_here)