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.

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 Considerations for Writing a Classifier Function and Resource Governor Workload Management Scenarios.

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

   • How to: Create a SQL Server Login

   • CREATE LOGIN (Transact-SQL)

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

   • GRANT System Object Permissions (Transact-SQL)

   For more information, see Permissions of Fixed Server Roles (Database Engine).

3. Create a SQL Server user for this login.

   To create a user

   • How to: 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 Permissions of Fixed Database Roles (Database Engine).

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.

-- 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 AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
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 How to: Enable or Disable Resource Governor (SSMS).

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

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)

• How to: Configure Resource Governor Using a Template (SSMS)

• How to: Create a Resource Pool (SQL Server Management Studio)

• How to: Create a Workload Group (SQL Server Management Studio)

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 Considerations for Writing a Classifier 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

• How to: Create a Full Database Backup (Transact-SQL)

• How to: Back Up a Database (SQL Server Management Studio)

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

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

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

[Top]

See Also

Tasks

How to: Create a Classifier User-Defined Function (Transact-SQL)

Concepts

Introducing Resource Governor

Resource Governor Concepts

Resource Governor Workload Management Scenarios

Troubleshooting Resource Governor

Other Resources

Managing SQL Server Workloads with Resource Governor

Resource Governor How-to Topics