Timeouts and Failed Executions
Overview
Every other page measures work that finished, or mixes these executions in with work that finished without saying so. This report is about the executions that never returned a result, and what they cost on the way to returning nothing.
Query Store keeps a separate row for each outcome in sys.query_store_runtime_stats.execution_type:
| Value | Outcome | What it usually means |
|---|---|---|
| 0 | Regular | The execution ran to the end. |
| 3 | Aborted | The client stopped waiting and canceled it. Almost always a command timeout, sometimes a user pressing stop. |
| 4 | Exception | The statement ended in an error: a deadlock victim, a divide by zero, a conversion that failed. |
Summed in with the rest, an abandoned execution disappears. A query that times out after thirty seconds on one call in fifty adds about half a second to its own average, which moves it nowhere on a page ranked by average duration, and the users who saw the spinner appear on no page at all.
Time thrown away
The number this page is built around is time thrown away: the elapsed time of executions that returned nothing. It is real time on the server. An execution canceled at thirty seconds spent thirty seconds of CPU, reads and locks exactly as if it had succeeded, held up whatever it was blocking, and then discarded the result.
A timeout, or people giving up?
Query Store keeps the fastest and slowest duration of the canceled executions, which is what lets the page tell the two apart:
- Cancellations clustered on one duration (the slowest within a third of the fastest, at least three of them, and at least three seconds in) are a configured timeout. The page names the setting when it matches a common one, such as the 30 second default command timeout in .NET.
- Cancellations spread over a range are callers giving up at their own pace, which is what a screen that has become slow enough to close looks like from the database.
The two send you to different places: the first is a conversation about a setting, the second is a query to tune.
Where to find it
In the tree, under a database, Real Time → Query Store → Timeouts and Failed Executions.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on. Reaching the page another way, from history or the command palette, runs the same checks and produces a message instead.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The history lives inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Time thrown away / Abandoned runs / Abandon rate / Most recent | What the queries are ranked by. It also decides what the lanes on the chart measure: time thrown away, abandoned executions, or the share of executions abandoned. |
| 4 h / 24 h / 3 d / 7 d | The window. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
At the top, a verdict names the worst thing on the page, in this order:
| Verdict | When |
|---|---|
| Being canceled by a timeout, not by a person | A query’s cancellations cluster on one duration. |
| Has not completed once in this window | Every execution of a query was abandoned. |
| Ending in an error rather than finishing | More of a query’s executions raised an error than were canceled. |
| Being canceled by whoever called it | Cancellations spread over a range of durations. |
| Every execution in this window ran to the end | Nothing was abandoned. |
A query that times out on every call is reported as timing out rather than as never completing, because the clock is the fact that says what to do about it.
Six tiles carry the totals:
| Tile | What it is |
|---|---|
| Abandoned executions | Aborted plus failed executions, and their share of all executions. |
| Time thrown away | Their elapsed time, and its share of all the time the window spent. |
| Canceled by the client | Aborted executions and the time spent before giving up. Click to show only queries with cancellations. |
| Ended in an error | Executions that raised an error. Click to show only queries with errors. |
| Timeout signature | The timeout the worst clustered cancellations sit on. Click to show only those queries. |
| Queries affected | How many queries had anything abandoned, of all that ran. |
Click a filtering tile again to clear the filter.
The horizon chart
Under the tiles, one thin lane per query shares one clock (in UTC):
- The top lane is the whole database.
- The lanes under it are the first twelve queries in the grid, in the same order.
- Each lane is cut into blocks of the clock, never shorter than the Query Store interval, sized so a window is at most about 120 blocks.
- The height of a block is its value. A lane is only about 24 pixels tall, so its scale is folded into three bands: a value past the first band is drawn again from the floor in a darker shade. The darker the block, the taller it is.
- A flat line is a block in which the query ran and nothing was abandoned. A gap is a block in which it did not run at all. Those are different facts.
- Each lane is scaled to its own peak, which the tooltip gives, except in the Abandon rate view, where every lane is drawn on a scale of 0 to 100%.
A dark column across several lanes is a moment when many queries failed at once. A lane that is one dark block against a pale floor is a bad hour; a lane that is evenly shaded is a query that fails all the time.
Red lanes are timing out, failing or never completing; orange lanes are canceled by callers. The note to the right of each lane says which. Hover over a block for its time, its value and its counts; click a lane to select the row in the grid.
Reading the grid
| Column | What it is |
|---|---|
| # | Rank by the toolbar’s ranking. |
| Query | The statement text, with the parameter list Query Store prefixes removed. |
| Finding | Timing out (with the timeout), never completes, failing, or canceled by the caller. |
| Canceled | Aborted executions. |
| Failed | Executions that ended in an error. |
| Abandon rate | Aborted plus failed as a share of all the query’s executions. |
| Time thrown away | Elapsed time of the aborted and failed executions. |
| Of its own time | That time as a share of everything the query spent. |
| Notes | The range the cancellations landed in, whether the query completes much faster when it completes at all, and how many plans were involved. |
| Completed | Regular executions. |
| Canceled at | The average duration of the canceled executions. |
| Completes in | The average duration of the completed executions. |
| CPU thrown away | CPU time of the aborted and failed executions. |
| Last abandoned (UTC) | When an execution was last abandoned. |
| Object | The procedure, function or trigger, when the statement belongs to one. |
The Notes column takes the width the others leave, so it grows with the window. The columns after it (Completed, Canceled at, Completes in, CPU thrown away, Last abandoned and Object) sit past the edge of a 1280 pixel screen; scroll right for them, or hover over a row, whose tooltip lists every column along with the full statement and notes.
Double-click a row to see the statement with its outcomes beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this query’s outcomes | The counts, durations and finding in words. |
| Show the statement | The statement in the query window. |
| Go to Waits by Query | When the query had cancellations: what the slow runs waited on. |
| Go to Duration Spread | When it also completes: whether its slow runs are a spread that is widening. |
| Go to Latency Service Levels | When it also completes: how its completed runs sit against a target. |
| Copy query text | The statement, without the parameter list. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions, average and minimum and maximum duration, and CPU, per plan per interval per execution_type. |
sys.query_store_runtime_stats_interval |
The window and the blocks of the clock. |
sys.query_store_plan, ..._query, ..._query_text |
The query behind each plan. |
sys.database_query_store_options |
The readiness banner and the interval length. |
The window ends at the last completed interval, not at the clock, and is bounded at both ends. Totals are weighted by executions: every duration is the average multiplied by the executions. The totals on the tiles are counted over every query, including the ones that never failed.
What Query Store cannot see
- An execution is recorded once it has a plan. A statement that failed to compile, or never started because it was blocked, is not counted.
- Under the AUTO capture mode, a cheap and infrequent query may not be captured at all.
- An aborted execution says the client stopped waiting. It cannot say whether that was a timeout, a cancel button, or a closed browser tab; the clustering test is what separates the first from the others.
- Query Store records that an execution ended in an error, never which error.
Messages you may see
Query Store recorded no executions in the window. Nothing ran, or nothing that ran was captured. Widen the window before reading anything into it.
Query Store’s history starts after the window begins, so the clock starts there. Query Store was turned on or cleared inside the window.
Capture mode is AUTO, so cheap or infrequent queries are being discarded. Queries that are not captured have no outcomes to count.
Related reports
| Report | Why you would go there |
|---|---|
| Latency Service Levels | How the executions that did complete sit against a response time target. |
| Waits by Query | What a query that times out was waiting on. |
| Duration Spread | Whether the slow runs of a query are a different plan or a different parameter. |
| Slow Periods | Whether the failures belong to particular hours. |
| Active Queries | Whether something is blocking right now. |
| Index Contention | Where deadlocks and blocking concentrate. |
Frequently asked questions
Should I just raise the timeout? If the query completes quickly when it completes, the timeout is catching a tail: find out what makes the tail long first. If it has not completed once, raising the timeout means waiting longer for the same outcome.
Which error did my query raise? Query Store does not record it. A deadlock victim appears in the system_health Extended Events session; anything else is in the application’s log, or in an Extended Events session on error_reported.
The whole database lane is dark but no query lane is. The failures belong to queries below the first twelve in the grid. Change the ranking, or scroll the grid.
Why is my timeout shown as “about 4 s” rather than a named setting? It clusters, but not on one of the common timeouts (30, 60, 90, 120, 180, 300, 600, 900 or 1,800 seconds).