Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
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.
Setting Up a Login and User for Low-Priority Operations
Configuring Resource Governor to Limit CPU Usage
Verifying the Classification of the Current Session (Transact-SQL)
Compressing Backups Using a Session with Limited CPU
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)."
Create a SQL Server login for creating low-priority compressed backups.
To create a login
Optionally, grant VIEW SERVER STATE to this login.
For more information, see GRANT Database Principal Permissions (Transact-SQL).
Create a SQL Server user for this login.
To create a user
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
For more information, see GRANT Database Principal Permissions (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.
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
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.
Register the classifier function with Resource Governor.
Apply the changes to the Resource Governor in-memory configuration.
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)
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
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = 'user_of_low_priority_login')
SET @workload_group_name = 'workload_group_name'
For information about the components of this CREATE FUNCTION statement, see:
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.
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);
Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:
ALTER RESOURCE GOVERNOR RECONFIGURE;
The following example performs the following steps within a single transaction:
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.
-- 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
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.
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).
BACKUP example creates a compressed full backup of the AdventureWorks2012 database in a newly formatted backup file,
--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