Query Store Runtime Statistics and Wait Statistics

This SQL script queries the Query Store Catalog Views to extract Runtime and Wait Statistics for the slowest query in a stored procedure, specifically within a designated time frame. The database must have the Query Store feature enabled first.

SELECT SYSDATETIMEOFFSET();

USE AppDB; -- DB name here

GO

SET XACT_ABORT ON;

SET NOCOUNT ON;

BEGIN TRAN

SELECT OBJECT_NAME(qsq.object_id) AS ObjName,

qsq.query_id, [plan].plan_id, qsqt.query_text_id, qsqt.query_sql_text, RS.runtime_stats_id, Interval.runtime_stats_interval_id,

qsq.last_execution_time AS query_last_exec_time, [plan].last_execution_time AS plan_last_exec_time,

Interval.start_time AS interval_start, Interval.end_time AS interval_end,

RS.first_execution_time AS RS_1st_exec_time, RS.last_execution_time AS RS_last_exec_time, RS.count_executions,

RS.avg_duration/1000.0/1000.0 AS avg_secs, RS.max_duration/1000.0/1000.0 AS max_secs, RS.min_duration/1000.0/1000.0 AS min_secs, RS.execution_type_desc,

RS.avg_logical_io_reads, RS.max_logical_io_reads, RS.avg_rowcount, RS.max_rowcount, RS.avg_logical_io_writes, RS.max_logical_io_writes, RS.avg_physical_io_reads, RS.max_physical_io_reads, RS.avg_tempdb_space_used, RS.max_tempdb_space_used

INTO #Temp1_QS_Perf_Analysis

FROM sys.query_store_query qsq

JOIN sys.query_store_query_text qsqt

ON qsq.query_text_id = qsqt.query_text_id

JOIN sys.query_store_plan [plan]

ON qsq.query_id = [plan].query_id

JOIN sys.query_store_runtime_stats RS

ON RS.plan_id = [plan].plan_id

JOIN sys.query_store_runtime_stats_interval Interval

ON RS.runtime_stats_interval_id = Interval.runtime_stats_interval_id

WHERE

/* Modify Criteria Here */

qsq.object_id IN (

OBJECT_ID('AppSP')) -- stored proc name here

AND qsq.last_execution_time >= '20250408 00:00'

AND Interval.start_time >= '20250408 00:00' AND Interval.end_time <= '20250408 04:00'

AND [plan].last_execution_time >= '20250408 00:00'

;

 

DECLARE @SlowQueryId bigint, @SlowPlanId bigint, @SlowIntervalId bigint, @FastPlanId bigint, @FastIntervalId bigint;

SELECT TOP 1 'SLOWEST', * FROM #Temp1_QS_Perf_Analysis ORDER BY max_secs DESC;

SELECT TOP 1 @SlowQueryId=query_id, @SlowPlanId=plan_id, @SlowIntervalId=runtime_stats_interval_id FROM #Temp1_QS_Perf_Analysis ORDER BY max_secs DESC;

SELECT TOP 1 'FASTEST', * FROM #Temp1_QS_Perf_Analysis WHERE query_id=@SlowQueryId AND avg_rowcount>0 ORDER BY min_secs;

SELECT TOP 1 @FastPlanId=plan_id, @FastIntervalId=runtime_stats_interval_id FROM #Temp1_QS_Perf_Analysis WHERE query_id=@SlowQueryId ORDER BY min_secs;

SELECT * FROM #Temp1_QS_Perf_Analysis WHERE query_id=@SlowQueryId ORDER BY max_secs;

-- WAIT

SELECT 'SLOW Wait', wait_category_desc, execution_type_desc, avg_query_wait_time_ms/1000 AS avg_wait_time_Seconds

FROM sys.query_store_wait_stats

WHERE plan_id=@SlowPlanId AND runtime_stats_interval_id=@SlowIntervalId

--GROUP BY wait_category_desc, execution_type_desc

ORDER BY avg_wait_time_Seconds DESC;

SELECT 'FAST Wait', wait_category_desc, execution_type_desc, avg_query_wait_time_ms/1000 AS avg_wait_time_Seconds

FROM sys.query_store_wait_stats

WHERE plan_id=@FastPlanId AND runtime_stats_interval_id=@FastIntervalId

--GROUP BY wait_category_desc, execution_type_desc

ORDER BY avg_wait_time_Seconds DESC;

 

DROP TABLE #Temp1_QS_Perf_Analysis;

ROLLBACK

Demonstrative result: