Using CPU Resource Governor for SQL Backups

As we discussed in previous post, while compressing data during backup operations will result in efficient disk I/O operations, it will significantly increase CPU usage which could impede other operations.

You can set a lower priority backup operation limiting CPU usage by using resource governor. In this post we will describe how to configure backup operation as a low priority operation.

There are several steps you have to complete to setup a resource governor.

1. Setup a SQL Server login you would use to perform this operation. Alternatively, you can use an existing user.

For example, you can run the following query to create a windows user, grant permission and assign the role of backup operator to the AdventureWorks database.

-- Create a new login
USE master;
CREATE LOGIN [<Your Domain>\ResourceGovernor] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [<Your Domain>\ResourceGovernor];
GO
-- add this user to AdventureWorks and assign him/her to DB Operator rule
USE AdventureWorks;
CREATE USER [<Your Domain>\ResourceGOvernor] FOR LOGIN [<Your Domain>\ResourceGovernor];
EXEC sp_addrolemember 'db_backupoperator', '<Your Domain>\ResourceGovernor';
GO

2. Create a Resource Governor resource pool which will limit the maximum average CPU bandwidth that will be given to this resource pool. You can create a resource pool governor using the following command.

CREATE RESOURCE POOL CPUGovernor;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

It will create a resource pool called CPUGovernor with default settings. You can also pass additional parameters such as...

MIN_CPU_PERCENT - The minimum % of CPU resource to limit for this pool.
MAX_CPU_PERCENT - The maximum % of CPU resource to limit for this pool.
MIN_MEMORY_PERCENT - The minimum % of memory to limit for this pool.  MAX_MEMORY_PERCENT - The maximum % of memory resource to limit for this pool.

CREATE RESOURCE POOL CPUGovernor
 WITH
    (  MIN_CPU_PERCENT = 2,
       MAX_CPU_PERCENT = 10,
       MIN_MEMORY_PERCENT = 10,
       MAX_MEMORY_PERCENT = 20
    )

3. Create a Resource Governor workload group that will use this pool.

CREATE WORKLOAD GROUP NewWorkLoadGroup
    USING "default" ;
GO

This will create a new workload group with default values. Alternatively, you can pass certain parameters.

CREATE WORKLOAD GROUP NewWorkLoadGroup
WITH
    ( IMPORTANCE = { LOW | MEDIUM | HIGH },
      REQUEST_MAX_MEMORY_GRANT_PERCENT=10, --max amount of memory a single request can take
      REQUEST_MAX_CPU_TIME_SEC=10, --max CPU time a request can take
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC=10, --max time a query can wait for memory 
      MAX_DOP = 1 -- specifies maximum degree of parallelism
      GROUP_MAX_REQUESTS = 2 -- maximum # of simultaneous requests that can execute)
[ USING { pool_name | "CPUGovernor" } ] -- using above defined resource pool

4. Create a user defined classifier function which will relate the user you created in step 1 with the workload you created in step 3.

CREATE FUNCTION func_ResourceGovernor() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @workloadGroup AS sysname

IF (SUSER_NAME() = '<Your Domain>\ResourceGovernor') -- SUSER_NAME() is a system function

SET @workloadGroup = 'NewWorkLoadGroup'

RETURN @workloadGroup

END

5. Alter Resource Governor to configure it with classifier function.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = func_ResourceGovernor);

6. Issue a second reconfigure command to apply the changes

ALTER RESOURCE GOVERNOR RECONFIGURE;

Let's put all this together in one script.

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 10%. 
CREATE RESOURCE POOL CPUGovernor
   WITH
      (MAX_CPU_PERCENT = 10);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP NewWorkLoadGroup
USING CPUGovernor;
GO
-- Create a classification function.

CREATE FUNCTION dbo.func_ResourceGovernor() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workloadGroup AS sysname
      IF (SUSER_NAME() = '\ResourceGovernor')
          SET @workloadGroup = 'NewWorkLoadGroup'
    RETURN @workloadGroup 
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.func_ResourceGovernor);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I realize this is a tedious process, but it could be very useful when your server is experiencing a heavy workload and you cannot schedule your backups to run during low usage.

Thank you.