How to Prevent SQL Server Query Stampedes: Protecting OLTP from Reporting and AI Agent Queries
Prevent SQL Server query stampedes by classifying Resource Governor on SUSER_SNAME() over dedicated reporting and AI agent logins, then capping their parallelism, concurrency, memory and tempdb. Layer a readable secondary, a pre execution gateway, and a runtime watchdog above it, because Resource Governor alone cannot stop long running statements or novel AI generated SQL.
By Andrew Baker · Group CIO, Capitec Bank
SQL Server 2025 quietly closed several of the holes that made Resource Governor unusable as a containment layer, and the one objection everybody repeats, that its classification is spoofable, turns out to be an artefact of classifying on APP_NAME() rather than on the authenticated login. Classify on SUSER_SNAME() over dedicated logins, give the workload its own resource pool, and Resource Governor becomes a credible native containment layer. The trap is that six of its ten controls never stop anything. They make the offending query take longer, which stretches out every lock, grant, snapshot and worker it is holding, so a throttled stampede reappears as backpressure somewhere the throttle does not reach. Pair the throttling controls with at least one that terminates, and keep the elapsed time watchdog, the readable secondary and the pre execution gateway around it, because Resource Governor still offers no wall clock timeout, no per query visibility into what it did, and no defence against SQL nobody has seen before.
The production incident has the same shape on every platform. Somebody, whether a human analyst working in SSMS, a BI tool refreshing a dataset, or increasingly an AI agent writing its own SQL, runs a query with no WHERE clause, an accidental cartesian product, or a scan whose cardinality the optimiser has badly misjudged. SQL Server does not look at that query and conclude that it seems expensive and therefore ought to wait its turn behind the payments workload, so it hands the query the same schedulers, the same buffer pool, and the same I/O path as your transactional traffic. If you are running on Azure SQL Database serverless, or on a managed instance with a generous vCore ceiling, the platform does precisely what it was told to do and scales, so your bill and your latency deteriorate together.
Since SQL Server will not rank queries by priority on its own initiative, the right mental model is not to prioritise but to contain, which means isolating where a query is allowed to run, bounding how much it can consume once it is running, and rejecting it outright before it starts if it looks dangerous enough. What has changed, and the reason this post takes a different line from the conventional advice, is that the engine will now do a substantial part of the bounding for you if you configure it to.
The objection to Resource Governor, and why it dissolves
The conventional wisdom holds that Resource Governor cannot be the backbone of this because it is Enterprise only, because its classifier routes on client supplied metadata that anybody can forge, and because it is blind to tempdb where the worst damage happens. Every one of those objections was true, and two of them stopped being true in SQL Server 2025.
Resource Governor now ships in Standard edition. Microsoft’s own wording is that it is available in the Enterprise, Enterprise Developer, Standard and Standard Developer editions, with the same functionality as in Enterprise, which removes the licensing objection for most estates. It also gained a genuine tempdb ceiling, in the form of GROUP_MAX_TEMPDB_DATA_MB and GROUP_MAX_TEMPDB_DATA_PERCENT on the workload group, aborting an offending request with error 1138 when the group’s cumulative tempdb data consumption would cross the line.
The spoofing objection is the interesting one, because it was never really an objection to Resource Governor at all. It is an objection to a particular way of writing the classifier. APP_NAME() and HOST_NAME() are supplied by the client connection string, and Microsoft’s documentation says so plainly, noting that an application or user can provide any application name as part of the connection string. But the classifier is an ordinary scalar function, and one of Microsoft’s own documented examples classifies on SUSER_SNAME(), the authenticated login. An AI agent cannot announce that it is the payments application and escape its workload group; it would need a different set of SQL Server credentials, which is precisely the boundary section 1 establishes anyway. The architecture that everybody already recommends as step one turns out to solve the objection that everybody raises as the reason to skip step two.
It is worth noticing where the folklore comes from, because it is not invented. Microsoft’s how to page on building a classifier carries three worked examples and every one of them routes on APP_NAME(), with no warning attached. Follow the tutorial and you get the spoofable design; the login based example lives on a different page. That is a documentation accident rather than a design limitation, but it has shaped a decade of received wisdom about the feature.
What remains true is that Resource Governor has no elapsed time control. REQUEST_MAX_CPU_TIME_SEC measures processor time rather than wall clock, so a query that spends twenty minutes waiting on I/O never trips it; it still raises an event rather than cancelling anything unless you are on SQL Server 2016 SP2 or 2017 CU3 or later with trace flag 2422 enabled, at which point it aborts with error 10961; and even then the detection sweep runs every five seconds, so a query that exceeds the threshold by less than that interval may not be noticed at all. None of that changed in 2025. Neither did the fact that no resource limit of any kind can tell the difference between a legitimate expensive query and a catastrophic one before it runs.
So the honest position is layered rather than dismissive:
AI / HUMAN / BI
│
SQL GATEWAY ← rejects unknown-bad SQL [STOPS]
│
dedicated login ← the identity all of it keys on
│
RESOURCE GOVERNOR ← classified by login, own pool
┌─────────────┬───┴────┬─────────────┐
MAX_DOP concurrency memory tempdb
[slows] [slows] [stops] [stops, 2025]
└─────────────┴───┬────┴─────────────┘
│
READABLE SECONDARY ← separate compute and storage
│
elapsed-time watchdog ← wall clock, and the only [STOPS]
layer that logs what it hitwith a separate, faster path for problems you have already met:
Known bad query, already seen once?
↓
ABORT_QUERY_EXECUTION via Query Store
↓
never runs againEverything below builds that model from the bottom up.
1. Separate the workload onto a dedicated login
The single change that helps most is to stop letting reporting, admin, BI, and AI generated SQL arrive under the same login, or worse through the same connection pool, as your application. This was always the right first move for permissions reasons; what makes it more valuable than it used to be is that the login is also the key everything else in this post keys on, from Resource Governor classification to the watchdog filters to the audit script at the end.
CREATE LOGIN adhoc_user WITH PASSWORD = N'...';
GO
USE ReportingDb;
GO
CREATE USER adhoc_user FOR LOGIN adhoc_user;
ALTER ROLE db_datareader ADD MEMBER adhoc_user;
-- Deny beats grant in SQL Server, so this holds even if the account
-- later picks up a role membership that would otherwise allow writes.
DENY INSERT, UPDATE, DELETE, ALTER, EXECUTE TO adhoc_user;
GOThe habit worth unlearning here is the PostgreSQL one, because PostgreSQL lets you write ALTER ROLE adhoc_user SET statement_timeout = '30s' and have that default follow the role into every session it opens, and SQL Server has no equivalent. There is no ALTER LOGIN ... SET, and session SET options issued inside a logon trigger do not survive into the session that triggered them. The settings therefore have to live somewhere other than the principal, and on SQL Server 2025 the best answer to “where” is a Resource Governor workload group, which is the closest thing the product has ever had to a per principal settings store.
2. Classify by authenticated identity, and let Resource Governor do the bounding
Resource Governor has three moving parts: a resource pool that reserves or caps physical resources, a workload group that sets per request and per group limits inside that pool, and a classifier function that runs once at login and decides which group the session belongs to for its lifetime. The classifier is where the whole design succeeds or fails, so write it against the authenticated login and nothing else:
USE master;
GO
CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @group sysname = N'default';
IF SUSER_SNAME() IN (N'adhoc_user', N'agent_user', N'bi_user')
SET @group = N'UntrustedQueries';
RETURN @group;
END;
GOKeep that function trivial, and resist the temptation to make it clever. It runs for every new session even when connection pooling is enabled, so anything slow in it becomes login latency for the whole instance, and Microsoft warns that connection attempts might time out if the function does not complete inside the client’s connection timeout. My own advice, rather than Microsoft’s, is to avoid attaching EXECUTE AS to the classifier. The archived guidance that documents the technique notes that Resource Governor runs the function in the context of the login user by default or as a designated user where EXECUTE AS is specified, and it presents context switching as the solution to a cross schema permission problem. That may well be safe, but it introduces an ambiguity about what SUSER_SNAME() returns underneath the one function whose entire job is to identify the login, and that is not an ambiguity worth accepting for convenience. If the classifier needs to see something the login cannot, prefer a design that does not require the switch, and test the behaviour on your own instance before relying on it either way.
The group is where the per request limits live, but the group alone does not isolate anything, and this is the mistake most worth avoiding. A workload group created USING [default] joins the default resource pool, which is where every unclassified session on the instance also lands, so it shares that pool’s CPU and query workspace memory with everything else. Worse, REQUEST_MAX_MEMORY_GRANT_PERCENT is a percentage of the pool, so a group in the default pool has capped one query’s slice of a budget that your OLTP traffic is drawing from at the same time. Create a pool of its own:
CREATE RESOURCE POOL UntrustedPool
WITH (
MIN_CPU_PERCENT = 0,
CAP_CPU_PERCENT = 25, -- hard ceiling, unlike MAX_CPU_PERCENT
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 15 -- query workspace memory only
);
GO
CREATE WORKLOAD GROUP UntrustedQueries
WITH (
IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
REQUEST_MAX_CPU_TIME_SEC = 60,
MAX_DOP = 2,
GROUP_MAX_REQUESTS = 4,
GROUP_MAX_TEMPDB_DATA_MB = 8192 -- SQL Server 2025 and later
)
USING UntrustedPool;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GOThe two memory settings on the pool do different jobs, and using only one of them is a common half measure: MAX_MEMORY_PERCENT caps what this pool can take, while MIN_MEMORY_PERCENT reserves memory that other pools cannot touch even when this one is idle. For containing a workload you want the cap; for protecting the workload you are containing from, you want a minimum on the OLTP pool rather than on this one. Memory grant queuing is per pool, since each pool gets its own resource semaphores and behaves, in Microsoft’s phrase, like a small independent server instance, which is precisely why the dedicated pool is what makes RESOURCE_SEMAPHORE waits in the reporting workload stop being your payments workload’s problem.
Note also the boundary of what pool memory governance covers, because it is narrower than people assume: only query workspace memory is governed. Buffer pool memory, the data and index pages, is always shared across all pools and is never reserved or limited by Resource Governor. A runaway scan in a tightly capped pool still evicts your OLTP working set from cache. Resource Governor cannot fix that, and no amount of tuning the pool will make it.
Three mechanical details catch people out. default is a reserved word, so where you do use it in a USING clause it has to be bracketed or double quoted rather than left bare. MAX_IOPS_PER_VOLUME defaults to 0, which means no I/O governance at all rather than none allowed, and a pool left at that default can consume all the IOPS on the instance even where other pools have minimums reserved. And nothing you have written takes effect until ALTER RESOURCE GOVERNOR RECONFIGURE runs, which is a surprisingly common reason for somebody to conclude the feature does not work. If you take one diagnostic habit from this section, make it checking is_reconfiguration_pending in sys.dm_resource_governor_configuration before believing any limit you have set is in force. GROUP_MAX_TEMPDB_DATA_MB is SQL Server 2025 and later; on earlier versions drop that line and use the file sizing and watchdog approach in section 6 instead.
The reassuring detail, for anybody who has been reluctant to deploy a classifier because a broken one sounds like a self inflicted outage, is that the dedicated administrator connection is not subject to Resource Governor classification at all. DAC queries always run in the internal workload group and resource pool, so the connection you would use to drop a misbehaving classifier is the one connection the classifier cannot touch. Enable remote admin connections in advance, because by default the DAC only accepts connections from a client running on the server itself, and discovering that during an incident is a poor use of the twenty minutes you will wish you had back.
Two limits of this deserve stating rather than discovering. Resource Governor is a per instance configuration and it does not propagate from a primary availability group replica to its secondaries, which matters a great deal given that section 8 sends all of this traffic to a secondary: you have to configure the pool, the group and the classifier on every instance that hosts the availability group, and a replica where you forgot is a replica with no containment at all. And REQUEST_MAX_CPU_TIME_SEC remains the weakest control in the group for the reasons already covered, so treat it as a backstop against processor bound runaways rather than as the timeout it superficially resembles.
3. Know which of these controls stop work and which only slow it down
Everything in section 2 is worth doing, and taken as a set it is easy to leave believing the workload is now contained. It is mostly not. Six of the ten Resource Governor controls never stop anything at all; they make the offending work take longer, which is a materially different outcome and occasionally a worse one.
| Control | On exceeding the limit | Terminates? |
|---|---|---|
MAX_CPU_PERCENT | scheduling delay, only under contention | no |
CAP_CPU_PERCENT | scheduling delay, always | no |
MIN/MAX_IOPS_PER_VOLUME | I/Os queued and delayed | no |
MAX_MEMORY_PERCENT | smaller pool, queuing on RESOURCE_SEMAPHORE | not directly |
MAX_DOP | plan shaped to fewer workers | no |
GROUP_MAX_REQUESTS | request queued, session still created | no |
REQUEST_MEMORY_GRANT_TIMEOUT_SEC | usually the minimum grant instead | rarely, 8645 |
REQUEST_MAX_MEMORY_GRANT_PERCENT | DOP reduced, then fails | yes, 8657 |
REQUEST_MAX_CPU_TIME_SEC | event only, unless TF 2422 | conditionally, 10961 |
GROUP_MAX_TEMPDB_DATA_MB (2025) | request aborted | yes, 1138 |
Microsoft is explicit about the grey rows in the middle. On the memory grant timeout: a query does not always fail when the timeout is reached, and only fails if too many concurrent queries are running, otherwise it might only get the minimum grant, resulting in reduced performance. On the per request grant cap the behaviour is a genuine two stage control, since the server first reduces the degree of parallelism until the requirement fits, and only fails with error 8657 once DOP has bottomed out at 1 and the query still wants too much. That grant cap is therefore one of the few controls that reliably ends something, and it is worth knowing that this only holds for user defined workload groups; the default and internal groups are permitted to obtain the memory they asked for regardless.
Why a throttled bad query can be worse than an unthrottled one
This matters more than a taxonomy usually would, because section 15 closes on the principle that a slow bad query is frequently worse than a fast one, and throttling is precisely the machine for producing slow bad queries. Every control in the top half of that table trades peak intensity for duration. That trade is often correct. It is not free, and the cost lands on resources that no Resource Governor setting governs.
A capped query holds its locks for longer. It holds its memory grant for longer, and because grants queue per pool, everything behind it in that pool waits proportionally longer too. On a readable secondary it holds its snapshot for longer, which means it blocks ghost record cleanup on the primary for longer, in the mechanism section 8 describes. If it opened a transaction it pins the log and the version store for longer, and the version store is explicitly excluded from what the 2025 tempdb ceiling counts. It occupies a worker and a connection throughout. Under GROUP_MAX_REQUESTS the requests behind it do not disappear, they accumulate as sessions that have been created and are waiting, each one holding a connection and, when it eventually runs, a worker.
So the failure mode people actually hit is not that the cap failed to engage. It is that the cap engaged, the workload stretched out to fit underneath it, and the backpressure showed up somewhere the cap does not reach:
CAP_CPU_PERCENT = 25 on the reporting pool
│
▼
reporting queries take 4x longer
│
├── memory grants held 4x longer ──► pool semaphore queue grows
├── snapshots held 4x longer ─────► ghost cleanup blocked on primary
├── workers held 4x longer ───────► THREADPOOL pressure
└── GROUP_MAX_REQUESTS queue ─────► sessions accumulate, none refused
│
▼
instance-wide symptoms with no single
expensive query visible in dm_exec_requestsThe practical rule that falls out of this is worth stating plainly, because it is the thing I would want to have known first. Throttling is only safe when something else is guaranteed to end the work. Use the shaping and throttling controls to bound how much damage a query can do per second, and pair them with at least one control that terminates: the tempdb ceiling on 2025, the per request grant cap, REQUEST_MAX_CPU_TIME_SEC with trace flag 2422 actually enabled, and the elapsed time watchdog in section 5 which is the only one of the four that measures wall clock. A workload group with CAP_CPU_PERCENT and GROUP_MAX_REQUESTS and nothing that can abort is not a containment configuration; it is a slow motion setting for the same incident.
Seeing whether any of it fired
The second half of the problem is that Resource Governor is remarkably bad at telling you a limit was hit. There is no per query attribution anywhere: sys.dm_exec_requests and sys.dm_exec_sessions carry a group_id, which tells you membership rather than violation, and no DMV records that a given request was throttled, capped, or delayed. What you get instead is a set of monotonic counters per group and per pool, sharing a single epoch:
SELECT wg.name,
wg.statistics_start_time, -- the shared epoch for all of it
wg.total_queued_request_count, -- GROUP_MAX_REQUESTS bit
wg.total_reduced_memgrant_count, -- grant cap bit
wg.total_cpu_limit_violation_count, -- REQUEST_MAX_CPU_TIME_SEC bit
wg.total_suboptimal_plan_generation_count,
wg.max_request_cpu_time_ms,
wg.queued_request_count, -- live gauge
rp.total_cpu_delayed_ms, -- CPU throttling, cumulative
rp.total_cpu_violation_sec,
rp.total_memgrant_timeout_count, -- the 8645s
rp.memgrant_waiter_count, -- live semaphore queue depth
rp.out_of_memory_count
FROM sys.dm_resource_governor_workload_groups AS wg
JOIN sys.dm_resource_governor_resource_pools AS rp ON rp.pool_id = wg.pool_id
WHERE wg.name NOT IN (N'internal');On SQL Server 2025 add wg.tempdb_data_space_kb, wg.peak_tempdb_data_space_kb and wg.total_tempdb_data_limit_violation_count, the last being the count of requests aborted with error 1138.
Read those counters knowing four things about them. They are cumulative since statistics_start_time with no timestamps in between, so a count of 400 tells you nothing about whether that happened steadily over a month or entirely during last Tuesday’s incident. Resetting them is ALTER RESOURCE GOVERNOR RESET STATISTICS, which is instance wide and all or nothing, so any monitoring tool that resets them destroys every other consumer’s baseline; collect deltas on a schedule rather than resetting. Only two Extended Events exist for any of this, cpu_threshold_exceeded and, on 2025, tempdb_data_workload_group_limit_reached, and Microsoft documents no event at all for CPU throttling or for GROUP_MAX_REQUESTS queuing. And there is no wait type to look for: RESOURCE_GOVERNOR_IDLE is marked informational and unsupported, CPU throttling surfaces only as ordinary scheduler yielding that is indistinguishable from normal CPU pressure, and a request queued by GROUP_MAX_REQUESTS has not started executing at all, so conventional wait statistics analysis simply does not see it.
RESOURCE_SEMAPHORE deserves its own warning, because it is the wait you will actually see and it is ambiguous in the worst possible way: it looks identical whether the instance is genuinely short of memory or your own pool’s MAX_MEMORY_PERCENT is doing exactly what you configured it to do. Distinguishing the two means joining sys.dm_exec_query_memory_grants on pool_id, where a requested_memory_kb far below ideal_memory_kb is the inference that the grant cap clipped this query. It is an inference; there is no flag.
There is one more trap worth knowing if you build alerting on this. sys.dm_exec_query_resource_semaphores also carries a timeout_error_count, and that one counts since server startup rather than since the statistics reset, so two counters for the same phenomenon sit on two different epochs. Pick one and be consistent.
The honest summary is that Resource Governor will tell you that a limit was hit some number of times since some timestamp. It will not tell you when, to which query, or at what cost. If you want that, the elapsed time watchdog is also your instrumentation, because the one thing it does reliably is write down exactly which session it killed and why.
4. Bound parallel fan out rather than abolishing parallelism
Parallelism deserves attention because a query the optimiser decides to parallelise recruits a worker per scheduler on each branch of the plan, and turns one careless statement into a small CPU flash mob. The memory arithmetic is worth getting right rather than assuming, because the grant does not simply scale with the degree of parallelism and it does not stay flat either. Microsoft’s formula splits the requirement in two: the workspace each worker needs for its own sort or hash is multiplied by the number of workers, while the memory needed to hold the rows themselves does not change with DOP, since the row count is the same however many threads process it. A parallel plan therefore asks for meaningfully more memory than the identical serial plan, with the increase falling on the per worker workspace, and it occupies several schedulers while it does so. That is the amplification worth bounding: one careless statement expanding across CPU and memory at once.
Before touching anything workload specific, look at cost threshold for parallelism, which still defaults to 5 and has done since a time when 5 units of estimated cost described a genuinely large query. On modern hardware that default means SQL Server considers a parallel plan for statements a single core would have finished in milliseconds, so the instance spends its time coordinating workers for queries that never needed them:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;For the analytical workload the instinct is to set MAXDOP 1 and be done with it, and that instinct is worth resisting, because it sits awkwardly against the principle this post closes on: a slow bad query is frequently worse than a fast one, since it holds locks, workers, connections and version store for longer while it fails. Forcing every analytical query onto one core does bound fan out, and it also converts every legitimate reporting query into a serial marathon that occupies its connection for far longer. The goal is to bound amplification, not to abolish parallelism, and a workload group gives you the tools to do that in combination:
GROUP_MAX_REQUESTS = 4 -- at most four of these run at once
MAX_DOP = 2 -- each using at most two schedulers
CAP_CPU_PERCENT = 25 -- on the pool: a hard ceiling, not a soft oneFour requests at DOP 2 is a bounded eight schedulers of exposure, which is a number you can reason about against your core count, and it leaves legitimate work able to finish. Reserve MAX_DOP = 1 for genuinely hostile traffic, or for a replica small enough that two schedulers is most of it. Note the distinction between the two CPU controls on the pool while you are there, since MAX_CPU_PERCENT is soft and only bites under contention, while CAP_CPU_PERCENT is the hard ceiling, subject to occasional short spikes that Microsoft acknowledges in the documentation.
Where Resource Governor is unavailable, the same bound is available per database through ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2, per statement through OPTION (MAXDOP 2), and for queries you do not control through a Query Store hint attached to the query itself:
EXEC sys.sp_query_store_set_hints
@query_id = 42,
@query_hints = N'OPTION(MAXDOP 1)';The precedence is worth stating plainly: the query hint overrides the database scoped configuration, which overrides the server level max degree of parallelism, while a Resource Governor workload group MAX_DOP acts as a hard ceiling over the query hint rather than as a straight override, so a hint asking for more than the group allows is clamped while a hint asking for less is honoured.
5. Build the elapsed time fuse yourself, because nothing in the engine provides one
This is the one place where the 2025 improvements change nothing, and it remains the sharpest difference from PostgreSQL. statement_timeout is a server side fuse that begins counting when the command arrives and cancels the statement when it expires, regardless of what the client does or whether the client is still there. SQL Server has no elapsed time equivalent anywhere: not in Resource Governor, whose CPU control measures processor time and needs a trace flag to abort at all, and not in any server configuration. What it has is a client side command timeout, which means the authoritative control over how long a query may run lives outside the database, in configuration owned by whoever wrote the calling application.
That has a consequence which shows up in real incidents constantly. SqlCommand.CommandTimeout in ADO.NET defaults to 30 seconds, which is a reasonable starting point, but SQL Server Management Studio ships with an execution timeout of 0, meaning an infinite wait, and many BI and ETL tools either do the same or have had their timeout raised to zero by somebody tired of a report failing. The analyst connecting interactively, who is the single most likely source of an accidental cross join, is usually the one connection in the estate with no time limit on it whatsoever. This is worth reading out of the tools rather than assuming, since the SSMS default has moved between releases and a saved connection keeps whatever it was created with.
| Workload | Command timeout |
|---|---|
| Application OLTP | 2 to 5 seconds |
| Interactive and admin | 15 to 30 seconds |
| Reporting | 1 to 5 minutes |
| Batch and ETL | explicitly controlled, no blanket timeout |
Lock waits are the one part of this the server does bound, through a session setting the client issues on connection, and it is worth setting because a query blocked behind a lock consumes a worker and a connection while achieving nothing:
SET LOCK_TIMEOUT 2000; -- milliseconds; abandon a blocked statement after 2 secondsBecause a client side timeout is advisory in the sense that anybody can raise it, the server side backstop has to be a watchdog you run yourself, scoped to the logins you actually want to police so that a long maintenance task is never caught in the net:
-- Watchdog: cancel ad hoc requests running longer than two minutes.
-- Schedule every 30 seconds via SQL Agent. STRING_AGG needs 2017+;
-- on 2016 use a cursor over the same result set.
DECLARE @kill nvarchar(max),
@log nvarchar(max);
SELECT @kill = STRING_AGG(CONVERT(nvarchar(max),
N'KILL ' + CAST(r.session_id AS nvarchar(10)) + N';'), N' '),
@log = STRING_AGG(CONVERT(nvarchar(max),
CONCAT(N'spid ', r.session_id,
N' login ', s.original_login_name,
N' elapsed_ms ', r.total_elapsed_time)), N'; ')
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
AND s.original_login_name IN (N'adhoc_user', N'agent_user', N'bi_user')
AND r.total_elapsed_time > 120000 -- two minutes, in milliseconds
AND r.session_id <> @@SPID;
IF @kill IS NOT NULL
BEGIN
SET @log = LEFT(@log, 1900);
RAISERROR(N'Query watchdog cancelling: %s', 10, 1, @log) WITH LOG;
EXEC sys.sp_executesql @kill;
ENDUse STRING_AGG rather than the older habit of accumulating a string with SELECT @kill = @kill + ... across rows, because Microsoft documents that pattern as producing undefined results, with the order and frequency of the assignments explicitly nondeterminate. A watchdog that quietly kills one of the five runaway sessions it found is worse than no watchdog, because the log line makes it look as though it worked.
A polling watchdog is cruder than a real timeout, since a query can do up to a full interval of damage before it dies, and this is the layer to replace first if Microsoft ever ships an elapsed time control. Until then it is the difference between a runaway query that ends and one that does not.
6. Cap tempdb, natively on 2025 and by construction everywhere else
PostgreSQL gives every role a temp_file_limit, so a query whose sort or hash join spills past the ceiling is killed and the incident ends there. On SQL Server 2025 the workload group finally offers the equivalent, and GROUP_MAX_TEMPDB_DATA_MB from section 2 is the single most valuable line in that CREATE WORKLOAD GROUP statement. Read its semantics carefully, though, because two details determine whether it covers your failure mode. The limit applies to the total space consumed in tempdb by all sessions in the workload group rather than to any one request, so it is a group budget that whichever request happens to cross the line pays for. And it governs tempdb data space only: temporary tables, table variables, table valued parameters, nontemporary tables in tempdb, cursors, spools, spills, worktables and workfiles are counted, while the transaction log and the version store, including the persistent version store under accelerated database recovery, are not. The version store growth described in section 13 is therefore still yours to monitor.
Where the native ceiling is unavailable, the first line of defence is structural and costs nothing. Size tempdb deliberately, put it on its own volume, and cap or disable autogrowth so a runaway spill hits a boundary rather than the end of the disk. Without that:
accidental query → 800 GB spill into tempdb → volume fills, IOPS saturate → cluster wide slowdownWith it:
accidental query → hits the tempdb file ceiling → query dies → everything else keeps runningError 1105 reports whichever database has a full filegroup, so the message names tempdb, though depending on the allocation path a full tempdb can also surface as 1101, and the Resource Governor ceiling reports 1138 with the offending workload group named in the message.
The second line is a watchdog, and this is where it is easy to build something that looks right and catches nothing. The obvious view to reach for is sys.dm_db_session_space_usage, but its counters update only when a task ends and do not reflect running tasks, so a watchdog built on it alone is blind to the runaway query for exactly as long as the query is running. The view that sees work in flight is sys.dm_db_task_space_usage, whose counters start at zero per request and aggregate to the session on completion. Read both:
-- tempdb watchdog: cancel ad hoc sessions holding more than 8 GB in tempdb.
-- Both DMVs apply only to tempdb. The task view sees a query while it is
-- still spilling; the session view covers tasks that have already finished
-- within a session that is still open.
DECLARE @kill nvarchar(max);
WITH usage_pages AS (
SELECT ts.session_id,
ts.user_objects_alloc_page_count - ts.user_objects_dealloc_page_count
+ ts.internal_objects_alloc_page_count - ts.internal_objects_dealloc_page_count AS pages
FROM sys.dm_db_task_space_usage AS ts
UNION ALL
SELECT ss.session_id,
ss.user_objects_alloc_page_count - ss.user_objects_dealloc_page_count
+ ss.internal_objects_alloc_page_count - ss.internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS ss
),
totals AS (
SELECT session_id, SUM(pages) AS pages
FROM usage_pages
GROUP BY session_id
)
SELECT @kill = STRING_AGG(CONVERT(nvarchar(max),
N'KILL ' + CAST(t.session_id AS nvarchar(10)) + N';'), N' ')
FROM totals AS t
JOIN sys.dm_exec_sessions AS s ON s.session_id = t.session_id
WHERE s.is_user_process = 1
AND s.original_login_name IN (N'adhoc_user', N'agent_user', N'bi_user')
AND t.session_id <> @@SPID
AND t.pages * 8.0 / 1048576 > 8.0; -- 8 KB pages → GB
IF @kill IS NOT NULL EXEC sys.sp_executesql @kill;7. Use memory grants for memory control, not as your brake
It is tempting to shrink the memory grant for ad hoc users and call it a throttle, and it is worth resisting for the same reason PostgreSQL administrators are told not to shrink work_mem. SQL Server sizes a grant at compile time from estimated row counts and row sizes, and when a sort or hash operator needs more than its grant it does not fail, it spills into tempdb and carries on. Cutting the grant does not make the query smaller; it converts a CPU and memory bound query into an I/O bound one and moves the contention from a resource the query was mostly consuming alone into tempdb, which everybody shares. The blast radius frequently gets worse.
Where grant control earns its place is in stopping one query monopolising the workspace memory every other concurrent query is queueing for, which shows up as RESOURCE_SEMAPHORE waits. REQUEST_MAX_MEMORY_GRANT_PERCENT on the workload group is the clean way to do this for a whole class of traffic, and the query hint is available where you need it per statement:
SELECT ...
FROM large_fact_table
OPTION (MAX_GRANT_PERCENT = 10);Memory grant feedback, in the product since SQL Server 2017 for batch mode and 2019 for row mode, with percentile and persistence modes added in 2022, addresses the same problem adaptively by adjusting grants across executions based on what the query actually used. Have it enabled rather than fighting the problem by hand, and do the real throttling with the tempdb ceiling, the watchdogs and the parallelism bounds, because those limit work rather than relocating it.
8. Route to a readable secondary, which SQL Server 2025 makes materially better
Ad hoc, BI and agent traffic should default to a readable secondary rather than the primary, and this is the single biggest risk reduction available to most estates. An Always On secondary has its own CPU, memory, buffer pool and, unlike an Aurora reader, its own storage, since an availability group ships log records and each replica maintains a complete independent copy. The same holds for a Business Critical read scale out replica in Azure SQL with its local SSD. Hyperscale is the exception worth knowing, since its replicas share the page server layer and keep only a local cache, which is architecturally the Aurora model rather than the Always On one.
Historically this recommendation came with a real cost: pushing analytical SQL onto the replica also pushed your visibility and tuning ability away from where the query ran. Statistics the optimiser created on the secondary were temporary, lived in the secondary’s tempdb, were visible only to the replica that made them, and vanished on restart, so a reporting workload could repeatedly rediscover the same statistics and still plan badly. There was no Query Store on the secondary at all, so the queries you had deliberately exiled were the queries you could see least.
SQL Server 2025 addresses both. Query Store for readable secondary replicas becomes generally available, having been a trace flag gated preview in 2022 that was not supported in production. The mechanism is worth understanding because it is not what most people assume: secondaries stream query execution information such as runtime and wait statistics to the primary, where it is persisted in Query Store and made visible across all replicas, with executions attributed per replica through a replica_group_id column and sys.query_store_replicas. There is no separate store sitting on the secondary. Whether it is on by default is, irritatingly, a question Microsoft’s own documentation answers two ways: the feature page’s availability table says no for SQL Server 2025 and gives you the command to enable it per database, while the “what’s new” page and the persisted statistics page both describe it as on by default. Check sys.database_query_store_options on your own instance rather than trusting either, and if it is off, this is the command, issued on the primary:
ALTER DATABASE [ReportingDb]
FOR SECONDARY
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);Once it is on, Query Store hints become replica aware through the @replica_group_id parameter of sys.sp_query_store_set_hints, which means you can pin a reporting query to MAXDOP 1 on the secondary without imposing that hint on the primary. That is a genuinely new capability for this architecture, because it lets the containment policy differ by replica rather than by database.
The companion improvement is persisted statistics for readable secondary replicas, which promotes those disposable secondary created statistics into permanent statistics persisted through the primary and synchronised to every replica. This one has no toggle of its own: it applies provided auto create statistics is on and the READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATE and READABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE database scoped configurations are left at their defaults, which they are. It is built on the Query Store for secondaries infrastructure, though, so it inherits whatever answer the previous paragraph resolves to on your instance.
A caveat carried over unchanged, and the reason the secondary is not a completely independent failure domain: read operations on a secondary map to snapshot isolation, and Microsoft documents that ghost record cleanup on the primary can be blocked by transactions on one or more secondary replicas, going as far as to say that in the extreme case you will need to kill a long running read query on the secondary that is blocking it. That is the same coupling PostgreSQL administrators know as hot_standby_feedback, reached by a different mechanism. Enabling read access also adds 14 bytes of overhead to modified rows on the primary to store version pointers, which can cause page splits, and although reads take no shared locks they do take schema stability locks, which can block the redo thread applying DDL. A secondary whose redo has stalled behind a reporting query is a secondary quietly falling behind as a failover target.
OLTP application ──────────► PRIMARY
command timeout: 3s
default workload group
read write
Humans / BI / AI SQL ──────► READABLE SECONDARY
command timeout: 30s
UntrustedQueries workload group
MAX_DOP 2, GROUP_MAX_REQUESTS 4
GROUP_MAX_TEMPDB_DATA_MB 8192
its own Query Store + persisted stats
read only login, DENY on writesTwo things make or break that boundary. Resource Governor configuration does not propagate from the primary to secondary replicas on SQL Server, so the pool, group and classifier must be created on every instance hosting the availability group, and a replica you forgot is a replica with no containment. And ApplicationIntent=ReadOnly is a hint supplied by the client, not a permission enforced by the server: it participates in read only routing when the connection goes through the availability group listener and names a database, and if either condition is unmet, or routing was never configured, the connection lands on the primary and does whatever the login’s permissions allow. Nothing about that string prevents a write. Permissions on the login are what make the traffic read only; routing just puts it where you wanted it.
9. Cap concurrency on the server, not only in the client
SQL Server does not need PgBouncer, because it multiplexes connections onto a pool of worker threads rather than forking a process per connection, and every mainstream client library already pools on the application side. The lever people reach for is therefore the connection string:
Server=agl-listener;Database=ReportingDb;ApplicationIntent=ReadOnly;
User Id=adhoc_user;Password=...;
Max Pool Size=4;Connect Timeout=5;That is worth setting, since the default Max Pool Size of 100 means one misbehaving reporting service can occupy a hundred workers while your OLTP application waits. But it is a control the client owns, and a client that wants more can simply ask for more. The server side equivalent is GROUP_MAX_REQUESTS on the workload group, and understanding precisely what it does is what makes it useful rather than misleading. Microsoft’s wording is that when the maximum concurrent requests are reached, a session in that group can be created, but is placed in a wait state until the number of concurrent requests drops below the specified value. So it queues rather than rejects, and it bounds concurrently executing requests rather than connections.
That distinction matters. An agent that opens five hundred connections still opens five hundred connections under GROUP_MAX_REQUESTS = 4; only four of them execute at a time while the rest wait. The group limit protects the server from concurrent expensive work, and the pool limit protects the server from connection and worker exhaustion, and they are genuinely different failure modes: once concurrent requests exceed the worker thread count, new requests wait on THREADPOOL, and because a session cannot log in without a worker, an instance in that state stops accepting the connection you were about to use to diagnose it. Set both. Watch the queue with queued_request_count and total_queued_request_count in sys.dm_resource_governor_workload_groups, which tell you whether your limit is doing useful work or simply throttling a workload that needed more headroom.
Where Resource Governor is unavailable, a logon trigger can cap concurrent sessions for a login, using the pattern Microsoft documents for exactly that purpose. It is worth treating as the fallback rather than the first choice, for two reasons: it caps connections rather than executing requests, which is the less useful of the two bounds, and it requires granting VIEW SERVER STATE to the login so the trigger can count sessions, which means handing a server wide visibility permission to precisely the untrusted principal you are trying to contain.
It also fails in a considerably less forgiving way than a classifier does, and the two are worth keeping straight because they run one after another in the login sequence. A classifier that fails for any reason drops the session into the default workload group, so a broken classifier costs you containment. A logon trigger denies the connection outright: a session is not established if the implicit transaction rolls back or fails, if an error above severity 20 is raised inside the trigger body, or even if the trigger returns a result set. A broken logon trigger therefore costs you the login, for everybody it applies to, which is why the -f startup flag and the DAC belong in your notes before you deploy one rather than after.
10. Reject before execution: unknown bad SQL and known bad SQL are different problems
Everything so far reacts to a query that is already running, or bounds what it can consume while it does. Refusing to run it at all is better, and the two mechanisms available do genuinely different jobs.
Known bad SQL is the easier problem and SQL Server 2025 solves it outright. Once a catastrophic query has appeared even once, ABORT_QUERY_EXECUTION puts its Query Store identity on a no fly list:
EXECUTE sys.sp_query_store_set_hints
@query_id = 39,
@query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';Every subsequent attempt fails immediately with error 8778. This is tailor made for rogue BI reports, recurring ORM disasters, dashboards somebody keeps refreshing, and agents rediscovering the same awful query, and it is far better than the alternatives people reach for, which usually involve revoking access to a table that other things legitimately need. The limitations are all worth knowing: it blocks at execution rather than compile, so a query already running when you block it continues and needs a KILL; the query must have at least one recorded execution in Query Store for a query_id to exist, though that execution need not have succeeded, so a query you cancelled or that timed out can still be blocked; Query Store hints are not supported for statements that qualify for simple parameterisation; and setting the hint needs ALTER on the database. Blocked executions are visible afterwards as execution_type 4, Exception, in sys.query_store_runtime_stats.
Unknown bad SQL is the harder problem, and it is the whole problem for AI generated queries, because the defining property of an agent is that its next query is one nobody has seen. Query Store cannot help by construction. What you have instead is a native cost gate and a gateway you build.
The native gate is the query governor, which has been in the product for decades and which most people have never enabled. It rejects any statement whose estimated cost exceeds a threshold, before the statement runs, returning error 8649:
EXEC sp_configure 'query governor cost limit', 300;
RECONFIGURE;
-- Or per connection, applied by the gateway before it runs anything.
SET QUERY_GOVERNOR_COST_LIMIT 300;Two caveats bound how much weight it carries. The cost number is not what people assume, and Microsoft’s documentation is unusually candid, describing it as an abstract figure referring to the estimated elapsed time in seconds to complete a query on a specific hardware configuration, while stating that it does not equate to the time required on the running instance and should be treated as a relative measure. It is a planner unit calibrated against hardware nobody has used in twenty years, and stale statistics fool it in both directions. More importantly, because SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection and lasts its duration, any session can raise its own limit or set it to zero. That makes the query governor an excellent guard against accidental load from a careless analyst or an overzealous ORM, and not a boundary against a caller whose SQL you do not control.
For that caller, the policy has to be enforced outside the connection, and SET SHOWPLAN_XML ON compiles a statement and returns its plan without executing any of it:
SET SHOWPLAN_XML ON;
GO
SELECT c.customer_id, SUM(t.amount)
FROM transactions t JOIN customers c ON c.customer_id = t.customer_id
GROUP BY c.customer_id;
GO
SET SHOWPLAN_XML OFF;
GOA sturdier gateway looks at several signals in that plan rather than trusting one number, since a single cost threshold is exactly what a stale statistic corrupts: estimated subtree cost at the root, estimated row counts at each operator, scans against large relations, nested loops joins with no join predicate, the number and type of joins, spill and missing index warnings, and a function allowlist. Mind the batch separation, since SET SHOWPLAN_XML ON must be alone in its batch and everything after it in the session returns plans instead of results.
It is also worth separating bounding the output of a query from bounding the work it does. TOP 1000 protects the result set size and does nothing to stop a billion row scan, a large hash aggregate and an expensive sort before those thousand rows come back. Agents are particularly prone to an innocent looking TOP 100 with a full table scan underneath, so row limits belong alongside the cost and plan checks rather than in place of them.
The layers that have become standard practice for agent facing access:
- A read only login on a readable secondary as the physical boundary, so the agent cannot write because its credential does not permit it, enforced by permissions rather than by
ApplicationIntent. - A Resource Governor workload group classified on that login, bounding parallelism, concurrency, memory and tempdb from inside the engine.
- Multi signal plan checks before execution rather than a single cost threshold.
- Row and result size caps alongside those checks rather than instead of them.
- Statement type allowlisting enforced outside the model.
ABORT_QUERY_EXECUTIONapplied to anything catastrophic that gets through once.- Audit logging of every generated statement, so a bad pattern is traceable to a prompt and a session.
The reasoning worth internalising is that enforcement has to happen at the database boundary, because a system prompt instructing a model to issue only SELECT statements is a request rather than a guarantee, and a prompt injection or a structural hallucination produces a write regardless of what the prompt said. The boundary is the only part of the stack that cannot be argued with.
Calibrating the cost threshold rather than guessing
Guessing goes wrong in one of two directions: too low, so legitimate reporting fails with 8649 until somebody disables the governor, or too high, so it never catches anything. Query Store already retains the compiled plan for every query it tracks, so the distribution you need is sitting in the database with none of the overhead that capturing actual runtimes would impose:
WITH plans AS (
SELECT p.plan_id, TRY_CAST(p.query_plan AS xml) AS plan_xml
FROM sys.query_store_plan AS p
WHERE p.query_plan IS NOT NULL
),
costs AS (
SELECT pl.plan_id,
stmt.value('@StatementSubTreeCost', 'float') AS estimated_cost
FROM plans AS pl
CROSS APPLY pl.plan_xml.nodes('//*:StmtSimple') AS s(stmt)
)
SELECT DISTINCT
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY estimated_cost) OVER () AS p95_cost,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY estimated_cost) OVER () AS p99_cost,
MAX(estimated_cost) OVER () AS max_cost
FROM costs;The 95th and 99th percentiles of known good traffic give you a floor, and the maximum tells you how much headroom your worst legitimate query needs. A reasonable starting threshold sits between three and ten times that maximum, comfortably above real reporting and far below the accidental cross join that produces a cost several orders of magnitude larger rather than marginally larger. Run in observation mode first, tighten over a week or two, and revisit as data volumes grow. The same discipline applies to every other number in this post, including the watchdog intervals, the tempdb ceiling and the group limits.
11. Agents cause compilation stampedes as well as execution stampedes
Everything above concerns queries that are expensive to run. There is a second failure mode that agent traffic produces and that none of the controls so far touch, because it happens before execution begins:
Execution stampede → 100 expensive queries start running
Compilation stampede → 100 near-identical dynamic queries start compilingWhen many sessions simultaneously send the same parameterised dynamic SQL, each can independently take the compilation path for a plan that is about to exist anyway, burning CPU on redundant optimisation at precisely the moment the instance is busiest. SQL Server 2025 addresses this with a database scoped configuration that serialises the compilation, making sp_executesql behave the way stored procedures and triggers already do: the first execution compiles and inserts its plan into the cache, and the other sessions stop waiting on the compile lock and reuse that plan once it becomes available rather than each compiling their own:
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;It defaults to OFF, so it is an explicit opt in, and it is available on SQL Server 2025, Azure SQL Database and Fabric SQL database but not on Azure SQL Managed Instance. Do not confuse it with OPTIMIZED_PLAN_FORCING, which is a different SQL Server 2022 feature that reduces compilation overhead for forced plans and defaults to on.
The honest caveat is that this helps when batches are identical apart from their parameters, which describes an ORM or a dashboard far better than it describes a large language model. An agent that produces structurally different SQL on every call generates a different batch each time and benefits very little. It is worth enabling for the parameterised traffic you do have, and it is not a defence against agent generated SQL specifically.
12. If you are on a consumption tier, cap the ceiling deliberately
Autoscaling absorbs the spike and in doing so rewards a misconfigured query with more capacity rather than stopping it. On Azure SQL Database serverless the max vCore setting is the equivalent of Aurora’s MaxCapacity:
az sql db update \
--resource-group prod-rg \
--server prod-sql \
--name ReportingDb \
--edition GeneralPurpose \
--family Gen5 \
--compute-model Serverless \
--min-capacity 0.5 \
--capacity 8 # max vCores: this is the ceilingOn a managed instance or a virtual machine the ceiling is the instance you bought, which makes the equivalent controls max server memory and the provisioned core count, and the useful discipline is to size the reporting replica smaller than the primary rather than matching it, so the environment where careless queries run is also the one least able to absorb them. Either way the ceiling is a financial and resource circuit breaker: instead of silently scaling and presenting a large bill, the workload hits the cap and produces contention, which is a visible and debuggable problem. It does not replace the query level guardrails, since a capped instance running an unthrottled query still degrades, just with a bound on how bad it gets.
13. Watch for the slow burn version of this problem
The hardest version to diagnose has no smoking gun in sys.dm_exec_requests at all. A session that opens a transaction and sits idle holds its locks and pins the log, so log_reuse_wait_desc reports ACTIVE_TRANSACTION and the log grows however diligently you back it up, and under read committed snapshot isolation the same idle transaction pins the version store in tempdb until tempdb becomes the constraint. Note that the Resource Governor tempdb ceiling does not help here, because it excludes the version store from what it counts. Long running queries on a readable secondary hold back ghost cleanup on the primary as section 8 described, producing a primary whose scans get gradually slower for reasons invisible from the primary itself.
SQL Server has no idle_in_transaction_session_timeout, so this is another watchdog, and the sessions are easy to find:
SELECT s.session_id,
s.original_login_name,
s.host_name,
s.open_transaction_count,
DATEDIFF(second, s.last_request_end_time, SYSDATETIME()) AS idle_seconds
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
AND s.status = 'sleeping'
AND s.open_transaction_count > 0
AND DATEDIFF(second, s.last_request_end_time, SYSDATETIME()) > 60
ORDER BY idle_seconds DESC;Alongside that, keep continuous visibility with Query Store for the historical picture and an Extended Events session filtered on duration for the live one, together covering what log_min_duration_statement and pg_stat_statements cover between them. Setting blocked process threshold enables a blocked process report, with the caveat that enabling it is half the job, since the event still has to be captured by an Extended Events session or an alert before it reaches anybody, and the effective minimum is five seconds because that is how often the lock monitor wakes.
14. What SQL Server 2025 improves that is not containment
It is worth being explicit about a category of 2025 improvement that will make your instance better and should not be counted as protection. Optional Parameter Plan Optimization and cardinality estimation feedback for expressions are both genuinely new; degree of parallelism feedback and parameter sensitive plan optimisation arrived in 2022 and were extended in 2025, with PSP gaining DML and tempdb support at compatibility level 170. Optimized locking, which requires accelerated database recovery and delivers its lock after qualification component only when read committed snapshot isolation is on, reduces lock memory and escalation and is off by default on premises. Between them these features will meaningfully reduce how often a legitimate query becomes a disaster because the optimiser guessed wrong.
None of that is stampede protection. SQL Server 2025 is significantly better at correcting bad plans, which is not the same thing as stopping bad queries. Plan feedback can rescue a legitimate query whose optimiser assumptions were wrong; it cannot make a SELECT with no predicate safe, prevent an accidental cartesian product, or stop an AI agent asking the database a perfectly valid but catastrophically expensive question. Optimized locking improves the survivability of the OLTP workload you are protecting, and since your reporting login is supposed to be read only it is not really a query stampede control at all. Enable these things; do not count them.
One caveat on DOP feedback specifically, which matters because section 4 leans on bounded parallelism. Microsoft’s “what’s new” page states it is now on by default in 2025, while the ALTER DATABASE SCOPED CONFIGURATION reference still documents DOP_FEEDBACK as defaulting to OFF. Check sys.database_scoped_configurations on your own instance rather than trusting either page.
15. Putting it together
In priority order if you are retrofitting an existing system:
- Split OLTP and everything else onto separate logins with separate permissions, denying writes explicitly on the ad hoc login (section 1). This is the cheapest change available and every later control keys on it.
- Point everything non transactional at a readable secondary (section 8), which is the biggest single risk reduction if you have availability groups, remembering that
ApplicationIntentis a hint and permissions are the boundary. - Deploy a Resource Governor classifier on
SUSER_SNAME(), a dedicated resource pool rather than[default], and a workload group boundingMAX_DOP,GROUP_MAX_REQUESTS,REQUEST_MAX_MEMORY_GRANT_PERCENTand, on 2025,GROUP_MAX_TEMPDB_DATA_MB(sections 2, 4, 6, 7, 9), on every instance hosting the availability group, since the configuration does not propagate. - Confirm that at least one of those controls actually terminates rather than merely slowing work down, and that you know where you would look to see it fire (section 3). A group full of throttles and nothing that aborts converts a fast incident into a long one.
- Set command timeouts per workload in the connection layer and deploy the elapsed time watchdog, which is the one control that measures wall clock and the only instrumentation that records which query it stopped (section 5).
- Where Resource Governor is unavailable, substitute database scoped
MAXDOP, deliberate tempdb file sizing, the tempdb watchdog and pool sizing (sections 4, 6, 9). - Enable the query governor as a cheap guard against accidental load, and for agent access add a gateway inspecting plans on multiple signals, with a credential that cannot write (section 10).
- Put
ABORT_QUERY_EXECUTIONon anything catastrophic that gets through, so the same query never runs twice (section 10). - If on a consumption tier, cap the vCore ceiling as a circuit breaker rather than a primary control (section 12).
The underlying principle has not changed, and the throttle versus terminate distinction is really just a restatement of it. Do not try to make SQL Server execute a bad query slowly and safely, because it cannot, and a resource limit that only makes it slower is doing exactly that. Make bad queries impossible to run at scale, by isolating the login, bounding fan out, capping tempdb and cancelling on elapsed time, or impossible to run at all, by rejecting them before execution on estimated cost and plan structure, or permanently once you have met them. What is genuinely new is that a good deal of the bounding is now the engine’s job rather than yours, provided you tell it who to bound by asking the one question the client cannot lie about.
16. Score your own setup
The companion script checks whether your actual ad hoc, reporting and agent logins, rather than any principal that happens to exist, have the safeguards described above. Scoring whether any login in the system has a given control produces a misleadingly high result, because a well configured service account you barely use earns full marks while adhoc_user remains unrestricted. The script only scores logins matching a name pattern you configure, and says so explicitly if none match.
It now checks the Resource Governor layer as well: whether Resource Governor is enabled and a classifier is actually in force rather than merely registered, whether that classifier keys on identity rather than routing on APP_NAME(), and whether the resulting workload group bounds parallelism. Because the classifier is an ordinary function whose body the script can read, this is one of the few parts of the architecture a catalog query can genuinely verify.
It also scores the distinction section 3 is about, which is whether any control on that group terminates rather than merely throttling. A group carrying CAP_CPU_PERCENT, MAX_DOP and GROUP_MAX_REQUESTS and nothing else looks thoroughly configured and cannot stop anything, so the script checks for a tempdb ceiling, a deliberately lowered grant percent, or REQUEST_MAX_CPU_TIME_SEC backed by trace flag 2422 actually being on, and reads the trace flag state rather than assuming it. It separately warns when a classified group sits in the default resource pool, since that shares grant memory with every unclassified session on the instance and quietly undermines the per request cap.
Two design decisions are worth knowing before reading its output. Every check fails closed, so a control the script could not evaluate scores zero rather than being awarded a mark nobody verified, and the script says so at the end rather than leaving you to guess whether a low score means “unprotected” or “could not tell”. Built in and service principals are excluded from the target set, because otherwise %agent% matches NT SERVICE\SQLSERVERAGENT, which is a sysadmin by design, and the script reports an alarming score on a healthy instance while blaming the SQL Agent service account.
sqlcmd -S your-server -d master -i sqlserver_protection_score.sqlThe one thing no catalog query can verify is the elapsed time fuse, because SQL Server has no per login settings store and your command timeouts live in connection strings the database never sees. Section 4 remains a check you have to make by hand, which is a reasonable summary of where SQL Server still stands relative to PostgreSQL even after a release that closed most of the other gaps.
The companion script, sqlserver_protection_score.sql, accompanies this post.