SQL Server Resource Governor in production: pools, workload groups, a safe classifier and tempdb limits

#The goal

One instance serves an OLTP application, a reporting tool and a handful of analysts. A single wide report takes every core, a huge memory grant and 200 GB of tempdb, and the application's p99 latency goes through the roof. You want the reports to slow down, not the application.

Resource Governor does this by classifying each new session into a workload group, and each group into a resource pool. Pools limit CPU, memory and IO. Groups limit per-request behaviour: memory grants, degree of parallelism, CPU time, concurrency and, from SQL Server 2025, tempdb space.

Resource Governor was Enterprise-only until SQL Server 2025, which makes it available in Standard edition as well.

#Check what is there first

SELECT is_enabled, classifier_function_id = OBJECT_NAME(classifier_function_id, 1)
FROM sys.resource_governor_configuration;

SELECT p.name AS pool, g.name AS grp, g.importance, g.max_dop,
       g.request_max_memory_grant_percent, g.group_max_requests
FROM sys.resource_governor_workload_groups g
JOIN sys.resource_governor_resource_pools p ON p.pool_id = g.pool_id;

-- Who is in which group right now
SELECT s.session_id, s.login_name, s.program_name, g.name AS grp
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id
WHERE s.is_user_process = 1;

Without a classifier every user session lands in the default group of the default pool.

#Design the pools

Keep it small: two or three pools. Every percentage you reserve with MIN_ is taken away from everyone else even when the owner is idle, so reserve little and cap generously.

USE master;
GO
CREATE RESOURCE POOL pool_oltp
WITH (MIN_CPU_PERCENT = 40, MIN_MEMORY_PERCENT = 20);

CREATE RESOURCE POOL pool_reporting
WITH (MAX_CPU_PERCENT = 40,          -- applies only when CPU is contended
      CAP_CPU_PERCENT = 60,          -- hard ceiling even when the box is idle
      MAX_MEMORY_PERCENT = 30,
      MAX_IOPS_PER_VOLUME = 2000);
GO

Tip

MAX_CPU_PERCENT only kicks in when other pools want the CPU, so an idle server still lets reports run fast. Use CAP_CPU_PERCENT only if you need a predictable ceiling, for example a chargeback agreement or licence-bound consolidation.

MAX_MEMORY_PERCENT limits query execution memory (grants, mainly) for the pool, not the buffer pool. That is usually what you want: data pages stay shared, while a reporting grant can't take the memory OLTP needs.

#Design the workload groups

CREATE WORKLOAD GROUP wg_oltp
WITH (IMPORTANCE = HIGH)
USING pool_oltp;

CREATE WORKLOAD GROUP wg_reporting
WITH (IMPORTANCE = LOW,
      MAX_DOP = 4,
      REQUEST_MAX_MEMORY_GRANT_PERCENT = 10,  -- of the pool's memory, per request
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120,
      REQUEST_MAX_CPU_TIME_SEC = 1800,
      GROUP_MAX_REQUESTS = 8)
USING pool_reporting;
GO

IMPORTANCE weights scheduling between groups in the same pool only; it has no effect across pools.

REQUEST_MAX_CPU_TIME_SEC raises the cpu_threshold_exceeded Extended Event by default and does not stop the query. Newer builds can abort requests that pass the limit (trace flag 2422 on supported builds); check the documentation for your version before you rely on it.

#tempdb limits (SQL Server 2025)

ALTER WORKLOAD GROUP wg_reporting WITH (GROUP_MAX_TEMPDB_DATA_MB = 51200);
-- or, if tempdb files have a fixed maximum size:
-- ALTER WORKLOAD GROUP wg_reporting WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 25);

The limit is for the whole group, not each request. A query that would push the group past it fails with an error, and the rest of the instance keeps its tempdb. If both are set, the MB value wins and the percent value is ignored.

#Write a classifier that can't hurt you

The classifier is a scalar function in master that runs for every new connection, including connection-pool resets. If it is slow, logins time out. If it errors, logins go to the default group. Rules:

• WITH SCHEMABINDING, no table access in the hot path, no lookups to other databases.
• Use built-in functions only: SUSER_SNAME(), APP_NAME(), HOST_NAME(), ORIGINAL_DB_NAME(), IS_SRVROLEMEMBER().
• Match on login, not APP_NAME(), for anything that matters. Clients set the application name themselves, so a report can claim to be the application.

USE master;
GO
CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @login sysname = SUSER_SNAME();

    IF @login IN (N'CORP\svc-orders-app', N'orders_app')
        RETURN N'wg_oltp';

    IF @login IN (N'CORP\svc-powerbi', N'CORP\svc-ssrs')
       OR IS_MEMBER(N'CORP\Analysts') = 1
        RETURN N'wg_reporting';

    RETURN N'default';
END;
GO

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

Existing sessions keep their group; only new connections are classified. Recycle application connection pools or wait for them to churn.

Warning

Test the classifier on a non-production instance under a login storm (for example, a connection-pool restart of every app server) before enabling it in production. A classifier that takes 50 ms turns a 2,000-connection reconnect into a login outage.

If a bad classifier is locking people out, connect with the dedicated admin connection (sqlcmd -S admin:SERVERNAME -E), which bypasses classification, and run:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

#Monitor it

-- Pool pressure: CPU, memory grants waiting, IO throttling
SELECT name, total_cpu_usage_ms, used_memory_kb, max_memory_kb,
       out_of_memory_count, active_memgrant_count
FROM sys.dm_resource_governor_resource_pools;

-- Group behaviour: queued requests, CPU violations, grant timeouts
SELECT name, total_request_count, total_queued_request_count,
       active_request_count, queued_request_count,
       total_cpu_limit_violation_count, total_query_optimization_count,
       request_max_memory_grant_percent, max_request_grant_memory_kb
FROM sys.dm_resource_governor_workload_groups;

-- Grants waiting per pool right now
SELECT pool_id, COUNT(*) AS waiting
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL
GROUP BY pool_id;

Also watch the Performance Monitor objects SQLServer:Resource Pool Stats and SQLServer:Workload Group Stats (instance prefix varies for named instances). Statistics reset on restart or with ALTER RESOURCE GOVERNOR RESET STATISTICS.

#Prevent it next time

• Keep the Resource Governor definition (pools, groups, classifier) in source control and deploy it with the same script to every replica of an Availability Group. The configuration lives in master and isn't replicated.
• Add a new login to the classifier as part of onboarding a reporting tool, not after the first incident.
• Review the group DMVs monthly. Limits set for last year's workload are usually wrong.
• Remember that Resource Governor doesn't fix bad queries. A capped report that scans 500 GB still does 500 GB of IO, just more slowly.