QueryStore – Regressed queries based on execution count

Handy TSQL statement to find gergressed queries on a given time based on execution count.

I had a scenario to validate if application is executing any statements more frequently on a given time. Following SQL can be used to find that information.

In summary, History time is overall time and Recent time is the time where we need to analyze.
Query evaluates number of executions per minute by each SQL statement on given time periods and compares which statement execution counts went up. Numbers are modified to address 0 and very small execution counts.





--
-- Querystore - Regressed SQL statements based on Average execution time 
--

--- "Recent" workload 
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
DECLARE @recent_minutes float;  
SET @recent_start_time =  '2020-01-13 07:00:00.0000000 +00:00' --  -- DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = '2020-01-13 12:00:00.0000000 +00:00' -- SYSUTCDATETIME();  
SELECT @recent_minutes=datediff(minute,@recent_start_time,@recent_end_time)
  
--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
DECLARE @history_minutes float;  
SET @history_start_time = '2020-01-16 07:00:00.0000000 +00:00' -- DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = '2020-01-16 12:00:00.0000000 +00:00' -- SYSUTCDATETIME();  
SELECT @history_minutes=datediff(minute,@history_start_time,@history_end_time)

;WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time  @history_start_time)  
        OR (rs.first_execution_time  @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time  @recent_start_time)  
        OR (rs.first_execution_time  @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
	,results.averageExecTime_Recent
	,results.averageExecTime_History
,ExecutioncountPerMinute_Recent
,ExecutioncountPerMinute_History
,col1, col2
,additional_executioncount_workload

FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
		,ROUND(CONVERT(float, recent.total_duration/recent.count_executions)  , 2) AS averageExecTime_Recent
		,ROUND(CONVERT(float, hist.total_duration/hist.count_executions)  , 2) AS averageExecTime_History
		,ROUND(CONVERT(float, recent.count_executions / @recent_minutes)  , 2) AS ExecutioncountPerMinute_Recent
		,ROUND(CONVERT(float, hist.count_executions / @history_minutes)  , 2) AS ExecutioncountPerMinute_History
		,(recent.count_executions+100) / @recent_minutes  as Col1
		,(hist.count_executions+100) / @history_minutes as Col2 
		,ROUND(CONVERT(float, ( (recent.count_executions+100) / @recent_minutes ) / ( (hist.count_executions+100) / @history_minutes) )  , 2) AS additional_executioncount_workload
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE 1=1
AND additional_duration_workload > 0  
-- AND additional_executioncount_workload > 0
ORDER BY additional_duration_workload DESC  
-- ORDER BY additional_executioncount_workload DESC  
OPTION (MERGE JOIN);  



Ref: Modified base statement from Microsoft documentation:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

This entry was posted in knowledge, Performance, Script, SQL, SQL Query, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s