SQL Server - Resource Governor part 2 - Setup

The Resource Governor is a feature of SQL Server that can manage how much system resources different loads are allowed to use and how. It is possible to limit/monitor/prioritize the amount of CPU and memory that a specific load can use on an instance.

In part 1 of the Resource Governor mini-series, I briefly described what the Resource Governor can be used for and the principle behind it. Here I wanted to explain how you can use TSQL to set up the components. We continue with the example from part 1 where the finance department wants to run reports of product sales against a webshop that normally has predominantly OLTP load. In order to avoid resource conflicts, they want to limit the resource usage of the report component. Other calls should use the default pool.

In this case, the report tool and the web shop have different SQL logins so you can easily determine which type of connection it is, reportUser is used by the report tool.

Step 1: Create the Resource Pool for the report part

We allocate only a small part of the resources to the report runs so that the more critical parts are not affected.

CREATE RESOURCE POOL ReportPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO

Step 2: Create Workload Group
Create a Workload that is linked to the Resource pool we just created.

CREATE WORKLOAD GROUP ReportGroup
USING ReportPool ;
GO

Step 3: Create a user defined function that defines which Workload group a load should use.
In this example, we use SUSER_NAME() to determine which Workgroup to use.

CREATE FUNCTION dbo. Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workloadGroup AS SYSNAME
IF(SUSER_NAME() = 'reportUser')
SET @WorkloadGroup = 'ReportGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

Step 4: In the fourth and final step, we start the Resource Governor with the Classifier function we created in step 3.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.Classifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Step 5: If you tested the steps above yourself and want to remove the example, you can use the script below. It will deactivate the Resource Governor and remove all the objects we have created so far.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.Classifier
GO
DROP WORKLOAD GROUP ReportGroup
GO
DROP RESOURCE POOL ReportPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I hope this has given you some understanding of how to set up the Resource Governor and some ideas of what to do with it.

/Björn