使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)

项目
07/08/2024

默认情况下，使用压缩进行备份会显著增加 CPU 的使用，并且压缩进程所消耗的额外 CPU 会对并发操作产生不利影响。因此，你可能需要在会话中创建低优先级的压缩备份，当发生 CPU 争用时，此备份的 CPU 使用率受 Resource Governor 限制。本主题介绍了这样一种方案：通过将特定 SQL Server 用户的会话映射到限制 CPU 使用的资源调控器工作负载组来对这些会话进行分类。

重要

在具体的资源调控器方案中，会话分类可能基于用户名、应用程序名称或者可以区分连接的其他任何因素。有关详细信息，请参阅 Resource Governor Classifier Function 和 Resource Governor Workload Group。

本主题包含下面一组方案，将按顺序对它们进行介绍：

为低优先级操作设置登录名和用户
配置资源调控器以限制 CPU 使用
验证当前会话的分类 (Transact-SQL)
使用具有有限 CPU 的会话压缩备份

为低优先级操作设置登录名和用户

本主题中的方案需要低优先级的 SQL Server 登录帐户和用户。将使用用户名对运行于此登录中的会话进行分类，并将它们路由到限制 CPU 使用的资源调控器工作负荷组。

下面的过程介绍了出于此目的设置登录帐户和用户的步骤，之后给出了 Transact-SQL 示例“示例 A：设置登录帐户和用户 (Transact-SQL)”。

创建一个用于创建低优先级压缩备份的 SQL Server 登录帐户。

创建登录名
- 创建登录名
- CREATE LOGIN (Transact-SQL)
还可以选择对此登录名授予 VIEW SERVER STATE。
- GRANT 系统对象权限 (Transact-SQL)
有关详细信息，请参阅 GRANT 数据库主体权限 (Transact-SQL)。
为此登录创建一个 SQL Server 用户。

创建用户
- 创建数据库用户
- CREATE USER (Transact-SQL)
要使此登录名和用户的会话可以备份给定数据库，应将此用户添加到该数据库的 db_backupoperator 数据库角色。对该用户将备份的每个数据库执行此步骤。还可以选择将该用户添加到其他固定数据库角色。

将用户添加到固定数据库角色
- sp_addrolemember (Transact-SQL)
有关详细信息，请参阅 GRANT 数据库主体权限 (Transact-SQL)。

只有当选择为低优先级备份创建新的 SQL Server 登录帐户和用户时，下面的示例才有意义。或者，如果存在适当的登录名和用户，也可以使用现有的登录名和用户。

重要

以下示例使用示例登录名和用户名 domain_name\MAX_CPU。将此登录帐户和用户名替换为你计划在创建低优先级压缩备份时使用的 SQL Server 登录帐户和用户名。

此示例为 domain_name\MAX_CPU Windows 帐户创建一个登录名，然后对该登录名授予 VIEW SERVER STATE 权限。使用此权限，您可以验证登录名的会话的资源调控器分类。然后，本示例将为 domain_name\MAX_CPU 创建一个用户，并将它添加到 AdventureWorks2022 示例数据库的 db_backupoperator 固定数据库角色。此用户名将供资源调控器分类器函数使用。

-- 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 AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO

[顶部]

配置资源调控器以限制 CPU 使用

注意

确保资源调控器已启用。有关详细信息，请参阅启用 Resource Governor。

在本资源调控器方案中，配置过程包括以下基本步骤：

创建并配置一个资源调控器资源池，发生 CPU 争用时，该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽。
创建并配置一个使用该池的资源调控器工作负荷组。
创建一个 分类器函数，它是一个用户定义函数 (UDF)，其返回值供 Resource Governor 用来对会话进行分类，以便将它们路由到适当的工作负荷组。
将分类器函数注册到资源调控器。
将更改应用于资源调控器内存中配置。

注意

有关 Resource Governor 资源池、工作负荷组以及分类的信息，请参阅 Resource Governor。

这些步骤的 Transact-SQL 语句在“配置用于限制 CPU 使用的资源调控器”过程中进行了介绍，并在后面给出了此过程的 Transact-SQL 示例。

配置资源调控器 (SQL Server Management Studio)

配置用于限制 CPU 使用的资源调控器 (Transact-SQL)

发出 CREATE RESOURCE POOL 语句以创建资源池。此过程的示例使用以下语法：
```
CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
```
Value 是 1 到 100 之间的一个整数，它指示最大平均 CPU 带宽的百分比。实际适合的值取决于具体的环境。为便于说明，本主题中的示例使用 20% (MAX_CPU_PERCENT = 20)。
发出一个 CREATE WORKLOAD GROUP 语句，为你要调控其 CPU 使用的低优先级操作创建一个工作负荷组。此过程的示例使用以下语法：
```
CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];
```
发出一个 CREATE FUNCTION 语句，以创建一个分类器函数，此函数将上一步创建的工作负荷组映射到低优先级登录名的用户。此过程的示例使用以下语法：
```
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;
```
有关此 CREATE FUNCTION 语句各组成部分的信息，请参阅：
- DECLARE @local_variable (Transact-SQL)
- SUSER_SNAME (Transact-SQL)
  
  重要
  
  SUSER_NAME 仅仅是可用在分类器函数中的几个系统函数中的一个。有关详细信息，请参阅创建和测试分类器用户定义函数。
- SET @local_variable (Transact-SQL)。
发出一个 ALTER RESOURCE GOVERNOR 语句，将该分类器函数注册到资源调控器中。此过程的示例使用以下语法：
```
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
```
再发出一个 ALTER RESOURCE GOVERNOR 语句，将更改应用于资源调控器内存中配置，如下所示：
```
ALTER RESOURCE GOVERNOR RECONFIGURE;  
```

示例 B：配置资源调控器 (Transact-SQL)

下例在一个事务内执行下列步骤：

创建 pMAX_CPU_PERCENT_20 资源池。
创建 gMAX_CPU_PERCENT_20 工作负荷组。
创建 rgclassifier_MAX_CPU() 分类器函数，此函数使用在前一示例中创建的用户名。
将该分类器函数注册到资源调控器。

提交事务后，本示例将应用 ALTER WORKLOAD GROUP 或 ALTER RESOURCE POOL 语句中请求的配置更改。

重要

以下示例使用“示例 A：设置登录帐户和用户 (Transact-SQL)”中创建的示例 SQL Server 用户的用户名 domain_name\MAX_CPU。将此用户名替换为您计划在创建低优先级压缩备份时使用的登录名的用户名。

-- Configure Resource Governor.  
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

[顶部]

验证当前会话的分类 (Transact-SQL)

还可以选择使用在分类器函数中指定的用户身份登录，通过在对象资源管理器中发出以下 SELECT 语句来验证会话分类：

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

在“结果”窗格中，“名称”列应列出你在分类器函数中指定的工作负荷组名称的一个或多个会话。

注意

有关此 SELECT 语句调用的动态管理视图的信息，请参阅 sys.dm_exec_sessions (Transact-SQL) 和 sys.dm_resource_governor_workload_groups (Transact-SQL)。

[顶部]

使用具有有限 CPU 的会话压缩备份

要在限定了最大 CPU 的会话中创建压缩备份，应当以分类器函数中指定的用户身份登录。在备份命令中，指定 WITH COMPRESSION (Transact-SQL) 或选择“压缩备份”(SQL Server Management Studio)。若要创建压缩数据库备份，请参阅创建完整数据库备份 (SQL Server)。

示例 C：创建压缩备份 (Transact-SQL)

下面的 BACKUP 示例在一个采用新格式的备份文件 Z:\SQLServerBackups\AdvWorksData.bak 中创建 AdventureWorks2022 数据库的压缩完整备份。

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

[顶部]

另请参阅

创建和测试分类器用户定义函数
 资源调控器

通过

使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)

为低优先级操作设置登录名和用户

配置资源调控器以限制 CPU 使用

配置用于限制 CPU 使用的资源调控器 (Transact-SQL)

示例 B：配置资源调控器 (Transact-SQL)

验证当前会话的分类 (Transact-SQL)

使用具有有限 CPU 的会话压缩备份

示例 C：创建压缩备份 (Transact-SQL)

另请参阅

反馈

其他资源

通过

使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)

为低优先级操作设置登录名和用户

设置用于对会话进行分类的登录名和数据库用户

示例 A：设置登录名和用户 (Transact-SQL)

配置资源调控器以限制 CPU 使用

配置用于限制 CPU 使用的资源调控器 (Transact-SQL)

示例 B：配置资源调控器 (Transact-SQL)

验证当前会话的分类 (Transact-SQL)

使用具有有限 CPU 的会话压缩备份

示例 C：创建压缩备份 (Transact-SQL)

另请参阅

反馈

其他资源