SQL Server - Plan Guide

planGuide.sql

DECLARE @planguide nvarchar(max) = 'PlanGuide'
 
SELECT st.dbid, st.text, qs.*, qp.*
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    cross apply sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE CONVERT(date, last_execution_time) = CONVERT(date, getdate())
    AND st.text like '%query text%'
    AND st.text not like '%e971a5bc-cd6c-4465-9232-2cb419163112%'
 
DECLARE @handle varbinary(64)
set @handle = (
SELECT top 1 qs.plan_handle
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    --cross apply sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE CONVERT(date, last_execution_time) = CONVERT(date, getdate())
    AND st.text like '%query text%'
    AND st.text not like '%e971a5bc-cd6c-4465-9232-2cb419163112%'
)
 
select @handle
exec sp_create_plan_guide_from_handle @name = @planguide, @plan_handle = @handle