SQL Server Resource Governor Setup

🔧 SQL Server Resource Governor: The Complete Configuration Guide

Resource Governor is SQL Server's built-in workload management tool that allows DBAs to control CPU, memory, and I/O resources for different workloads. It prevents runaway queries from consuming all server resources and ensures critical applications get the resources they need. This comprehensive guide covers everything you need to implement Resource Governor effectively.

📊 Understanding Resource Governor Architecture

Core Components

Resource Governor Flow

+------------------+     +-------------------+     +----------------+
|  New Connection  | --> | Classifier        | --> | Workload       |
|                  |     | Function          |     | Group          |
+------------------+     +-------------------+     +----------------+
                                                          |
                                                          v
                                                   +----------------+
                                                   | Resource       |
                                                   | Pool           |
                                                   +----------------+

🚀 Setting Up Resource Governor

Step 1: Create Resource Pools

-- Create resource pool for OLTP workloads (high priority)
CREATE RESOURCE POOL PoolOLTP
WITH (
    MIN_CPU_PERCENT = 30,      -- Guaranteed minimum CPU
    MAX_CPU_PERCENT = 80,      -- Maximum CPU allowed
    MIN_MEMORY_PERCENT = 30,   -- Guaranteed minimum memory
    MAX_MEMORY_PERCENT = 70,   -- Maximum memory allowed
    CAP_CPU_PERCENT = 100,     -- Hard CPU cap (Enterprise only)
    AFFINITY SCHEDULER = AUTO  -- CPU affinity
);

-- Create resource pool for reporting workloads (lower priority)
CREATE RESOURCE POOL PoolReporting
WITH (
    MIN_CPU_PERCENT = 10,
    MAX_CPU_PERCENT = 50,
    MIN_MEMORY_PERCENT = 10,
    MAX_MEMORY_PERCENT = 40,
    CAP_CPU_PERCENT = 60       -- Hard cap at 60% CPU
);

-- Create resource pool for ad-hoc queries (lowest priority)
CREATE RESOURCE POOL PoolAdHoc
WITH (
    MIN_CPU_PERCENT = 0,       -- No guaranteed CPU
    MAX_CPU_PERCENT = 30,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 20,
    CAP_CPU_PERCENT = 30
);

-- Create resource pool for maintenance tasks
CREATE RESOURCE POOL PoolMaintenance
WITH (
    MIN_CPU_PERCENT = 5,
    MAX_CPU_PERCENT = 40,
    MIN_MEMORY_PERCENT = 5,
    MAX_MEMORY_PERCENT = 30
);

Step 2: Create Workload Groups

-- Workload group for critical OLTP transactions
CREATE WORKLOAD GROUP GroupOLTP
WITH (
    IMPORTANCE = HIGH,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MAX_CPU_TIME_SEC = 0,           -- Unlimited
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
    MAX_DOP = 4,
    GROUP_MAX_REQUESTS = 0                  -- Unlimited
)
USING PoolOLTP;

-- Workload group for reports
CREATE WORKLOAD GROUP GroupReporting
WITH (
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
    REQUEST_MAX_CPU_TIME_SEC = 600,         -- 10 minute max
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120,
    MAX_DOP = 8,
    GROUP_MAX_REQUESTS = 20                 -- Limit concurrent reports
)
USING PoolReporting;

-- Workload group for ad-hoc queries
CREATE WORKLOAD GROUP GroupAdHoc
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 15,
    REQUEST_MAX_CPU_TIME_SEC = 120,         -- 2 minute max
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 30,
    MAX_DOP = 2,
    GROUP_MAX_REQUESTS = 50
)
USING PoolAdHoc;

-- Workload group for maintenance
CREATE WORKLOAD GROUP GroupMaintenance
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
    REQUEST_MAX_CPU_TIME_SEC = 0,
    MAX_DOP = 4,
    GROUP_MAX_REQUESTS = 5                  -- Limit concurrent maintenance jobs
)
USING PoolMaintenance;

