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

Submitted 8 months ago

This is a continuation on an old post, that involved locking in plans that only include ONE batch (sad trombone). Here's a more(?) complicated "script" (set of instructions) that will lock in multiple batches as separate batches per plan guide...

Lets see what plan guides are created for the individual batches

-- Examine the query plans for this sproc
DECLARE @ProcName NVARCHAR(100) = 'prc_SomeProc'

SELECT x.text, qs.statement_start_offset, qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) x
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE x.objectid = object_id(@ProcName);

Okay.. so we have a ton of batches... (omitting the text)

statement_start_offset   plan_handle
686                      0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
1388                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
1754                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
3056                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
7464                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
8070                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
9000                     0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000
10456                    0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000

Well, completely scripting this out is non-trivial, and limited because it is required to execute this in a single batch... Yes that means no cursors to iterate over the results.

So we will end up pasting the OFFSET, and the same plan_handle per batch... ugh..

-- Run these one by one grabbing values from the temp table
DECLARE @plan_handle varbinary(64) = 0x05000E003F7F763C30DF07055301000001000000000000000000000000000000000000000000000000000000;
DECLARE @offset int = 10456;
DECLARE @n NVARCHAR(64) = SUBSTRING(convert(nvarchar(64),NEWID()),0,9)
DECLARE @Name NVARCHAR(64) = 'prc_SomeProc' + @n;

EXECUTE sp_create_plan_guide_from_handle 
    @name =  @Name,
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;


Verifying the results..

SELECT * FROM sys.plan_guides WHERE Name like '%prc_SomeProc%'

Still kinda ugly... but hey... I like sleep. Also this is SQL2014, Query Store (SQL2016) may solve for this.




Add a comment: