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)
Wow! This is so helpful. Thank you 🙂
This was spot-on. Thank you!
I found your blog on Google. Good Job!