The DBA’s New Nuclear Weapon–ABORT_QUERY_EXECUTION

Yesterday on the r/SQL subreddit there was a post about someone, who’s boss was using ChatGPT to generate queries against an operational database, and in shocking news, the queries sucked and were causing large amounts of resource contention. While this is very much a 2025 problem, developers and their applications executing terrible code, is a common problem that has helped me pay off my house early. The first time I really remember this was when I fixed a vendor’s Oracle application by adding a couple of indexes which dropped CPU by like 80%. I felt like a superhero. The other one I remember was supporting a vCenter database, back when it still ran on SQL Server.

VMware vCenter was absolutely hammering my shared SQL Server FCI with IO, (yes this was in 2010, if you were guessing), and I wanted to limit what it could do. Since we were on SQL Server 2008, I had the newly released resource governor feature. In 2008, I couldn’t use that to directly limit IO requests, but I could cut the CPU requests to the vCenter login enough that it couldn’t get that much IO. This step was admittedly draconian, but in shared environments you do what you need to do.

Resource governor is a powerful feature, and it works quite well, however, it can be tricky to implement. It’s classifier function is limited to things that can be captured at login, like login name or program name, as opposed to being able to scope it to a database. However, in most cases if you have a shared server resource governor is the best way to balance resources between workloads of different criticality.

However, SQL Server 2025 gives us a bigger hammer (DBAs love hammers). Building on top of the query store hints feature that was added in SQL Server 2022, ABORT_QUERY_EXECUTION simply blocks the exection of known problematic queries.
When you specify this hint for a query, an attempt to execute the query fails with error 8778, severity 16, Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.

You can implement this using the following T-SQL block.

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

I wouldn’t go around using this reguarly, but if you have identified some really terrible queries, maybe that are coming from an older application, or a rogue user, this can be a good way to kill it very early in the optimization process.