On occasion you may have a query that starts picking up a bad plan as a result of an execution plan regression. This can be for a variety of reasons (stats, parameter sniffing, etc). If this occurs on a regular basis and you don’t have the luxury of query store, you can use sp_create_plan_guide_from_handle in order to pin the good plan in the plan cache. Under the covers this command creates an XML plan guide.
sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'
, [ @plan_handle = ] plan_handle
, [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]
There are three parameters to pass:
The @name is the name that will be used for the plan guide that gets created.
The @plan_handle needs to be retrieve from the plan cache. I will provide more info on that later.
The @statement_start_offset indicates which statement in a stored procedure or a batch gets a plan guide created for it. This is an optional parameter. If you do not provide this a plan guide will be created for every statement in the batch or stored procedure.
Finding the @plan_handle
You can use sys.dm_exec_query_stats to find a plan_handle. If you are attempting to find a plan handle for a stored procedure you can start by searching for it with the following query. Be sure to change the database name and the stored procedure name.
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, qs.statement_start_offset, qs.plan_handle, db_name(st.dbid) as db,
qs.creation_time, qs.last_execution_time,
object_name(st.objectid, st.dbid) as proc_name
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE object_name(st.objectid, st.dbid) = 'CreateStats'
AND db_name(st.dbid) = 'atmhrkjt'
Make note of the sql_handle – this will be useful in a later step where we validate that the plan guide took effect.
If you are attempting to create a plan guide for a query that is not a stored procedure, you can run the following query. Just change the text in the like clause wildcard to match something in your query (along with the database name). I typically look for comments in the query (if any exist) since these seem to be pretty unique.
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, qs.statement_start_offset, qs.plan_handle,
db_name(CONVERT(INT, pa.value)) as db,
qs.creation_time, qs.last_execution_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE st.text like '%INSERT INTO ExecQueryWaitStats30%'
AND pa.attribute='dbid'
AND db_name(CONVERT(INT, pa.value)) = 'atmhrkjt'
If you are a SQLGrease user this is much simpler. You can run the following query to locate the plan by the query hash you are seeing in SQLGrease:
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, qs.statement_start_offset, qs.plan_handle,
db_name(CONVERT(INT, pa.value)) as db,
qs.creation_time, qs.last_execution_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE qs.query_hash = 0xC8053072089930A1
AND pa.attribute='dbid'
AND db_name(CONVERT(INT, pa.value)) = 'atmhrkjt'
Running the command
Very important note be sure to run this in the database where the query executes. If you do not run this in the right database it will not work.
Here’s what our command looks like:
USE atmhrkjt
exec sp_create_plan_guide_from_handle @name = N'demo plan guide',
@plan_handle = 0x06002A00C102EC1F305CC6750300000001000000000000000000000000000000000000000000000000000000,
@statement_start_offset = 1538
How to validate the plan guide
In order to validate the plan guide is working you can re-run the queries you used to find the plan handle and look in the execution plan. If the query is using a plan guide it will show up in the XML plan (doesn’t work in sql 2008). Keep in mind, you will not be able to find the plan in the cache again until it actually executes. In other words you really can’t validate that the plan guide took until after the next query execution.
SELECT CONVERT(XML, query_plan) FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_text_query_plan(qs.plan_handle,
qs.statement_start_offset, qs.statement_end_offset)
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE qs.sql_handle = 0x02000000C102EC1FA39C991D4065FCA7E1AE843981D65B480000000000000000000000000000000000000000
AND qs.statement_start_offset = 1538
AND pa.attribute='dbid'
AND db_name(CONVERT(INT, pa.value)) = 'atmhrkjt'
If you search for PlanGuideName and PlanGuideDB it will show you the plan guide we just created if the query is using it.
If you are on SQL 2008 you can use a profiler trace. Look for this under the Event: Performance/Plan Guide Successful.
Caveats
Of course there’s the big one… Should you really be forcing execution plans? In some cases yes – it really depends on your environment. I prefer fixing things by writing better SQL rather than tweaking things; however, there are cases when that’s not possible.
The plan_handle can possibly change between the time you find it and the time you attempt to create the plan guide – that’s why I included the created column. This helps you determine how volatile what’s in your plan cache might be. If the created column consistently shows the current date time you might not be able to use this approach to create the plan guide.
Watch us diagnose a query timeout with SQLGrease:
See how you can get query level wait events without Query Store:
SQLGrease can help you find plan regressions even if you are not on SQL 2016 or later.