使用資源管理員進行備份壓縮，以限制 CPU 使用率 (Transact-SQL)

發行項
03/07/2024

適用於：SQL Server

根據預設，使用壓縮來備份會大幅增加 CPU 使用量，而且壓縮程序所耗用的額外 CPU 可能會對並行作業造成不良的影響。因此，如果發生 CPU 競爭的情況，您可在 Resource Governor 限制 CPU 使用量的工作階段中，建立低優先順序的壓縮備份。這個主題所展示的案例，是將特定 SQL Server 使用者的工作階段，對應至在這類情況中限制 CPU 使用量的 Resource Governor 工作負載群組，藉以將這些工作階段分類。

重要

在給定的資源管理員狀況中，工作階段分類可能會以使用者名稱、應用程式名稱，或可區分連接的其他任何項目為基礎。如需相關資訊，請參閱 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 使用量

注意

請確定資源管理員已啟用。如需詳細資訊，請參閱啟用資源管理員。

在這個資源管理員狀況中，組態設定包含下列基本步驟：

建立和設定資源管理員資源集區，以便在發生 CPU 競爭時，限制提供給資源集區中要求的最大平均 CPU 頻寬。
建立和設定使用這個集區的資源管理員工作負載群組。
建立 「分類函數」 (Classifier Function)，它是使用者定義的函數 (UDF)，而且資源管理員會使用其傳回值來分類工作階段，以便將它們路由傳送至適當的工作負載群組。
向資源管理員註冊此分類函數。
將這些變更套用至資源管理員的記憶體中組態。

注意

如需有關資源管理員資源集區、工作負載群組和分類的相關資訊，請參閱資源管理員。

如需這些步驟的 Transact-SQL 陳述式說明，請參考「設定 Resource Governor 來限制 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% percent (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：設定 Resource Governor (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：設定 Resource Governor (Transact-SQL)

確認目前工作階段的分類 (Transact-SQL)

使用含有限制 CPU 的工作階段來壓縮備份

範例 C：建立壓縮備份 (Transact-SQL)

另請參閱

意見反應

意見反應

其他資源

使用資源管理員進行備份壓縮，以限制 CPU 使用率 (Transact-SQL)

針對低優先權作業設定登入和使用者

設定登入和資料庫使用者以便分類工作階段

範例 A：設定登入和使用者 (Transact-SQL)

設定資源管理員來限制 CPU 使用量

設定資源管理員來限制 CPU 使用量 (Transact-SQL)

範例 B：設定 Resource Governor (Transact-SQL)

確認目前工作階段的分類 (Transact-SQL)

使用含有限制 CPU 的工作階段來壓縮備份

範例 C：建立壓縮備份 (Transact-SQL)

另請參閱

意見反應

意見反應

其他資源