Script: Slow Query Store dashboards

 --USE YourDB
DECLARE @badqry NVARCHAR(MAX)
 
SELECT TOP (1) @badqry = SUBSTRING(st.text, (r.statement_start_offset/2) + 1, 
    ((CASE statement_end_offset  
        WHEN -1 THEN DATALENGTH(st.text) 
        ELSE r.statement_end_offset
    END - r.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE st.text LIKE '(@results_row_count int,@interval_start_time%'
 
IF @badqry IS NULL
RAISERROR('Missed the plan',16,1)
 
EXEC sp_create_plan_guide
@name = N'Fixing QueryStore Top Duration', -- Change name for each resource dashboard
@stmt = @badqry,
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',
@hints = N'OPTION (HASH JOIN, LOOP JOIN)'