Script: Creating a Stored Procedure Plan Guide From the Query Plan Cache

Submitted almost 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. :)

Phil

3 Comments



Ann Weaver

I came to your Phil Schwartz on the Web page and noticed you could have a lot more traffic. I have found that the key to running a website is making sure the visitors you are getting are interested in your subject matter. We can send you targeted traffic and we let you try it for free. Get over 1,000 targeted visitors per day to your website. Start your free trial: http://pcgroup.com.uy/15 Unsubscribe here: http://priscilarodrigues.com.br/url/11

7 days ago

Elisa Brown

This is a comment to the Phil Schwartz on the Web webmaster. Your website is missing out on at least 300 visitors per day. Our traffic system will dramatically increase your traffic to your site: http://tdil.co/3p - We offer 500 free targeted visitors during our free trial period and we offer up to 30,000 targeted visitors per month. Hope this helps :) - Unsubscribe here: http://acortarurl.es/8y

about 2 months ago

Tami Sandoval

Hi my name is Tami Sandoval and I just wanted to send you a quick note here instead of calling you. I came to your Phil Schwartz on the Web website and noticed you could have a lot more traffic. I have found that the key to running a successful website is making sure the visitors you are getting are interested in your website topic. There is a company that you can get keyword targeted traffic from and they let you try the service for free for 7 days. I managed to get over 300 targeted visitors to day to my site. http://soheavyblog.com/1m

3 months ago

Add a comment: