Resource Governor Workload Groups and Pools: A Simple Setup Example

Resource Governor workload groups decide how much CPU and memory each kind of work on SQL Server can use. Resource Governor is a feature that manages SQL Server workload and system resource consumption. This post answers the basic questions first and then walks through a simple T-SQL example.

What Is Resource Governor?

Resource Governor can manage the SQL Server workload and the consumption of system resources. You can limit, govern or throttle the CPU and memory that a workload uses on SQL Server.

Why Do You Need It?

Different workloads run on one SQL Server, and each needs different resources. When they compete for resources, they affect the performance of the whole server. In that case Resource Governor becomes important.

Two Real-World Scenarios

Scenario 1: a server runs an OLTP workload and various resource-intensive reports. The ideal setup is two servers kept in sync, one for the OLTP transactions and one for the reports. Not everybody has that luxury. When reports and OLTP run on the same server, you can limit the resources of the reporting workload. Then the critical OLTP transactions are not throttled.

Scenario 2: two DBAs work in one organization. DBA A runs critical queries for the business, and DBA B does database maintenance. The work of DBA A should never be affected, but DBA B must be able to work as well. The ideal setup is that DBA B gets some resources and cannot get more than the defined amount.

The Default Components

SQL Server creates two Resource Governor components by default:

• Internal: used by the database engine exclusively. Users have no control over it.
• Default: used by all the workloads that are not assigned to any other group.

The Major Components and the Process

Resource Governor has three major components:

• Resource pools
• Workload groups
• Classification

In simple words, the process looks like this, and the diagram above shows the same flow:

1. Create a resource pool
2. Create a workload group
3. Create a classification function based on the criteria you specify
4. Enable Resource Governor with the classification function

Configure Resource Governor With T-SQL

Yes, you can configure Resource Governor with T-SQL. Here is the code, with an explanation between the steps.

Step 0: we assume that there are separate login accounts for the reporting server and the OLTP server.

/*-----------------------------------------------
Step 0: (Optional and for Demo Purpose)
Create Two User Logins
1) ReportUser, 2) PrimaryUser
Use ReportUser login for Reports workload
Use PrimaryUser login for OLTP workload
-----------------------------------------------*/

Step 1: create the resource pools. We create two: one for the report server and one for the primary OLTP server. As scenario 1 describes, the OLTP side is mission critical and the report side is not. So the report pool gets only a few resources.

-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report Server
CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for OLTP Primary Server
CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

Step 2: create the workload groups. We create two, and each maps to one of the resource pools we created in step 1.

-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report Server
CREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;
GO
-- Creating Workload Group for OLTP Primary Server
CREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;
GO

Step 3: create a user-defined function that routes each workload to the right workload group. In this example we check SUSER_NAME() to choose the group. You can use other functions too, such as HOST_NAME(), APP_NAME() and IS_MEMBER().

-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportServerGroup'
ELSE IF (SUSER_NAME() = 'PrimaryUser')
SET @WorkloadGroup = 'PrimaryServerGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

Step 4: in this final step we enable Resource Governor with the classifier function from step 3.

-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Step 5: if you followed this demo and want to clean up, run the script below. It disables Resource Governor and deletes all the objects created so far.

-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO
DROP WORKLOAD GROUP ReportServerGroup
GO
DROP WORKLOAD GROUP PrimaryServerGroup
GO
DROP RESOURCE POOL ReportServerPool
GO
DROP RESOURCE POOL PrimaryServerPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I hope this introductory example sheds some light on Resource Governor. In future posts we will take the same example and learn a few more details.

Resource Governor is not a speed boost, it is a limit that protects the work that matters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.