Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Resource Governor (Enterprise edition) puts incoming sessions into workload groups and limits the CPU, memory and I/O each group can use. The typical use is stopping reporting or ad hoc queries from starving the main application.

Step 1: Pool and workload group

USE master;
CREATE RESOURCE POOL ReportingPool
WITH (MAX_CPU_PERCENT = 30, MAX_MEMORY_PERCENT = 30);

CREATE WORKLOAD GROUP ReportingGroup
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 15, MAX_DOP = 4)
USING ReportingPool;
GO

MAX_CPU_PERCENT only applies when there is CPU contention. Use CAP_CPU_PERCENT for a hard limit.

Step 2: Classifier function

The classifier runs for every new connection and returns the name of the workload group to use.

USE master;
GO
CREATE FUNCTION dbo.fn_RGClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @group sysname = N'default';
    IF APP_NAME() LIKE N'%Report%' OR SUSER_SNAME() = N'DOMAIN\svc_reporting'
        SET @group = N'ReportingGroup';
    RETURN @group;
END;
GO

Keep it short and avoid table lookups. A slow or failing classifier delays or blocks every login. If that happens, connect with the dedicated admin connection (ADMIN:ServerName), which bypasses it.

Step 3: Switch it on

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

Check it is working

-- Which group each user session landed in
SELECT s.session_id, s.login_name, s.program_name, g.name AS workload_group
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;

-- Usage by pool
SELECT name, total_cpu_usage_ms, used_memory_kb, max_memory_kb
FROM sys.dm_resource_governor_resource_pools;

Switching it off

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- or, to stop it completely
ALTER RESOURCE GOVERNOR DISABLE;

Watch out for

• Sessions keep their group until they reconnect, so connection pools may take a while to pick up a change.
• Memory limits apply to query memory grants, not the buffer pool. Resource Governor will not stop a report from pushing other data out of cache.
• I/O can be limited per volume with MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME on the pool.

From my SQL Server DBA handbook.