SQL Server Resource Governor Basics

2020-10-27 · mssql, performance, configuration

When one reporting user or one badly written batch job can starve the order-entry application of CPU and memory, you need a way to say "this group of connections gets at most this much". Resource Governor is SQL Server's built-in answer. It classifies incoming sessions into workload groups, each group belongs to a resource pool, and the pools carve up CPU, memory and, since SQL Server 2014, I/O. Nothing is rewritten in the application; the classification happens at login based on login name, application name, host name or anything else a T-SQL function can inspect.

I first reached for it after one analyst's Excel refresh kept eating the server at month end. It needs Enterprise Edition (Developer for testing) and CONTROL SERVER. Examples use SQL Server 2017 but apply from 2012 onwards; the I/O settings need 2014 or later. Decide up front how you'll tell workloads apart. Distinct logins per application is the cleanest; APP_NAME() works, but clients can set it to anything.

Resource pools

A pool defines minimum and maximum shares of the instance's resources. The MIN values across all pools cannot exceed 100 percent; MAX is a soft cap enforced only under contention, while CAP_CPU_PERCENT is a hard ceiling.

CREATE RESOURCE POOL pool_reporting
WITH (
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 30,
    CAP_CPU_PERCENT = 40,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 30,
    MIN_IOPS_PER_VOLUME = 0,
    MAX_IOPS_PER_VOLUME = 2000
);

CREATE RESOURCE POOL pool_oltp
WITH (
    MIN_CPU_PERCENT = 40,
    MAX_CPU_PERCENT = 100,
    MIN_MEMORY_PERCENT = 30,
    MAX_MEMORY_PERCENT = 100
);

The memory percentages govern query execution memory (sort and hash grants), not the buffer pool. Two pools always exist: internal for system tasks, which you cannot restrict, and default for anything not classified.

Workload groups

Groups sit inside pools and add per-request limits and importance relative to other groups in the same pool.

CREATE WORKLOAD GROUP wg_reporting
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MAX_CPU_TIME_SEC = 600,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
    MAX_DOP = 4,
    GROUP_MAX_REQUESTS = 10
) USING pool_reporting;

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

REQUEST_MAX_CPU_TIME_SEC only generates an event by default; to actually abort a request that exceeds it, enable trace flag 2422 (SQL Server 2016 SP2 and 2017 CU3 and later). MAX_DOP here overrides the instance and database settings for those sessions, which is a handy way to cap a reporting tool's parallelism without touching the whole instance. GROUP_MAX_REQUESTS queues additional requests rather than rejecting them.

The classifier function

This is the part I'm most careful with. The classifier is a schema-bound scalar function in master that returns the workload group name as sysname. It runs for every login, so keep it trivial and fast; a slow classifier makes every connection slow.

USE master;
GO
CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp sysname = N'default';

    IF SUSER_SNAME() IN (N'report_user', N'CORP\Analysts')
        OR APP_NAME() LIKE N'%Power BI%'
        OR APP_NAME() LIKE N'%Report Server%'
        SET @grp = N'wg_reporting';
    ELSE IF SUSER_SNAME() = N'app_service'
        SET @grp = N'wg_oltp';

    RETURN @grp;
END;
GO

Avoid table lookups inside the classifier, and never reference a user database from it. Returning a name that does not exist sends the session to default.

Turning it on

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

Existing sessions keep their current group; only new logins are classified. RECONFIGURE is also required after any change to pools or groups. Before you activate a new classifier, keep a Dedicated Admin Connection handy (sqlcmd -A); the DAC bypasses classification, so if the function locks everyone out you can still fix it.

Watching it work

SELECT s.session_id, s.login_name, s.program_name, g.name AS workload_group, p.name AS pool
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups g ON s.group_id = g.group_id
JOIN sys.dm_resource_governor_resource_pools p ON g.pool_id = p.pool_id
WHERE s.is_user_process = 1;

Cumulative statistics per pool and group show whether the limits are biting:

SELECT name, total_cpu_usage_ms, cache_memory_kb, used_memgrant_kb, read_io_throttled_total, active_request_count
FROM sys.dm_resource_governor_resource_pools;

SELECT name, total_request_count, total_queued_request_count, total_cpu_limit_violation_count,
       total_reduced_memgrant_count, max_request_cpu_time_ms
FROM sys.dm_resource_governor_workload_groups;

total_queued_request_count rising means GROUP_MAX_REQUESTS is queuing work; total_reduced_memgrant_count means queries in that group are getting smaller memory grants than they asked for.

A real test

Connect as report_user and run a heavy query while the OLTP login runs a steady load. In Performance Monitor, the SQLServer:Resource Pool Stats object has a CPU usage % counter per pool; the reporting pool should flatten at the cap while pool_oltp keeps its share. Compare the OLTP procedure's average duration from Query Store before and after; that is the number the business cares about.

Trouble

Everything lands in default. The classifier is not enabled (SELECT classifier_function_id, is_enabled FROM sys.resource_governor_configuration;) or RECONFIGURE was not run, or the function returned a group name with a typo.

Nobody can log in after enabling a classifier. The function errors or takes too long (there is a login timeout). Connect via the DAC and run ALTER RESOURCE GOVERNOR DISABLE;, then fix the function.

"Msg 10904: Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools." You cannot drop or re-parent a group while sessions are using it. Kill them or wait.

CPU caps seem to have no effect. MAX_CPU_PERCENT only applies under contention; if the server has idle CPU, the pool is allowed to use it. Use CAP_CPU_PERCENT for a hard limit, and remember that CPU percentages are per scheduler, so a single-threaded query in a 30 percent pool can still peg one core.

It won't make a bad query good, but it keeps one from taking everyone else down. Start small (two pools, a login-based classifier) and tighten from the DMV numbers, not guesses.