We are pleased to announce a limited 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 ad hoc queries impacting application workloads.
At this time, the preview is available for a limited number of customers in Azure SQL Database only. To participate, please fill out a short form: https://forms.office.com/r/8sZrLUD2rV.
Frequently Asked Questions
- Is there any risk in participating in this preview?
As with any preview feature, there is a risk of bugs that can affect your workloads.
- Is this new feature supported by Microsoft Support during the limited preview?
No. However, the Microsoft team running the preview will provide support on a best-effort basis. The feature will be fully supported once it is generally available (GA).
- How do I use this?
Use Query Store catalog views or SSMS reports to find the query ID of the query you want to block, and execute sys.sp_query_store_set_hints. For example:
EXEC sys.sp_query_store_set_hints
@query_id = 17,
@query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';
- What happens when a query with this hint is executed?
This hint can be specified directly or as a Query Store hint. In either case, the query fails immediately with a severity 16 error:
Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.
- How do I unblock a query?
Remove the hint by executing sys.sp_query_store_clear_hints with the same value for the @query_id parameter.
- Can I block a query that is not yet recorded in Query Store?
No. At least one complete 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.
- 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.
- 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.
- 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 a hint. However, you can create automation that will periodically find all new query IDs matching a given query hash and block them.
Updated Dec 07, 2024
Version 2.0Dimitri_Furman
Microsoft
Joined November 27, 2018
Azure SQL Blog
Follow this blog board to get notified when there's new activity