Step 3: Create Classifier Function

-- Create classifier function in master database
USE master;
GO

CREATE FUNCTION dbo.ClassifierFunction()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME;

    -- Route based on application name
    IF APP_NAME() LIKE '%OLTP%'
        OR APP_NAME() LIKE '%Production%'
        OR APP_NAME() LIKE '%WebApp%'
        SET @WorkloadGroup = 'GroupOLTP';

    ELSE IF APP_NAME() LIKE '%Report%'
        OR APP_NAME() LIKE '%SSRS%'
        OR APP_NAME() LIKE '%PowerBI%'
        SET @WorkloadGroup = 'GroupReporting';

    ELSE IF APP_NAME() LIKE '%Maintenance%'
        OR APP_NAME() LIKE '%Backup%'
        OR APP_NAME() LIKE '%SQLAgent%'
        SET @WorkloadGroup = 'GroupMaintenance';

    ELSE IF SUSER_NAME() LIKE '%developer%'
        OR APP_NAME() LIKE '%SSMS%'
        SET @WorkloadGroup = 'GroupAdHoc';

    -- Default to ad-hoc for unclassified connections
    ELSE
        SET @WorkloadGroup = 'GroupAdHoc';

    RETURN @WorkloadGroup;
END;
GO

Step 4: Enable Resource Governor

-- Register the classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFunction);

-- Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- Verify it's enabled
SELECT
    is_enabled,
    classifier_function_id,
    OBJECT_NAME(classifier_function_id, DB_ID('master')) AS classifier_function
FROM sys.resource_governor_configuration;

💡 Advanced Classifier Patterns

Multi-Criteria Classification

CREATE FUNCTION dbo.ClassifierFunctionAdvanced()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME = 'GroupAdHoc';  -- Default
    DECLARE @LoginName SYSNAME = SUSER_NAME();
    DECLARE @AppName SYSNAME = APP_NAME();
    DECLARE @HostName SYSNAME = HOST_NAME();

    -- Priority 1: Route by specific service accounts
    IF @LoginName = 'svc_production'
        SET @WorkloadGroup = 'GroupOLTP';

    ELSE IF @LoginName = 'svc_reporting'
        SET @WorkloadGroup = 'GroupReporting';

    ELSE IF @LoginName = 'svc_etl'
        SET @WorkloadGroup = 'GroupMaintenance';

    -- Priority 2: Route by application name
    ELSE IF @AppName LIKE '%CriticalApp%'
        SET @WorkloadGroup = 'GroupOLTP';

    -- Priority 3: Route by host/server name
    ELSE IF @HostName LIKE 'WEBSERVER%'
        SET @WorkloadGroup = 'GroupOLTP';

    ELSE IF @HostName LIKE 'REPORTSERVER%'
        SET @WorkloadGroup = 'GroupReporting';

    -- Priority 4: Time-based routing
    ELSE IF DATEPART(HOUR, GETDATE()) BETWEEN 22 AND 6
        -- Night hours: Allow more resources for maintenance
        SET @WorkloadGroup = 'GroupMaintenance';

    RETURN @WorkloadGroup;
END;
GO

Database-Based Classification

CREATE FUNCTION dbo.ClassifierByDatabase()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME;

    -- Can only use ORIGINAL_DB_NAME() or check specific conditions
    IF ORIGINAL_DB_NAME() = 'ProductionDB'
        SET @WorkloadGroup = 'GroupOLTP';

    ELSE IF ORIGINAL_DB_NAME() IN ('ReportingDW', 'Analytics')
        SET @WorkloadGroup = 'GroupReporting';

    ELSE
        SET @WorkloadGroup = 'GroupAdHoc';

    RETURN @WorkloadGroup;
END;
GO

📊 I/O Resource Governance (SQL Server 2014+)

Configure I/O Resource Pools

-- Enable I/O governance on resource pools
ALTER RESOURCE POOL PoolOLTP
WITH (
    MIN_IOPS_PER_VOLUME = 100,   -- Guaranteed minimum IOPS
    MAX_IOPS_PER_VOLUME = 1000   -- Maximum IOPS cap
);

ALTER RESOURCE POOL PoolReporting
WITH (
    MIN_IOPS_PER_VOLUME = 50,
    MAX_IOPS_PER_VOLUME = 500
);

ALTER RESOURCE POOL PoolAdHoc
WITH (
    MIN_IOPS_PER_VOLUME = 0,
    MAX_IOPS_PER_VOLUME = 200
);

-- Apply changes
ALTER RESOURCE GOVERNOR RECONFIGURE;

⚠️ Warning: I/O governance requires that your storage subsystem can report accurate IOPS metrics. Test thoroughly before implementing in production.

📈 Monitoring Resource Governor

Current Resource Usage

-- Resource pool statistics
SELECT
    rp.pool_id,
    rp.name AS pool_name,
    rp.min_cpu_percent,
    rp.max_cpu_percent,
    rp.cap_cpu_percent,
    rp.min_memory_percent,
    rp.max_memory_percent,
    rps.active_memgrant_count,
    rps.active_memgrant_kb / 1024 AS active_memgrant_mb,
    rps.target_memory_kb / 1024 AS target_memory_mb,
    rps.used_memgrant_kb / 1024 AS used_memgrant_mb,
    rps.total_cpu_usage_ms,
    rps.read_io_completed_total,
    rps.write_io_completed_total
FROM sys.resource_governor_resource_pools rp
LEFT JOIN sys.dm_resource_governor_resource_pools rps
    ON rp.pool_id = rps.pool_id
ORDER BY rp.pool_id;

-- Workload group statistics
SELECT
    wg.group_id,
    wg.name AS group_name,
    rp.name AS pool_name,
    wg.importance,
    wg.max_dop,
    wg.request_max_memory_grant_percent,
    wgs.active_request_count,
    wgs.queued_request_count,
    wgs.total_request_count,
    wgs.total_cpu_usage_ms,
    wgs.total_reduced_memgrant_count
FROM sys.resource_governor_workload_groups wg
INNER JOIN sys.resource_governor_resource_pools rp
    ON wg.pool_id = rp.pool_id
LEFT JOIN sys.dm_resource_governor_workload_groups wgs
    ON wg.group_id = wgs.group_id
ORDER BY wg.pool_id, wg.group_id;

Session Classification

-- See which workload group each session is in
SELECT
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    wg.name AS workload_group,
    rp.name AS resource_pool,
    r.cpu_time,
    r.logical_reads,
    r.granted_query_memory,
    r.dop
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_resource_governor_workload_groups wg
    ON s.group_id = wg.group_id
LEFT JOIN sys.resource_governor_resource_pools rp
    ON wg.pool_id = rp.pool_id
LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY wg.name, s.session_id;

Historical Statistics

-- Aggregate statistics by pool and group
SELECT
    rp.name AS pool_name,
    wg.name AS group_name,
    wgs.total_request_count,
    wgs.total_cpu_usage_ms / 1000 / 60 AS total_cpu_minutes,
    wgs.total_cpu_limit_violation_count,
    wgs.max_request_cpu_msec / 1000 AS max_request_cpu_sec,
    wgs.blocked_task_count,
    wgs.total_lock_wait_count,
    wgs.total_lock_wait_time_ms / 1000 AS total_lock_wait_sec,
    wgs.total_reduced_memgrant_count,
    wgs.total_memgrant_timeout_count
FROM sys.dm_resource_governor_workload_groups wgs
INNER JOIN sys.resource_governor_workload_groups wg
    ON wgs.group_id = wg.group_id
INNER JOIN sys.resource_governor_resource_pools rp
    ON wg.pool_id = rp.pool_id
ORDER BY wgs.total_cpu_usage_ms DESC;

🔧 Common Use Cases

Scenario 1: Limit Report Query Resources

-- Create pool and group for reporting with strict limits
CREATE RESOURCE POOL PoolReportingLimited
WITH (
    MIN_CPU_PERCENT = 5,
    MAX_CPU_PERCENT = 25,
    CAP_CPU_PERCENT = 30,
    MIN_MEMORY_PERCENT = 5,
    MAX_MEMORY_PERCENT = 25
);

CREATE WORKLOAD GROUP GroupReportingLimited
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 20,
    REQUEST_MAX_CPU_TIME_SEC = 300,  -- 5 minute timeout
    MAX_DOP = 4,
    GROUP_MAX_REQUESTS = 10
)
USING PoolReportingLimited;

Scenario 2: Protect OLTP from Analytics

-- Ensure OLTP always has resources
CREATE RESOURCE POOL PoolCriticalOLTP
WITH (
    MIN_CPU_PERCENT = 40,      -- Always get at least 40% CPU
    MAX_CPU_PERCENT = 100,
    MIN_MEMORY_PERCENT = 40,
    MAX_MEMORY_PERCENT = 80
);

CREATE WORKLOAD GROUP GroupCriticalOLTP
WITH (
    IMPORTANCE = HIGH,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    MAX_DOP = 4                 -- Limit parallelism for OLTP
)
USING PoolCriticalOLTP;

Scenario 3: Control Developer Queries

-- Limit resources for developer queries
CREATE RESOURCE POOL PoolDevelopers
WITH (
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 20,
    CAP_CPU_PERCENT = 25,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 15
);

CREATE WORKLOAD GROUP GroupDevelopers
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 10,
    REQUEST_MAX_CPU_TIME_SEC = 60,   -- 1 minute timeout
    MAX_DOP = 2,
    GROUP_MAX_REQUESTS = 20
)
USING PoolDevelopers;

✅ Best Practices

Resource Pool Guidelines

Classifier Function Best Practices

1. Keep it simple - Complex logic impacts every connection
2. Use deterministic values - APP_NAME(), SUSER_NAME(), HOST_NAME()
3. Avoid queries - Don't access tables in classifier
4. Test thoroughly - Verify all connection scenarios
5. Default wisely - Unknown connections should get limited resources

Testing Changes

-- Test classifier function without enabling
SELECT dbo.ClassifierFunction();

-- Simulate classification for specific context
EXECUTE AS LOGIN = 'TestUser';
SELECT dbo.ClassifierFunction();
REVERT;

-- Validate before enabling
ALTER RESOURCE GOVERNOR RECONFIGURE;

💡 Pro Tip: Always test Resource Governor changes in a non-production environment. Incorrect classifier functions can cause all connections to fail.

🔧 Troubleshooting

Disable Resource Governor (Emergency)

-- Disable Resource Governor
ALTER RESOURCE GOVERNOR DISABLE;

-- Or remove classifier function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Common Issues

-- Check for classifier errors
SELECT
    start_time,
    error_number,
    error_severity,
    error_message
FROM sys.dm_resource_governor_classifier_running_stats;

-- Sessions not being classified correctly
SELECT
    s.session_id,
    s.login_name,
    s.program_name,
    s.group_id,
    wg.name AS expected_group
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_resource_governor_workload_groups wg
    ON s.group_id = wg.group_id
WHERE s.is_user_process = 1
  AND wg.name != 'expected_group_name';  -- Adjust as needed

Conclusion

Resource Governor is essential for managing multi-tenant or mixed-workload SQL Server environments. Key takeaways:

• Resource Pools define resource limits (CPU, memory, I/O)
• Workload Groups organize sessions with similar needs
• Classifier Function routes connections to appropriate groups
• Monitor continuously to validate resource allocation
• Test thoroughly before enabling in production

With proper implementation, Resource Governor ensures critical workloads always have the resources they need.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

• SQL Server Memory Management Best Practices
• SQL Server Performance Tuning Guide
• SQL Server TempDB Configuration

Need help with SQL Server optimization? Contact us for expert database consulting.