ABORT_QUERY_EXECUTION query hint - public preview

We are pleased to announce the public preview of a new query hint, ABORT_QUERY_EXECUTION. The hint is intended to be used as a Query Store hint to let administrators block future execution of known problematic queries, for example non-essential queries causing high resource consumption and affecting application workloads.

The hint is now available in Azure SQL Database for all databases without restrictions. The hint will later be available in Azure SQL Managed Instance with the always-up-to-date update policy, as well as in a future version of SQL Server.

For more information, see Block future execution of problematic queries in documentation.

Frequently Asked Questions

1. Is this supported by Microsoft Support during public preview?

Yes, just like other query hints.

2. How do I use this?

Use Query Store catalog views or the Query Store UI in SSMS to find the query ID of the query you want to block and execute sys.sp_query_store_set_hints specifying that query ID as a parameter. For example:

EXEC sys.sp_query_store_set_hints
     @query_id = 17,
     @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

3. What happens when a query with this hint is executed?

This hint is intended to be used as a Query Store hint but can be specified directly as well. In either case, the query fails immediately with error 8778, severity 16:

Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.

4. How do I unblock a query?

Remove the hint by executing sys.sp_query_store_clear_hints with the query ID value of the query you want to unblock passed via the @query_id parameter.

5. Can I block a query that is not in Query Store?

No. At least one execution of the query must be recorded in Query Store. That query execution does not have to be successful. This means that a query that started executing but was canceled or timed out can be blocked too.

6. When I add the hint, does it abort any currently executing queries?

No. The hint only aborts future query executions. You can use KILL to abort currently executing queries.

7. What permissions are required to use this?

As with all other Query Store hints, the ALTER permission on the database is required to set and clear the hint.

8. Can I block all queries matching a query hash?

Not directly. As with all other Query Store hints, you must use a query ID to set and clear a hint. However, you can create automation that will periodically find all new query IDs matching a given query hash and block them.

9. Can I find all blocked queries in Query Store?

Yes, by executing the following query:

SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
INNER JOIN sys.query_store_query AS q
ON qsh.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS qt
ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%'

10. Where do I send feedback about this hint?

The preferred feedback channel is via https://aka.ms/sqlfeedback. Feedback sent that way is public and can be voted and commented on by other SQL community members. You can also leave comments on this blog post or email us at intelligentqp@microsoft.com.