Script – Find expensive or slow queries using Query Store

I have personally been a huge fan of Query Store ever since it was released with SQL Server 2016. It helps me simplify performance troubleshooting as it asynchronously captures a history of queries, plans and run time statistics, and stores this information within the database itself – which means that this data persists across server restarts, upgrades and query re-compiles.

Below is a script I commonly use to find answers to the following questions:
1) Which query (or queries) consumed high CPU, memory or IO during a given time interval?
2) What is the performance impact on my workload after a recent change/deployment?
3) A query has suddenly started performing slow. Is there a change in it’s execution plan?

The column headings are self-explanatory. You just need to adjust the ORDER BY clause based on the type of information you are looking for.

DECLARE @starttime AS DATETIME = 'MM/DD/YYYY hh:mm'
DECLARE @endtime AS DATETIME = 'MM/DD/YYYY hh:mm'

SELECT TOP 20 q.query_id AS [Query_ID]
	,rs.count_executions AS [Execution_Count]
	,rs.avg_duration / 1000 AS [Avg_Duration(ms)]
	,rs.max_duration / 1000 AS [Max_Duration(ms)]
	,rs.min_duration / 1000 AS [Min_Duration(ms)]
	,rs.avg_cpu_time / 1000 AS [Avg_CPU_time(ms)]
	,rs.max_cpu_time / 1000 AS [Max_CPU_time(ms)]
	,rs.count_executions * (rs.avg_cpu_time / 1000) AS [Total_CPU_Time(ms)]
	,q.avg_compile_duration / 1000 AS [Avg_Compile_Duration(ms)]
	,p.compatibility_level AS [Compatibility_Level]
	,rs.avg_logical_io_reads AS [Avg_Logical_Reads]
	,rs.max_logical_io_reads AS [Max_Logical_Reads]
	,rs.avg_logical_io_writes AS [Avg_Logical_Writes]
	,rs.max_logical_io_writes AS [Max_Logical_Writes]
	,rs.avg_physical_io_reads AS [Avg_Physical_Reads]
	,rs.max_physical_io_reads AS [Max_Physical_Reads]
	,rs.avg_dop AS [Avg_DOP]
	,rs.avg_query_max_used_memory * 8 AS [Avg_Memory(KB)]
	,rs.max_query_max_used_memory * 8 AS [Max_Memory(KB)]
--	,rs.avg_log_bytes_used / 1024 AS [Avg_Log_Used (KB)]		--Only for Azure SQL Database
--	,rs.max_log_bytes_used / 1024 AS [Max_Log_Used (KB)]		--Only for Azure SQL Database
	,qt.query_sql_text AS [Query_Text]
	,qt.query_text_id AS [Query_Text_ID]
	,p.plan_id AS [Plan_ID]
	,rs.last_execution_time AS [Last_Execution_Time]
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time BETWEEN @starttime	AND @endtime
ORDER BY rs.count_executions * rs.avg_cpu_time DESC -- by Total CPU Time
--ORDER BY rs.avg_cpu_time DESC		-- by CPU per execution
--ORDER BY rs.max_duration DESC		-- by Duration per execution
--ORDER BY rs.max_logical_io_reads DESC		-- by IO per execution
--ORDER BY rs.max_query_max_used_memory DESC		-- by memory per execution
--ORDER BY rs.count_executions * rs.avg_log_bytes_used DESC	--by Total Log Used (Only for Azure SQL Database)