Configure SQL Server Resource Governor to Manage CPU, Memory and I/O

SQL Server Resource Governor is a feature that can be used to manage SQL Server workload and system resource consumption. Resource Governor enables us to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
This feature was introduced in SQL Server 2008 to set the limit on resources consumed by specific workload. Before SQL Server 2014, this feature was used to specify limits of the amount of CPU and memory that incoming application requests can use. However SQL Server 2014 onward, we can also specify limits of physical IO.
It is helpful in an environment where multiple distinct workloads are present on the same server, Resource Governor enables us to differentiate these workloads and allocate shared resources as they are requested, based on the limits that is specified. These resources are CPU, physical IO, and memory.

Resource Governor Concepts

The following three concepts are fundamental to understanding and using Resource Governor:


Resource pool is a way to limit resource consumption for one of more workload groups. Two resource pools (internal and default) are created when SQL Server is installed. This pool cannot be altered.
The pool resources are defined by specifying one or more of the following settings for each resource (CPU, memory, and physical IO):
  • MIN_CPU_PERCENT and MAX_CPU_PERCENT settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. Use these settings to establish predictable CPU resource usage for multiple workloads that is based on the needs of each workload. 
  • MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT settings are the minimum and maximum amount of memory reserved for the resource pool that cannot be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory. Setting a minimum memory value for a pool ensures that the percentage of memory specified will be available for any requests that might run in this resource pool. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of overall memory. 
  • CAP_CPU_PERCENT settings is a hard cap limit on the CPU bandwidth for all requests in the resource pool. Workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT. 
  • MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are the minimum and maximum physical IO operations per second (IOPS) per disk volume for a resource pool. Use these settings to control the physical IOs issued for user threads for a given resource pool.
Workload group is just a way of grouping together queries, tasks, user connections, etc. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed. Resource Governor also supports user-defined workload groups.A user cannot change anything classified as an internal group, but can monitor it.

Classification process assigns incoming sessions to a workload group based on the characteristics of the session. Classification is based on the set of user-defined scalar function that allow us to customize how incoming requests are routed Resource Governor also supports a classifier user-defined function for implementing classification rules.

Putting Concepts Together


Demo on how to configure resource governor 

Create a resource pool to limit the usage for each resource.Resource pool is defined with minimum and maximum values of resource (Memory, CPU and IOPS) utilization. The minimum values are defined in a way that sum of minimum values of each workload does not exceed 100. The max value of each can vary between the minimum value and 100.
The following example shows the creation of three resource pool dba_pool, bi_pool and dev_pool which will limit resource as shown below.

-- Step 1) Verify Resource Governor Configuration
USE [master]
GO
SELECT * FROM [sys].[resource_governor_configuration]
GO

-- Step 2) Create the Resource Pools
CREATE RESOURCE POOL [dba_pool] WITH (MIN_CPU_PERCENT = 5, MAX_CPU_PERCENT = 10, MIN_MEMORY_PERCENT = 10, MAX_MEMORY_PERCENT = 20, MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 40)
GO
CREATE RESOURCE POOL [bi_pool] WITH (MIN_CPU_PERCENT = 10, MAX_CPU_PERCENT = 20, MIN_MEMORY_PERCENT = 5, MAX_MEMORY_PERCENT = 10, MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 30)
GO
CREATE RESOURCE POOL [dev_pool] WITH (MIN_CPU_PERCENT = 10, MAX_CPU_PERCENT = 30, MIN_MEMORY_PERCENT = 20, MAX_MEMORY_PERCENT = 40, MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 50)
GO

-- Step 3) verify the Resource Pools
SELECT * FROM [sys].[resource_governor_resource_pools]
GO


After the resource pool has been created we need to create workload group. All workloads are assigned to some specific resource pool. A resource pool may have zero or more workload groups assigned to it. In the following example, the 3 workload group with named dev_group, dba_group and bi_group is created and it is associated with dev_pool, dba_pool, bi_pool resource pool respectively.

-- Step 4) Create the Workload Groups
CREATE WORKLOAD GROUP [dev_group] WITH ( IMPORTANCE = HIGH, MAX_DOP = 1 ) USING [dev_pool]
GO
CREATE WORKLOAD GROUP [dba_group] WITH ( IMPORTANCE = MEDIUM, MAX_DOP = 4 ) USING [dba_pool]
GO
CREATE WORKLOAD GROUP [bi_group] WITH ( IMPORTANCE = LOW, MAX_DOP = 0 ) USING [bi_pool]
GO

-- Step 5) verify the Workload groups
SELECT * FROM [sys].[resource_governor_workload_groups]
GO


After the workload group has been created and assigned to respective resource pool. We will be create three users dev_user, dba_user and bi_user and grant the alter permissions.

-- Step 6) Create the Logins and users
Use [Master]
GO
CREATE LOGIN [dev_user] WITH PASSWORD = N'password', CHECK_POLICY = OFF, DEFAULT_DATABASE = AdventureWorks
GO
CREATE LOGIN [dba_user] WITH PASSWORD = N'password', CHECK_POLICY = OFF, DEFAULT_DATABASE = AdventureWorks
GO
CREATE LOGIN [bi_user] WITH PASSWORD = N'password', CHECK_POLICY = OFF, DEFAULT_DATABASE = AdventureWorks
GO

USE [AdventureWorks]
GO
CREATE USER [dev_user] FOR LOGIN [dev_user]
GO
CREATE USER [dba_user] FOR LOGIN [dba_user]
GO
CREATE USER [bi_user] FOR LOGIN [bi_user]
GO

--Assign permission to logins
GRANT ALTER TO [dev_user]
GRANT ALTER TO [dba_user]
GRANT ALTER TO [bi_user]
GO

Next step is to create the classifier function that will be used to route the appropriate workloads to the correct workload group. In the listing below classify_users is used to route requests from the dev_user, dba_user and bi_user to dev_group, dba_group and bi_group workload group respectively. All other requests will go the default workload group.

-- Step 7) Create the Classifier Function
USE [master]
GO
CREATE FUNCTION [classify_users]()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @group_type NVARCHAR(30)
 SET @group_type = N'default'
 if SUSER_SNAME() = N'dev_user'
   SET @group_type = N'dev_group'
 else if SUSER_SNAME() = N'dba_user'
   SET @group_type = N'dba_group'
 else if SUSER_SNAME() = N'bi_user'
   SET @group_type = N'bi_group'
 return @group_type
END
GO

Associate the classifier function with the Resource Governor using the ALTER RESOURCE GOVERNOR command.

-- Step 8) Configure and verify the Resource Governor
ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.classify_users )
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

SELECT OBJECT_NAME(classifier_function_id) [Function Name],* FROM [sys].[resource_governor_configuration]
GO

Performance Monitoring

  • Open a three new query window in SQL Server Management Studio, and connect as the dev_user, dba_user and bi_user. In other words, open three sessions associated with three users.
  • We will run rebuild index command on some tables to generate some load with these three users and try to gather data. Paste the code in a separate sessions and execute the query simultaneously.
-- Copy the below query in dev_user session. DO NOT RUN
USE [AdventureWorks]
GO
DECLARE @count as int = 1
WHILE @count <= 1
BEGIN
       ALTER INDEX ALL ON [Production].[ProductProductPhoto] REORGANIZE
END
GO

-- Copy the below query in dba_user session. DO NOT RUN
USE [AdventureWorks]
GO
DECLARE @count as int = 1
WHILE @count <= 1
BEGIN
       ALTER INDEX ALL ON [Production].[TransactionHistory] REORGANIZE
END
GO

-- Copy the below query in bi_user session. DO NOT RUN
USE [AdventureWorks]
GO
DECLARE @count as int = 1
       WHILE @count <= 1
BEGIN
       ALTER INDEX ALL ON [Production].[TransactionHistoryArchive] REORGANIZE
END
GO
  • Now, click on the Windows Start → Run 
  • Type perfmon and hit enter. 
  • Remove any default counters by clicking on the first counter in the list below the graph and then pressing the delete key as many times as necessary. 
  • Press (Ctrl+R) to change to the text based Report Mode. 
  • In the perfmon toolbar, click on “+” toolbar button 
  • Add the following counters
    • SQLSERVER:Resource Pool Stats: CPU Usage % (All instances) 
    • SQLSERVER:Resource Pool Stats: Query exec memory target (All instances)
    • SQLSERVER:Resource Pool Stats: Target Memory (All instances)


Add the following counter
  • SQLSERVER:Resource Pool Stats: Disk Read IO/sec 
  • SQLSERVER:Resource Pool Stats: Disk Read IO Throttled/sec
We have set the MAX_IOPS_PER_VOLUME setting for each pool. This will set a hard limit for the maximum IOPS to guarantee predicable performance and balance rest of the user’s workload on the instance:





Cleanup Resource Governor Configuration

Finally, Clean up T-SQL statements for our demo: 
  1. Drop the work load group.
  2. Drop the resource pool.
  3. Disable and drop the login that was created earlier.
  4. Remove the entry for the classifier function and then disable Resource Governor (RG).
--Drop Workload Group
USE [master]
GO
DROP WORKLOAD GROUP [dev_group]
DROP WORKLOAD GROUP [dba_group]
DROP WORKLOAD GROUP [bi_group]
GO

--Drop Resource Pool
DROP RESOURCE POOL [dev_pool]
DROP RESOURCE POOL [dba_pool]
DROP RESOURCE POOL [bi_pool]
GO

--Drop Users
USE [AdventureWorks]
GO
DROP USER [dev_user]
DROP USER [dba_user]
DROP USER [bi_user]
GO

--Drop Logins
USE [master]
GO
DROP LOGIN [dev_user]
DROP LOGIN [dba_user]
DROP LOGIN [bi_user]
GO

--Disable and drop classifier function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION [classify_users]
GO

No comments:

Post a Comment