ABORT_QUERY_EXECUTION: Stopping a Runaway Query With a Query Store Hint

ABORT_QUERY_EXECUTION is a Query Store hint that stops one known bad query before it starts. You use it when the query comes from an app you can’t change. I tested every step on SQL Server 2025 and wrote down what the caller sees.

The Problem With a Runaway Query

A runaway query is one that burns CPU for minutes and slows everyone else. Picture a report screen in a packaged app. It sends the same bad query every time someone opens it, and the vendor won’t change the code.

You can kill the session, but the app starts a new one. You can add an index, if the query allows it. A third option is to tell SQL Server to refuse that one query, and that is what this hint does.

What Query Store Adds

Query Store is a recorder built into each database. It keeps the text of every query, its plans and its run statistics. A Query Store hint attaches an instruction to one query in that record. SQL Server applies it each time the query compiles. With ABORT_QUERY_EXECUTION, that instruction is to refuse the query. The app never knows the hint exists, until the query fails.

Build the Test

The script creates a database with Query Store on. It captures every query (mode ALL), so the short test queries are not skipped. Then it adds 8,000 orders.

IF DB_ID(N'SqlAbortQueryDemo') IS NULL CREATE DATABASE SqlAbortQueryDemo;
GO
ALTER DATABASE SqlAbortQueryDemo SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO
USE SqlAbortQueryDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT s.value % 500 + 1, s.value % 300 + 10.50
FROM GENERATE_SERIES(1, 8000) AS s;
GO

Now the bad query. It compares every order with every other order, and the ABS function stops SQL Server from using a faster join. That makes it a fair stand-in for a runaway.

SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) AS PairCount FROM dbo.Orders AS a JOIN dbo.Orders AS b ON ABS(a.Amount - b.Amount) < 1;

It returned 213,400 and took 5.1 seconds, with 9.4 seconds of CPU across two cores. The query is on one line on purpose. Why that matters comes later.

Find the query_id

Query Store gives every query an id. The lookup below finds ours by its text. It skips itself, because its own text also contains the word PairCount.

SET STATISTICS TIME OFF;
SELECT q.query_id, q.context_settings_id, qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%PairCount%' AND qt.query_sql_text NOT LIKE N'%query_store_query%';

On a fresh database it was query_id 2. Yours can differ, so the next script reads the id into a variable instead of typing a number.

Set the Hint

The procedure sys.sp_query_store_set_hints takes the id and the hint text. Query Store hints are a way to change how one query runs without touching its code. My post on Query Store hints covers the general idea. The doubled quotes below are normal, because the hint is a string inside a string.

DECLARE @qid bigint;
SELECT @qid = q.query_id
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%PairCount%' AND qt.query_sql_text NOT LIKE N'%query_store_query%';
EXEC sys.sp_query_store_set_hints @query_id = @qid, @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

What the Caller Sees

Run the same query again, with the same text.

SELECT COUNT_BIG(*) AS PairCount FROM dbo.Orders AS a JOIN dbo.Orders AS b ON ABS(a.Amount - b.Amount) < 1;

The query failed at once. In the screenshot the error says Line 3, because that script starts with USE and GO. Run alone, the query reports Line 1. It is output, not code to run:

Msg 8778, Level 16, State 1, Line 1
Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.

No rows came back and no partial result. The error number is 8778 and the severity is 16, so an app sees it as an ordinary query error. TRY and CATCH can catch it, and the batch carries on.

BEGIN TRY
    SELECT COUNT_BIG(*) AS PairCount FROM dbo.Orders AS a JOIN dbo.Orders AS b ON ABS(a.Amount - b.Amount) < 1;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

The hint lives in sys.query_store_query_hints, and Query Store logs each blocked run. The second query below shows both kinds of run for our query.

SELECT query_id, query_hint_text, last_query_hint_failure_reason_desc, source_desc FROM sys.query_store_query_hints;

SELECT q.query_id, rs.execution_type_desc, rs.count_executions, rs.avg_duration
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.query_id IN (SELECT query_id FROM sys.query_store_query_hints);

A blocked run costs almost nothing: well under one millisecond, against 5 or 6 seconds for the real one. In the table above it averaged 99.5 microseconds. Query Store also records these runs as Exceptions. That makes it easy to see whether the query is still being sent.

Three Traps

The hint belongs to one query_id, and that id comes from the exact query text. In an earlier test, I ran the same query with its lines indented. Query Store gave it a new id, and it ran for the full time. If the app builds its text a little differently each time, one hint will not cover it.

The id also depends on the SET options of the connection. Run the same text with another setting, and you get a second id. SSMS and most apps differ here, with ARITHABORT as the classic example. Take the query_id from the app’s own run, not from your copy.

SET DATEFIRST 2;
SELECT COUNT_BIG(*) AS PairCount FROM dbo.Orders AS a JOIN dbo.Orders AS b ON ABS(a.Amount - b.Amount) < 1;
SET DATEFIRST 7;
SELECT q.query_id, q.context_settings_id, IIF(h.query_id IS NULL, 'no hint', 'hinted') AS hint_state
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
LEFT JOIN sys.query_store_query_hints AS h ON h.query_id = q.query_id
WHERE qt.query_sql_text LIKE N'%PairCount%' AND qt.query_sql_text NOT LIKE N'%query_store_query%';

The query ran for the full time and returned 213,400, because DATEFIRST 2 made it a different query. The lookup now lists two ids.

The third trap is timing. The hint affects new executions only. I started the query, set the hint two seconds later, and the running query finished with its 213,400 rows. To stop an execution that is already running, use KILL.

Clear the Hint

The matching procedure is sys.sp_query_store_clear_hints. After it runs, the query is allowed again.

DECLARE @qid bigint;
SELECT @qid = query_id FROM sys.query_store_query_hints;
EXEC sys.sp_query_store_clear_hints @query_id = @qid;
SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) AS PairCount FROM dbo.Orders AS a JOIN dbo.Orders AS b ON ABS(a.Amount - b.Amount) < 1;
SET STATISTICS TIME OFF;
SELECT COUNT(*) AS HintsLeft FROM sys.query_store_query_hints;

The query returned 213,400 again, after 6.9 seconds, and HintsLeft was 0.

When This Is the Right Tool

Use ABORT_QUERY_EXECUTION for a known bad query from an app you can’t change. The app must be able to live without that query, and a non-essential report is a good case. It also works as a stopgap while the vendor prepares a fix.

Don’t use ABORT_QUERY_EXECUTION on a query the app needs. The user gets error 8778 instead of a slow screen, and some apps handle that badly. If you own the code or can add an index, fix the query. When the query must run, but run better, use another Query Store hint or forcing a plan.

You could say this is a blunt tool, a KILL that never forgets. Fair point. It is blunt, and it is also the only option when the code is out of your hands. The documented use is through Query Store. I also tried the hint inside the query text, and it worked there too, but that needs a code change.

A Safe Routine

Before you set the hint, tell the app owner that error 8778 is coming. Copy the query_id from the app’s own run, and keep a list of every hinted query. In my reviews, I read sys.query_store_query_hints on every server, so no blocked query is forgotten. Clear the hint when the fix ships.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlAbortQueryDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlAbortQueryDemo;

This hint is not a cure for a slow query, it is a stop sign for one.

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