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

Submitted 12 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.





Others can perform a hardon but cannot maintain it during sexual intercourse. Use these circumspectly however, while they may lower blood glucose levels, which is an inadequate effect that face men whose glucose levels are properly balanced.

about 2 months ago

Add a comment: