LLI Wiki — My Digital Knowledge Base

SQL Server Resource Governor: pools, workload groups and classifier function

Configure Resource Governor with three resource pools, three workload groups and a login-based classifier function.

Resource Governor lets you limit the CPU and memory that groups of sessions can consume on SQL Server. This example routes sessions to three workload groups (admin, application, reporting) through a classifier function, each group being bound to its own resource pool.

Principle

  1. A classifier function (created in master, with SCHEMABINDING) returns the name of a workload group for each new session.
  2. Each workload group belongs to a resource pool that defines CPU and memory limits.
  3. The classifier is registered with ALTER RESOURCE GOVERNOR, then the configuration is applied with RECONFIGURE.

Resource pools

CREATE RESOURCE POOL [poolApp]
    WITH (min_cpu_percent=10,
          max_cpu_percent=20,
          min_memory_percent=0,
          max_memory_percent=100);
GO

CREATE RESOURCE POOL [poolAdmin]
    WITH (min_cpu_percent=10,
          max_cpu_percent=100,
          min_memory_percent=0,
          max_memory_percent=100);
GO

CREATE RESOURCE POOL [poolReport]
    WITH (min_cpu_percent=10,
          max_cpu_percent=70,
          min_memory_percent=0,
          max_memory_percent=100);
GO

Workload groups

CREATE WORKLOAD GROUP [GroupAdmin]
    WITH (group_max_requests=0,
          importance=Medium,
          request_max_cpu_time_sec=0,
          request_max_memory_grant_percent=25,
          request_memory_grant_timeout_sec=0,
          max_dop=0)
    USING [poolAdmin];
GO

CREATE WORKLOAD GROUP [GroupApp]
    WITH (group_max_requests=300,
          importance=Medium,
          request_max_cpu_time_sec=0,
          request_max_memory_grant_percent=25,
          request_memory_grant_timeout_sec=0,
          max_dop=1)
    USING [poolApp];
GO

CREATE WORKLOAD GROUP [GroupReport]
    WITH (group_max_requests=0,
          importance=Medium,
          request_max_cpu_time_sec=0,
          request_max_memory_grant_percent=25,
          request_memory_grant_timeout_sec=0,
          max_dop=0)
    USING [poolReport];
GO

Classifier function

The function classifies sessions by login name or application name. The login patterns below are examples.

USE master;
GO
CREATE FUNCTION [dbo].[rgclassifier_V1]() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name AS sysname;
    IF (SUSER_NAME() LIKE '%adm%' OR APP_NAME() LIKE '%SQL Server Management%')
        SET @grp_name = 'GroupAdmin';
    IF (SUSER_NAME() LIKE '%AppService')
        SET @grp_name = 'GroupApp';
    IF (SUSER_NAME() LIKE '%Report%')
        SET @grp_name = 'GroupReport';
    RETURN @grp_name;
END;
GO

Enable the classifier

USE master;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[rgclassifier_V1]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Check the configuration

SELECT * FROM sys.dm_resource_governor_workload_groups;
SELECT * FROM sys.dm_resource_governor_configuration;
SELECT * FROM sys.dm_resource_governor_resource_pools;

To test the classifier for a given login, run SELECT [dbo].[rgclassifier_V1](); inside an EXECUTE AS LOGIN = '...' block, then REVERT.

Notes

Mastodon LLIMastodon SocialFosstodon