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
- A classifier function (created in
master, withSCHEMABINDING) returns the name of a workload group for each new session. - Each workload group belongs to a resource pool that defines CPU and memory limits.
- The classifier is registered with
ALTER RESOURCE GOVERNOR, then the configuration is applied withRECONFIGURE.
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
- A session that matches no condition gets a
NULLreturn value and falls into thedefaultworkload group. - If several conditions match, the last one wins; order the tests accordingly.
- Changes only take effect after
ALTER RESOURCE GOVERNOR RECONFIGURE.