How To Limit CPU Usage in SQL Server Using Resource Governor

SQL Backup with High CPU Use Case

speed limit

SQL Server has a feature called Resource Governor that can limit the usage of CPU, Memory and IO. If you want to control resource consumption of an application connecting to SQL Server, Resource Governor can do that.

Supposed you have a backup job that is running and taking 100% of our CPU, we can use resource governor to limit the usage consumption to your desired percentage say 20%.

Note: adding a limit to CPU usage on backups can make your backups take longer to finish. Review your RPO and RTO requirements.

Configure Resource Governor

Let’s start by creating a lookup table so we can reference it on our Resource Governor function:

/*Enable Resource Governor*/
ALTER RESOURCE GOVERNOR RECONFIGURE;

/*create lookup table*/
USE [master]
GO

CREATE TABLE dbo.RG_BackupJob
( job_id uniqueidentifier
	, [name] sysname
	, match_string nvarchar(256) PRIMARY KEY
);

Next step is we insert the job on that table:

/*insert to RG_BackupJob with backup name from sysjobs table*/
INSERT INTO dbo.RG_BackupJob (job_id, [name], match_string)
SELECT
	job_id, [name], N'SQLAgent - TSQL JobStep (Job ' + CONVERT(VARCHAR(36), CONVERT(BINARY(16), job_id),1) + '%'
FROM msdb.dbo.sysjobs
WHERE [name] LIKE '%backup%'; 

Then we need to create our resource pool and workgroup objects:

USE [master]
GO

/*Create resource pool to be used*/
CREATE RESOURCE POOL poolThrottleBackup
WITH (
	MIN_CPU_PERCENT = 10 /*allocate at least 10% of the CPU bandwidth for backup*/
	, MAX_CPU_PERCENT = 20 /*do not let them exceed 20% either (avg CPU, can exceed if idle)*/
	, CAP_CPU_PERCENT = 20 /*hard cap on CPU*/
)
GO

/*Create backup workload group*/
CREATE WORKLOAD GROUP groupBackup
USING poolThrottleBackup
GO

After successfully created the resource pool and workgroup objects, we can now create a custom function that will reference to our lookup table dbo.RG_BackupJob we created earlier:

/*Create function*/
CREATE FUNCTION [dbo].[FN_RGBackupJob] ()
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
     /*Define the return sysname variable for the function*/
     DECLARE @app nvarchar(256) = APP_NAME()
	 , @grp_name AS sysname = 'default';

     /*Specify the T-SQL statements for mapping session information with Workload Groups defined for the Resource Governor.*/
     IF @app LIKE N'SQLAgent - TSQL JobStep%'
	 BEGIN
		IF EXISTS (SELECT 1 FROM dbo.RG_BackupJob WHERE match_string LIKE @app)
		BEGIN
			SET @grp_name = N'groupBackup';
		END
	 END

     RETURN @grp_name;
END
GO

Make sure to follow Microsoft best practice on Resource Governor.

Once the custom function created successfully, we can now use it as our classifer:

/*Set the classifier function for Resource Governor*/
ALTER RESOURCE GOVERNOR 
WITH ( CLASSIFIER_FUNCTION = [dbo].[FN_RGBackupJob])
GO

Last step, we need to execute the reconfigure to updates our changes:

/*Make changes effective*/
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Above script you can get it from Template Explorer.

Quick Check

Check the Resource Governor function:

SELECT
    c.classifier_function_id
	, function_name = OBJECT_SCHEMA_NAME(c.classifier_function_id)
    + '.' + OBJECT_NAME(c.classifier_function_id)
	, c.is_enabled
	, c2.is_reconfiguration_pending
FROM sys.resource_governor_configuration AS c
CROSS JOIN sys.dm_resource_governor_configuration AS c2
OPTION(RECOMPILE);
 
 /*Obtain the resource pool and workload group configuration*/
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;

Start running the backup job and execute below script to get the active session of our Resource Governor Group groupBackup:

/*get the Resource Group name and its session and request*/
SELECT
    g.group_id
	, g.name AS  GroupName
	, COALESCE(s.countsessions, 0) AS connectedsessions
    , g.active_request_count AS ActiveRequests
FROM sys.dm_resource_governor_workload_groups AS g
LEFT OUTER JOIN (SELECT group_id, COUNT(*) AS countsessions
                 FROM sys.dm_exec_sessions
                 GROUP BY group_id ) AS s ON g.group_id = s.group_id
OPTION(RECOMPILE);

Monitoring Resource Governor

On Perfmon, there is an object called Workload Group Stats with CPU usage % counter. You will see the poolThrottleBackup resource pool not exceeding 20%.

Summary

There you have it. Resource Governor can be a lifesaver and an option to quickly mitigate high intensive workloads. Above example is just one of many use cases of Resource Governor. If you are thinking of using Resource Governor, be aware that this feature is Enterprise only and it is not available in SQL Server Standard Edition.