When I work with Query Store, I often need to find the executions that didn’t finish successfully, queries that were aborted or ended with an exception. In the SSMS interface, you’d normally look for a square icon (client-abort) or a triangle icon (runtime error) . Trying to find those by clicking through the dashboard is slow.
-
An aborted execution can come from exceeding the client execution timeout.
-
An exception might occur when, for example, a 5-character string is inserted into a
CHAR(2)column.
Retrieve failed executions with T-SQL
The T‑SQL query provided below lets you filter for these failed executions. It retrieves time window, together with the plan_id/query_id, execution count, Query plan, and SQL text.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT qsrsi.[start_time], qsrsi.[end_time], qsrs.[plan_id], qsq.[query_id], qsrs.[execution_type_desc], qsrs.[count_executions] AS [failed_executions], try_convert(xml,qsp.[query_plan]) AS [query_plan], qsqt.[query_sql_text] FROM [sys].[query_store_runtime_stats] qsrs INNER JOIN [sys].[query_store_runtime_stats_interval] qsrsi ON qsrs.[runtime_stats_interval_id] = qsrsi.[runtime_stats_interval_id] INNER JOIN [sys].[query_store_plan] qsp ON qsrs.[plan_id] = qsp.[plan_id] INNER JOIN [sys].[query_store_query] qsq ON qsp.[query_id] = qsq.[query_id] INNER JOIN [sys].[query_store_query_text] qsqt ON qsq.[query_text_id] = qsqt.[query_text_id] WHERE qsrs.[execution_type_desc] IN ('Aborted', 'Exception') ORDER BY qsrsi.[start_time] desc |
Retrieve wait stats for aborted queries
When a query got aborted, it can be useful to get information about the wait statistics related to that query. The wait-stats can point you into the right direction. To retrieve the wait-stats you can use this query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT qsrsi.[start_time], qsrsi.[end_time], qsrs.[plan_id], qsrs.[execution_type_desc], qsrs.[count_executions], qsws.[wait_category_desc], qsws.[total_query_wait_time_ms], qsws.[avg_query_wait_time_ms] FROM [sys].[query_store_runtime_stats] qsrs INNER JOIN [sys].[query_store_wait_stats] qsws ON qsrs.[runtime_stats_interval_id] = qsws.[runtime_stats_interval_id] AND qsrs.[plan_id] = qsws.[plan_id] AND qsrs.[execution_type_desc] = qsws.[execution_type_desc] INNER JOIN [sys].[query_store_runtime_stats_interval] qsrsi ON qsrs.[runtime_stats_interval_id] = qsrsi.[runtime_stats_interval_id] WHERE qsrs.[execution_type_desc] IN ('Aborted') ORDER BY qsrsi.[start_time] desc |
Note: sys.query_store_wait_stats is only available in SQL Server 2017+ or Azure SQL Database, and requires WAIT_STATS_CAPTURE_MODE to be enabled (default on new databases)
Share via:
