SQL Server Resource Governor: Putting a Leash on the Report Workload

The month-end close at a retail client had a ritual quality to it: on the first business day of the month, the reporting suite would light up all forty cores at 06:00, the order-taking application would degrade from twenty-millisecond reads to multi-second queuing, the support queue would fill, and at about 13:00 the reports would finish and everyone would pretend it was weather. The instance had been upsized twice — more cores, more memory — and the close simply expanded to fill it, because the report queries were unbounded parallelism and unbounded memory grants running against a workload that could not tell them apart from the orders that paid the bills. The fix that finally held was not hardware and not query tuning, though both had been tried; it was Resource Governor. A classifier function routed the reporting login into a workload group capped at twenty-five percent of CPU and thirty percent of grantable memory, the close still finished by 09:30, and order latency on close day became indistinguishable from any other Tuesday.

Resource Governor is one of the oldest and least-deployed answers in the box, mostly because its setup looks scarier than it is and its failure modes are the kind you read about rather than experience. This is the production setup that has survived at three estates, what it genuinely can and cannot throttle, and the classifier mistake that will teach you where the Dedicated Admin Connection is. Applies to Enterprise Edition of SQL Server 2016 through 2022.

What can Resource Governor actually throttle, and what can it not?

It throttles CPU bandwidth, memory grant share, degree of parallelism, and — on 2016 and later — IOPS per volume, all per resource pool; it does not throttle worker threads, locks, tempdb space, or the buffer pool’s total memory in the way people assume, and confusing those lists is how governance projects disappoint. The mental model that works: a resource pool is a slice of the schedulers and of the grantable memory budget, and a workload group of sessions assigned to that pool competes only within its slice. Cap the reporting pool at twenty-five percent CPU and the OLTP sessions in the default pool are guaranteed their seventy-five — the report queries queue inside their own lane instead of across the highway. The memory story has a subtlety that bites: the pool memory cap governs query execution grants, the RESOURCE_SEMAPHORE waits I covered in the memory grants notes, not buffer pool pages, so a reporting workload that floods cache with cold pages still pollutes the buffer for everyone. And the thread story bites hardest of all: a throttled pool whose queries still run with MAXDOP 8 can exhaust worker threads exactly as before, which is why my standard template caps the group’s MAXDOP alongside its CPU — the thread exhaustion failure mode in the worker thread notes does not respect pool boundaries.

What does a production setup look like end to end?

Two pools, two groups, one classifier, and a reconfigure — the whole thing is fifty lines, and every line deserves a comment because the next person to read it will be reading it during an incident:

-- pools: the leash itself
CREATE RESOURCE POOL PoolReporting
WITH (MAX_CPU_PERCENT = 25, CAP_CPU_PERCENT = 40,
      MIN_MEMORY_PERCENT = 0, MAX_MEMORY_PERCENT = 30);

CREATE RESOURCE POOL PoolOLTP
WITH (MIN_CPU_PERCENT = 60, MAX_CPU_PERCENT = 100,
      MAX_MEMORY_PERCENT = 70);

-- groups: who lives in which pool
CREATE WORKLOAD GROUP GroupReporting
WITH (MAX_DOP = 4, REQUEST_MAX_MEMORY_GRANT_PERCENT = 15)
USING PoolReporting;

CREATE WORKLOAD GROUP GroupOLTP USING PoolOLTP;
GO

-- classifier: the traffic cop — must be fast and must never fail
CREATE FUNCTION dbo.fn_GovernorClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp SYSNAME = N'GroupOLTP';
    IF ORIGINAL_LOGIN() = N'rpt_svc' OR APP_NAME() LIKE N'%ReportSuite%'
        SET @grp = N'GroupReporting';
    RETURN @grp;
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_GovernorClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

The defaults deserve a sentence: sessions that match no rule land in the default group in the default pool, which is exactly where you want anything the classifier has never heard of, and the internal pool — the engine’s own — cannot be governed at all, which is correct and occasionally inconvenient. CAP_CPU_PERCENT matters more than people expect: without it, a capped pool can use idle CPU beyond its MAX, which is usually what you want on a mixed box, but on a box where the reporting pool must never exceed its share — licensing, co-tenant billing — the cap is the enforcement.

What is the classifier bug that locks you out of your own server?

A classifier function runs for every login, before the session exists, and if it throws, blocks, or takes locks, logins fail — all of them, including yours. The canonical disaster is a classifier that looks up a table to decide routing: someone builds a dbo.WorkloadRouting table, the classifier SELECTs from it, and one night the table is locked by a maintenance job or the database is in SINGLE_USER after a restore, and every new connection to the instance hangs or errors. The server is up, healthy, and unreachable. This is the one configuration in SQL Server where the Dedicated Admin Connection stops being trivia: the DAC bypasses the classifier entirely, which is precisely why it exists, and the incident runbook for any governed instance starts with the words connect via DAC, disable the classifier, ALTER RESOURCE GOVERNOR RECONFIGURE. The defenses are cheap and absolute: the classifier must be schema-bound, must touch no tables, must classify only on connection metadata — login name, app name, host, IP — and must be deterministic enough to run in microseconds, because it runs for every login forever. If routing needs data, stage the data into the classification criteria themselves: a login naming convention beats a lookup table every time.

How do I know the leash is actually holding?

From the performance counters and DMVs that Resource Governor exposes per pool, not from application latency alone — the whole point of governance is that the throttled workload is supposed to get slower, so the only honest success metric is that everyone else stopped noticing. sys.dm_resource_governor_resource_pools and its workload-groups sibling report CPU usage, granted memory, and — the telling one — queued request counts per pool, so on close day I watch the reporting pool’s queue depth climb while the OLTP pool’s CPU stays under its ceiling and its grants stay instantaneous. That asymmetry is the success state, and it reads oddly the first time: governance working correctly looks like one workload visibly suffering on a dashboard while the business has a normal morning. The counters also catch the drift cases — a new report login nobody classified, landing in default and quietly un-governed, shows up as default-pool CPU that does not match the known OLTP baseline. I pair that with the parallelism discipline from the MAXDOP and cost threshold notes, because governance caps and server parallelism settings are two halves of the same policy, and estates that set one without the other always end up debugging the seam between them.

Watching governed workloads with MonPG when SQL Server support lands

For MonPG product capabilities and setup information, see the SQL Server monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• SQL Server: Resource Governor