How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs. This topic presents a scenario that classifies the sessions of a particular SQL Server user by mapping them to a Resource Governor workload group that limits CPU usage in such cases.

The scenario in this topic requires a low-priority SQL Server login and user. The user name will be used to classify sessions running in the login and route them to a Resource Governor workload group that limits CPU usage.

The following procedure describes the steps for setting up a login and user for this purpose, followed by a Transact-SQL example, "Example A: Setting Up a Login and User (Transact-SQL)."

To enable sessions of this login and user to back up a given database, add the user to the db_backupoperator database role of that database. Do this for each database that this user will back up. Optionally, add the user to other fixed database roles.

Example A: Setting Up a Login and User (Transact-SQL)

The following example is relevant only if you choose to create a new SQL Server login and user for low-priority backups. Alternatively, you can use an existing login and user, if an appropriate one exists.

Important

The following example uses a sample login and user name, domain_name\MAX_CPU. Replace these with the names of the SQL Server login and user that you plan to use when creating your low-priority compressed backups.

This example creates a login for the domain_name\MAX_CPU Windows account and then grants VIEW SERVER STATE permission to the login. This permission enables you to verify the Resource Governor classification of sessions of the login. The example then creates a user for domain_name\MAX_CPU and adds it to the db_backupoperator fixed database role for the AdventureWorks2008R2 sample database. This user name will be used by the Resource Governor classifier function.

In this Resource Governor scenario, configuration comprises the following basic steps:

Create and configure a Resource Governor resource pool that limits the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs.

Create and configure a Resource Governor workload group that uses this pool.

Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

To configure Resource Governor for limiting CPU usage (Transact-SQL)

Issue a CREATE RESOURCE POOL statement to create a resource pool. The example for this procedure uses the following syntax:

CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. The appropriate value depends on your environment. For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)

Issue a CREATE WORKLOAD GROUP statement to create a workload group for low-priority operations whose CPU usage you want to govern. The example for this procedure uses the following syntax:

CREATE WORKLOAD GROUP group_name USING pool_name;

Issue a CREATE FUNCTION statement to create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login. The example for this procedure uses the following syntax:

CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @workload_group_name AS sysname

IF (SUSER_NAME() = 'user_of_low_priority_login')

SET @workload_group_name = 'workload_group_name'

RETURN @workload_group_name

END

For information about the components of this CREATE FUNCTION statement, see:

Example B: Configuring Resource Governor (Transact-SQL)

The following example performs the following steps within a single transaction:

Creates the pMAX_CPU_PERCENT_20 resource pool.

Creates the gMAX_CPU_PERCENT_20 workload group.

Creates the rgclassifier_MAX_CPU() classifier function, which uses the user name created in the preceding example.

Registers the classifier function with Resource Governor.

After committing the transaction, the example applies the configuration changes requested in the ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements.

Important

The following example uses the user name of the sample SQL Server user created in "Example A: Setting Up a Login and User (Transact-SQL)," domain_name\MAX_CPU. Replace this with the name of the user of the login that you plan to use for creating low-priority compressed backups.

To create a compressed backup in a session with a limited maximum CPU, log in as the user specified in your classifier function. In your backup command, either specify WITH COMPRESSION (Transact-SQL) or select Compress backup (SQL Server Management Studio).