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.