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 byResource 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.

Important

In a given Resource Governor scenario, session classification might be based on a user name, an application name, or anything else that can differentiate a connection. For more information, see Resource Governor Classifier Function and Resource Governor Workload Group.

This topic contains the following set of scenarios, which are presented in sequence:

1. Setting Up a Login and User for Low-Priority Operations

2. Configuring Resource Governor to Limit CPU Usage

3. Verifying the Classification of the Current Session (Transact-SQL)

4. Compressing Backups Using a Session with Limited CPU

Setting Up a Login and User for Low-Priority Operations

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 set up a login and database user for classifying sessions

1. Create a SQL Server login for creating low-priority compressed backups.

   To create a login

   • Create a Login

   • CREATE LOGIN (Transact-SQL)

2. Optionally, grant VIEW SERVER STATE to this login.

   • GRANT System Object Permissions (Transact-SQL)

   For more information, see GRANT Database Principal Permissions (Transact-SQL).

3. Create a SQL Server user for this login.

   To create a user

   • Create a Database User

   • CREATE USER (Transact-SQL)

4. 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.

   To add a user to a fixed database role

   • sp_addrolemember (Transact-SQL)

   For more information, see GRANT Database Principal Permissions (Transact-SQL).

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 AdventureWorks2012 sample database. This user name will be used by the Resource Governor classifier function.

-- Create a SQL Server login for low-priority operations  
USE master;  
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;  
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];  
GO  
-- Create a SQL Server user in AdventureWorks2012 for this login  
USE AdventureWorks2012;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  
  

[Top]

Configuring Resource Governor to Limit CPU Usage

Note

Ensure that Resource Governor is enabled. For more information, see Enable Resource Governor.

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

1. 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.

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

3. 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.

4. Register the classifier function with Resource Governor.

5. Apply the changes to the Resource Governor in-memory configuration.

Note

For information about Resource Governor resource pools, workload groups, and classification, see Resource Governor.

The Transact-SQL statements for these steps are described in the procedure, "To configure Resource Governor for limiting CPU usage," which is followed by a Transact-SQL example of the procedure.

To configure Resource Governor (SQL Server Management Studio)

• Configure Resource Governor Using a Template

• Create a Resource Pool

• Create a Workload Group

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

1. 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.)

2. 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;

3. 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:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Important

     SUSER_NAME is just one of several system functions that can be used in a classifier function. For more information, see Create and Test a Classifier User-Defined Function.

   • SET @local_variable (Transact-SQL).

4. Issue an ALTER RESOURCE GOVERNOR statement to register the classifier function with Resource Governor. The example for this procedure uses the following syntax:

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

5. Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Example B: Configuring Resource Governor (Transact-SQL)

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

1. Creates the pMAX_CPU_PERCENT_20 resource pool.

2. Creates the gMAX_CPU_PERCENT_20 workload group.

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

4. 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.

-- Configure Resource Governor.  
BEGIN TRAN  
USE master;  
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%.   
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20  
   WITH  
      (MAX_CPU_PERCENT = 20);  
GO  
-- Create a workload group to use this pool.   
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20  
USING pMAX_CPU_PERCENT_20;  
GO  
-- Create a classification function.  
-- Note that any request that does not get classified goes into   
-- the 'Default' group.  
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname   
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS sysname  
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')  
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'  
    RETURN @workload_group_name  
END;  
GO  
  
-- Register the classifier function with Resource Governor.  
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);  
COMMIT TRAN;  
GO  
-- Start Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO  
  

[Top]

Verifying the Classification of the Current Session (Transact-SQL)

Optionally, log in as the user that you specified in your classifier function, and verify the session classification by issuing the following SELECT statement in Object Explorer:

USE master;  
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name   
FROM sys.dm_exec_sessions AS sess   
JOIN sys.dm_resource_governor_workload_groups AS grps   
    ON sess.group_id = grps.group_id  
WHERE session_id > 50;  
GO  

In the results pane, the name column should list one or more sessions for the workload-group name that you specified in your classifier function.

Note

For information about the dynamic management views called by this SELECT statement, see sys.dm_exec_sessions (Transact-SQL) and sys.dm_resource_governor_workload_groups (Transact-SQL).

[Top]

Compressing Backups Using a Session with Limited CPU

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). To create a compressed database backup, see Create a Full Database Backup (SQL Server).

Example C: Creating a Compressed Backup (Transact-SQL)

The following BACKUP example creates a compressed full backup of the AdventureWorks2012 database in a newly formatted backup file, Z:\SQLServerBackups\AdvWorksData.bak.

--Run backup statement in the gBackup session.  
BACKUP DATABASE AdventureWorks2012 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'   
WITH   
   FORMAT,   
   MEDIADESCRIPTION='AdventureWorks2012 Compressed Data Backups'  
   DESCRIPTION='First database backup on AdventureWorks2012 Compressed Data Backups media set'  
   COMPRESSION;  
GO  

[Top]

See Also

Create and Test a Classifier User-Defined Function
Resource Governor