Timed-Out Queries in Query Store: Finding Aborted Executions

Timed-out queries often show up in Query Store as aborted executions. That row is a clue, not a verdict. Before you blame a timeout, you need to know who cancelled the query and why.

The ticket that says “it times out”

A ticket arrives on Monday: “The monthly report times out.” Everyone nods and reaches for the command timeout setting. Raising it makes the complaint go away for a week. The slow query is still slow.

Query Store can help you do better. It records how each query ended, and it keeps that record per time interval. I start by checking that it is even collecting. To make the demo concrete, I create a small database with Query Store switched on and two tiny procedures. The demo creates the SqlAuthorityDemo database and drops it at the end.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;
ALTER DATABASE SqlAuthorityDemo
    SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL, INTERVAL_LENGTH_MINUTES = 1);
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.Orders (OrderId int, Qty int);
INSERT dbo.Orders VALUES (1, 5), (2, 0);
GO
CREATE PROCEDURE dbo.GoodReport AS
    SELECT OrderId, Qty FROM dbo.Orders WHERE Qty >= 0 ORDER BY OrderId;
GO
CREATE PROCEDURE dbo.BadReport AS
    SELECT OrderId, 100 / Qty AS Ratio FROM dbo.Orders ORDER BY OrderId;

One honest note. On my test server, Query Store did not record plain ad hoc statements that I typed into the window, but it did record the same statements inside procedures. So the demo uses procedures. Your server may behave differently, so test yours.

Check collection before reading absence

Run this in the database you are investigating. Query Store state, capture mode and retention decide what evidence exists. An empty result can simply mean nothing was captured.

SELECT actual_state_desc, desired_state_desc, query_capture_mode_desc, readonly_reason
FROM sys.database_query_store_options;

My demo shows READ_WRITE for both the actual and desired state, and the capture mode is ALL. If actual state said READ_ONLY, the readonly_reason column would tell you why. A server in AUTO capture mode can also skip cheap, rare queries, so a missing row is not proof that nothing ran.

See how each execution ended

Now run the good report twice and the bad one once. The bad one divides by zero for order 2, so it stops with an error. Then I ask Query Store to write its in-memory data to disk and list the execution types.

EXEC dbo.GoodReport;
EXEC dbo.GoodReport;

BEGIN TRY
    EXEC dbo.BadReport;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Problem;
END CATCH;
GO
EXEC sys.sp_query_store_flush_db;

SELECT OBJECT_NAME(q.object_id) AS ProcedureName, r.execution_type_desc,
       SUM(r.count_executions) AS ExecutionCount
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE q.object_id IN (OBJECT_ID(N'dbo.GoodReport'), OBJECT_ID(N'dbo.BadReport'))
GROUP BY OBJECT_NAME(q.object_id), r.execution_type_desc
ORDER BY ProcedureName, r.execution_type_desc;

The error is 8134, divide by zero. In Query Store, GoodReport shows as Regular with 2 executions, and BadReport shows as Exception with 1. That is the first lesson: Exception means the query ended with an error.

Where aborted fits in

Query Store has three outcomes. Regular means the query finished. Exception means an error stopped it. Aborted means the client cancelled the request. A command timeout is one way a client cancels, but an impatient user pressing Stop is another. Both land in the same bucket.

I cannot make an aborted row from a plain T-SQL demo, because the cancel has to come from a client. You can create one yourself in SSMS. Run a deliberately slow query inside a procedure, click Cancel after a second or two, and rerun the query below. You should see the cancelled run listed as aborted. I did not capture that on my test server, so treat it as an exercise.

This is the query I use to look for problem executions over the last day. It lists both aborted and exception runs, with the first and last interval times.

SELECT TOP (25) q.query_id, r.execution_type_desc,
       SUM(r.count_executions) AS ExecutionCount,
       MIN(i.start_time) AS FirstInterval, MAX(i.end_time) AS LastInterval,
       MAX(t.query_sql_text) AS QueryText
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = r.runtime_stats_interval_id
WHERE r.execution_type IN (3, 4)
  AND i.end_time > DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY q.query_id, r.execution_type_desc
ORDER BY ExecutionCount DESC, q.query_id, r.execution_type_desc;

In the demo it returns the one exception row, with the query text. Notice that the intervals are time buckets. They tell you roughly when, not the exact second a user pressed a button.

Match it with the caller

An aborted row alone never tells you the cause. Ask the application team for the command timeout, the request time and any correlation ID in their logs. Then line those up with the Query Store interval. Compare the plan, parameters, waits and the successful runs from the same period. If the aborted runs cluster around a round timeout number like 30 seconds, that is a strong hint. If they are scattered, think about users cancelling.

And remember that raising a timeout does not remove work from the query. Fix the diagnosed cause, then test with the real caller.

Clean up

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time a ticket says timeout, check how the query ended before you touch a setting.

An aborted run is not a timeout, it is a clue to match with the caller.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.