使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

默认情况下,使用压缩进行备份会显著增加 CPU 的使用,并且压缩进程所消耗的额外 CPU 会对并发操作产生不利影响。By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. 因此,你可能需要在会话中创建低优先级的压缩备份,当发生 CPU 争用时,此备份的 CPU 使用率受Resource Governor 限制。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. 本主题介绍了这样一种方案:通过将特定 SQL ServerSQL Server 用户的会话映射到限制 CPU 使用的资源调控器工作负荷组,来对这些会话进行分类。This topic presents a scenario that classifies the sessions of a particular SQL ServerSQL Server user by mapping them to a Resource Governor workload group that limits CPU usage in such cases.

重要

在具体的资源调控器方案中,会话分类可能基于用户名、应用程序名称或者可以区分连接的其他任何因素。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. 有关详细信息,请参阅 Resource Governor Classifier FunctionResource Governor Workload GroupFor 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. 配置资源调控器以限制 CPU 使用Configuring Resource Governor to Limit CPU Usage

  3. 验证当前会话的分类 (Transact-SQL)Verifying the Classification of the Current Session (Transact-SQL)

  4. 使用具有有限 CPU 的会话压缩备份Compressing Backups Using a Session with Limited CPU

为低优先级操作设置登录名和用户Setting Up a Login and User for Low-Priority Operations

本主题中的方案需要低优先级的 SQL ServerSQL Server 登录名和用户。The scenario in this topic requires a low-priority SQL ServerSQL Server login and user. 将使用用户名对运行于此登录中的会话进行分类,并将它们路由到限制 CPU 使用的资源调控器工作负荷组。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.

下面的过程介绍了出于此目的设置登录名和用户的步骤,之后给出了 Transact-SQLTransact-SQL 示例“示例 A:设置登录名和用户 (Transact-SQL)。”The following procedure describes the steps for setting up a login and user for this purpose, followed by a Transact-SQLTransact-SQL example, "Example A: Setting Up a Login and User (Transact-SQL)."

设置用于对会话进行分类的登录名和数据库用户To set up a login and database user for classifying sessions

  1. 创建一个用于创建低优先级压缩备份的 SQL ServerSQL Server 登录名。Create a SQL ServerSQL Server login for creating low-priority compressed backups.

    创建登录名To create a login

  2. 还可以选择对此登录名授予 VIEW SERVER STATE。Optionally, grant VIEW SERVER STATE to this login.

    有关详细信息,请参阅 GRANT 数据库主体权限 (Transact-SQL)For more information, see GRANT Database Principal Permissions (Transact-SQL).

  3. 为此登录名创建一个 SQL ServerSQL Server 用户。Create a SQL ServerSQL Server user for this login.

    创建用户To create a user

  4. 要使此登录名和用户的会话可以备份给定数据库,应将此用户添加到该数据库的 db_backupoperator 数据库角色。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

    有关详细信息,请参阅 GRANT 数据库主体权限 (Transact-SQL)For more information, see GRANT Database Principal Permissions (Transact-SQL).

示例 A:设置登录名和用户 (Transact-SQL)Example A: Setting Up a Login and User (Transact-SQL)

只有当您选择为低优先级备份创建新的 SQL ServerSQL Server 登录名和用户时,下面的示例才对您有意义。The following example is relevant only if you choose to create a new SQL ServerSQL Server login and user for low-priority backups. 或者,如果存在适当的登录名和用户,也可以使用现有的登录名和用户。Alternatively, you can use an existing login and user, if an appropriate one exists.

重要

以下示例使用示例登录名和用户名 domain_name\MAX_CPUThe following example uses a sample login and user name, domain_name\MAX_CPU. 将此登录名和用户名替换为您计划在创建低优先级压缩备份时使用的 SQL ServerSQL Server 登录名和用户名。Replace these with the names of the SQL ServerSQL Server login and user that you plan to use when creating your low-priority compressed backups.

此示例为 domain_name\MAX_CPU Windows 帐户创建一个登录名,然后对该登录名授予 VIEW SERVER STATE 权限。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. 本示例为 domain_name\MAX_CPU 创建一个用户,并将它添加到 AdventureWorks2012AdventureWorks2012 示例数据库的 db_backupoperator 固定数据库角色。The example then creates a user for domain_name\MAX_CPU and adds it to the db_backupoperator fixed database role for the AdventureWorks2012AdventureWorks2012 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]

配置资源调控器以限制 CPU 使用Configuring Resource Governor to Limit CPU Usage

备注

确保资源调控器已启用。Ensure that Resource Governor is enabled. 有关详细信息,请参阅 启用 Resource GovernorFor more information, see Enable Resource Governor.

在本资源调控器方案中,配置过程包括以下基本步骤:In this Resource Governor scenario, configuration comprises the following basic steps:

  1. 创建并配置一个资源调控器资源池,发生 CPU 争用时,该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽。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. 创建一个 分类器函数,它是一个用户定义函数 (UDF),其返回值供 Resource Governor 用来对会话进行分类,以便将它们路由到适当的工作负荷组。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.

备注

有关 Resource Governor 资源池、工作负荷组以及分类的信息,请参阅 Resource GovernorFor information about Resource Governor resource pools, workload groups, and classification, see Resource Governor.

这些步骤的 Transact-SQLTransact-SQL 语句在“配置用于限制 CPU 使用的资源调控器”这一步中介绍,该步骤后面给出了此步骤的 Transact-SQLTransact-SQL 示例。The Transact-SQLTransact-SQL statements for these steps are described in the procedure, "To configure Resource Governor for limiting CPU usage," which is followed by a Transact-SQLTransact-SQL example of the procedure.

配置资源调控器 (SQL Server Management Studio)To configure Resource Governor (SQL Server Management Studio)

配置用于限制 CPU 使用的资源调控器 (Transact-SQL)To configure Resource Governor for limiting CPU usage (Transact-SQL)

  1. 发出 CREATE RESOURCE POOL 语句以创建资源池。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 );CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

    Value 是 1 到 100 之间的一个整数,它指示最大平均 CPU 带宽的百分比。Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. 实际适合的值取决于具体的环境。The appropriate value depends on your environment. 为便于说明,本主题中的示例使用 20% (MAX_CPU_PERCENT = 20)。For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)

  2. 发出一个 CREATE WORKLOAD GROUP 语句,为你要调控其 CPU 使用的低优先级操作创建一个工作负荷组。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;CREATE WORKLOAD GROUP group_name USING pool_name;

  3. 发出一个 CREATE FUNCTION 语句,以创建一个分类器函数,此函数将上一步创建的工作负荷组映射到低优先级登录名的用户。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 sysnameCREATE FUNCTION [schema_name.]function_name() RETURNS sysname

    WITH SCHEMABINDINGWITH SCHEMABINDING

    ASAS

    BEGINBEGIN

    DECLARE @workload_group_name AS sysnameDECLARE @workload_group_name AS sysname

    IF (SUSER_NAME() = 'user_of_low_priority_login')IF (SUSER_NAME() = 'user_of_low_priority_login')

    SET @workload_group_name = 'workload_group_name'SET @workload_group_name = 'workload_group_name'

    RETURN @workload_group_nameRETURN @workload_group_name

    ENDEND

    有关该 CREATE FUNCTION 语句的各组成部分的信息,请参阅:For information about the components of this CREATE FUNCTION statement, see:

  4. 发出一个 ALTER RESOURCE GOVERNOR 语句,将该分类器函数注册到资源调控器中。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);ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

  5. 再发出一个 ALTER RESOURCE GOVERNOR 语句,将更改应用于资源调控器内存中配置,如下所示:Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:

    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    

示例 B:配置资源调控器 (Transact-SQL)Example B: Configuring Resource Governor (Transact-SQL)

下例在一个事务内执行下列步骤:The following example performs the following steps within a single transaction:

  1. 创建 pMAX_CPU_PERCENT_20 资源池。Creates the pMAX_CPU_PERCENT_20 resource pool.

  2. 创建 gMAX_CPU_PERCENT_20 工作负荷组。Creates the gMAX_CPU_PERCENT_20 workload group.

  3. 创建 rgclassifier_MAX_CPU() 分类器函数,此函数使用在前一示例中创建的用户名。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.

提交事务后,本示例将应用 ALTER WORKLOAD GROUP 或 ALTER RESOURCE POOL 语句中请求的配置更改。After committing the transaction, the example applies the configuration changes requested in the ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements.

重要

以下示例使用示例 SQL ServerSQL Server 用户的用户名,该用户是在“示例 A:设置登录名和用户 (Transact-SQL)” domain_name\MAX_CPU 中创建的 。The following example uses the user name of the sample SQL ServerSQL 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]

验证当前会话的分类 (Transact-SQL)Verifying the Classification of the Current Session (Transact-SQL)

还可以选择使用在分类器函数中指定的用户身份登录,通过在对象资源管理器中发出以下 SELECT 语句来验证会话分类: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.

备注

有关此 SELECT 语句调用的动态管理视图的信息,请参阅 sys.dm_exec_sessions (Transact-SQL)sys.dm_resource_governor_workload_groups (Transact-SQL)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]

使用具有有限 CPU 的会话压缩备份Compressing Backups Using a Session with Limited CPU

要在限定了最大 CPU 的会话中创建压缩备份,应当以分类器函数中指定的用户身份登录。To create a compressed backup in a session with a limited maximum CPU, log in as the user specified in your classifier function. 在备份命令中,指定 WITH COMPRESSION (Transact-SQLTransact-SQL) 或选择“压缩备份”(SQL Server Management StudioSQL Server Management Studio) 。In your backup command, either specify WITH COMPRESSION ( Transact-SQLTransact-SQL) or select Compress backup ( SQL Server Management StudioSQL Server Management Studio). 若要创建压缩数据库备份,请参阅创建完整数据库备份 (SQL Server)To create a compressed database backup, see Create a Full Database Backup (SQL Server).

示例 C:创建压缩备份 (Transact-SQL)Example C: Creating a Compressed Backup (Transact-SQL)

下面的 BACKUP 示例在一个采用新格式的备份文件 AdventureWorks2012AdventureWorks2012 中创建 Z:\SQLServerBackups\AdvWorksData.bak数据库的压缩完整备份。The following BACKUP example creates a compressed full backup of the AdventureWorks2012AdventureWorks2012 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 GovernorResource Governor