Script: Creating a Stored Procedure Plan Guide From the Query Plan Cache
Submitted about 2 years ago
Dealing with a system regularly you begin to learn how long certain procs should be running for, and sometimes they get out of wack. Dealing with some pretty gnarly queries the plan guide can change and SQL will take a different route for executing the sproc. When you notice it's back to normal, after a forced recompile or SQL generating a new plan in the cache, you may want to lock that bad boy in so that way your day is much better. :)
Here's a pretty simple script that just takes the sproc name as a parameter.
declare @sproc_name varchar(150) = 'prc_Example' declare @plan_guide_name varchar(150) = (SELECT SUBSTRING('SRP - 1 - ' + @sproc_name, 0, 123)) declare @planhandle varbinary(64) IF EXISTS (SELECT 1 FROM sys.plan_guides WHERE name = @plan_guide_name) BEGIN EXEC sp_control_plan_guide @operation = N'DROP', @name = @plan_guide_name END ;WITH sqlplan as ( select OBJECT_NAME(objectid) as Name ,creation_time ,execution_count ,a.plan_generation_num ,plan_handle ,total_worker_time ,[text] from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text WHERE OBJECT_NAME(objectid) = @sproc_name ) SELECT @planhandle = (SELECT TOP 1 plan_handle FROM sqlplan ORDER BY creation_time DESC) exec sp_create_plan_guide_from_handle @name = @plan_guide_name ,@plan_handle = @planhandle; exec sp_control_plan_guide @name = @plan_guide_name, @operation = N'ENABLE' PRINT N'exec sp_control_plan_guide @name = N''' + @plan_guide_name + ''', @operation = N''DISABLE''; GO' GO
This script should work for most use cases, however, sometimes you may get an error saying something along the lines of "The plan handle is invalid and cannot be used as a plan guide because it is malformed" (I forgot the actual error message, but I've seen it before).
Also note that I print out the query to DISABLE the plan guide, in case the wrong plan guide is locked in. So keep that in mind, and keep that handy and ready to execute.
Another note, if the plan cache contains a handle involving multiple queries the plan guide creation will create two (or more) plan guides and auto increment them to something like:
SRP - 1 - StoredProc_998
SRP - 1 - StoredProc_999
so keep that in mind as well. This happens because the object (sproc) actually has two transactions included in the cached query stat, for example an INSERT and then a SELECT in the same object, this causes the "exec sp_create_plan_guide_from_handle" to create two different plan guides from the same query plan handle.
Cheers, and be careful of the impact of the plan_guide creation. Be prepared to DISABLE the guide immediately if needed. :)