The Problem: Slow Query Store Interface
The Query Store in SSMS is a powerful tool for tracking query performance, but its graphical interface can become a bottleneck, particularly when dealing with large datasets or complex environments.
The Solution: Direct Query to the Query Store views
The following T-SQL query is designed to pull detailed performance metrics from the Query Store for a specified time range. It returns the same data that you see in the detailed grid of the ‘Top Resource Consuming Queries’ in SSMS.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
DECLARE @start_time DATETIMEOFFSET(7) = CAST(DATEADD(day, -30, SYSDATETIMEOFFSET()) AS datetimeoffset(7)) DECLARE @end_time DATETIMEOFFSET(7) = CAST(DATEADD(day, -1, SYSDATETIMEOFFSET()) AS datetimeoffset(7)) SELECT @start_time [start_time], @end_time [end_time], p.query_id query_id, q.object_id object_id, OBJECT_NAME(q.object_id) [object_name], qt.query_sql_text query_sql_text, ROUND(CONVERT(float, SUM(rs.avg_duration * rs.count_executions)) / (SUM(rs.count_executions)) * 0.001,2) [avg duration], ROUND(CONVERT(float, SUM(rs.avg_cpu_time * rs.count_executions)) / (SUM(rs.count_executions)) * 0.001 ,2) [avg cpu time], ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes * rs.count_executions)) / (SUM(rs.count_executions)) * 8 ,2) [avg logical writes], ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads * rs.count_executions)) / (SUM(rs.count_executions)) * 8 ,2) [avg logical reads], ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads * rs.count_executions)) / (SUM(rs.count_executions)) * 8,2) [avg physical reads], ROUND(CONVERT(float, SUM(rs.avg_clr_time * rs.count_executions)) / (SUM(rs.count_executions)) * 0.001,2) [avg clr time], ROUND(CONVERT(float, SUM(rs.avg_dop * rs.count_executions)) / (SUM(rs.count_executions)) * 1,0) [avg dop], ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory * rs.count_executions)) / (SUM(rs.count_executions)) * 8,2) [avg memory consumption], ROUND(CONVERT(float, SUM(rs.avg_rowcount * rs.count_executions)) / (SUM(rs.count_executions)) ,0) [avg row count], ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used * rs.count_executions)) / (SUM(rs.count_executions)) * 0.0009765625,2) [avg log memory used], ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) * 0.001,2) [total duration], ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions)) * 0.001,2) [total cpu time], ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions)) * 8,2) [total logical reads], ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions)) * 8,2) [total logical writes], ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions)) * 8,2) [total physical reads], ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions)) * 0.001,2) [total clr time], ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions)) * 1,0) [total dop], ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions)) * 8,2) [total memory consumption], ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions)) * 1,0) [total row count], ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions)) * 0.0009765625,2) [total log memory used], ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions)) * 8,2) [total tempdb memory used], SUM(rs.count_executions) [execution count], COUNT(distinct p.plan_id) num_plans FROM sys.query_store_runtime_stats rs INNER JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id INNER JOIN sys.query_store_query q ON q.query_id = p.query_id INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id WHERE NOT (rs.last_execution_time < @start_time OR rs.first_execution_time > @end_time) GROUP BY p.query_id, qt.query_sql_text, q.object_id OPTION (RECOMPILE) |
Benefits of This Approach
- Speed: By querying the data directly, you bypass the SSMS interface.
- Customization: You can easily modify the query to focus on the metrics that matter most to you, adding or removing columns as needed.
Conclusion
If you don’t want to wait for the Query Store interface in SSMS to load, this T-SQL query offers a fast and flexible alternative. You can quickly gather the performance data you need to troubleshoot and optimize your SQL Server database.
Share via:
