共用方式為

  • 發行項

如何：使用資源管理員來限制備份壓縮的 CPU 使用量 (Transact-SQL)

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

重要注意事項重要事項

在給定的資源管理員狀況中，工作階段分類可能會以使用者名稱、應用程式名稱，或可區分連接的其他任何項目為基礎。如需詳細資訊，請參閱＜撰寫分類函數的考量＞和＜資源管理員工作負載管理案例＞。

這個主題包含下列狀況集，並依序展示：

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

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

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

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

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

這個主題中的狀況需要使用低優先權 SQL Server 登入和使用者。使用者名稱將用來分類在登入中執行的工作階段，並且將它們路由傳送至限制 CPU 使用量的資源管理員工作負載群組。

下列程序將描述針對此目的設定登入和使用者的步驟，後面接著 Transact-SQL 範例：「範例 A：設定登入和使用者 (Transact-SQL)」。

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

  1. 針對建立低優先權壓縮備份，建立 SQL Server 登入。 

    建立登入

  2. (選擇性) 將 VIEW SERVER STATE 授與這個登入。

    如需詳細資訊，請參閱＜固定伺服器角色的權限 (Database Engine)＞。

  3. 針對這個登入建立 SQL Server 使用者。

    建立使用者

  4. 若要讓這個登入和使用者的工作階段備份給定的資料庫，請將使用者加入至該資料庫的 db_backupoperator 資料庫角色。請針對這位使用者將備份的每個資料庫執行此步驟。(選擇性) 將使用者加入至其他固定資料庫角色。

    將使用者加入至固定資料庫角色

    如需詳細資訊，請參閱＜固定資料庫角色的權限 (Database Engine)＞。

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

只有當您選擇針對低優先權備份建立新的 SQL Server 登入和使用者時，下列範例才會相關。或者，您也可以使用現有的登入和使用者 (如果適當項目存在的話)。

重要注意事項重要事項

下列範例會使用範例登入和使用者名稱 domain_name\MAX_CPU。請將這些名稱取代成您打算在建立低優先權壓縮備份時使用的 SQL Server 登入和使用者名稱。

這個範例會針對 domain_name\MAX_CPU Windows 帳戶建立登入，然後將 VIEW SERVER STATE 權限授與此登入。這個權限可讓您確認登入工作階段的資源管理員分類。然後，此範例會針對 domain_name\MAX_CPU 建立使用者並將它加入至 AdventureWorks 範例資料庫的 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 AdventureWorks for this login
USE AdventureWorks;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[回到頂端]

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

[!附註]

請確定資源管理員已啟用。如需詳細資訊，請參閱＜如何：啟用或停用資源管理員 (SQL Server Management Studio)＞。

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

  1. 建立和設定資源管理員資源集區，以便在發生 CPU 爭用時，限制提供給資源集區中要求的最大平均 CPU 頻寬。

  2. 建立和設定使用這個集區的資源管理員工作負載群組。

  3. 建立「分類函數」(Classifier Function)，它是使用者定義的函數 (UDF)，而且資源管理員會使用其傳回值來分類工作階段，以便將它們路由傳送至適當的工作負載群組。

  4. 向資源管理員註冊此分類函數。

  5. 將這些變更套用至資源管理員的記憶體中組態。

[!附註]

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

這些步驟的 Transact-SQL 陳述式將在「設定資源管理員來限制 CPU 使用量」程序中說明，而且後面接著該程序的 Transact-SQL 範例。

設定資源管理員 (SQL Server Management Studio)

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

  1. 發出 CREATE RESOURCE POOL 陳述式來建立資源集區。這個程序的範例會使用下列語法：

    CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

    Value 是介於 1 和 100 之間的整數，表示最大平均 CPU 頻寬的百分比。適當的值會因您的環境而不同。為了方便說明，這個主題中的範例會使用 20% percent (MAX_CPU_PERCENT = 20)。

  2. 發出 CREATE WORKLOAD GROUP 陳述式，針對您想要管理其 CPU 使用量的低優先權作業建立工作負載群組。這個程序的範例會使用下列語法：

    CREATE WORKLOAD GROUP group_name USING pool_name;

  3. 發出 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 陳述式之元件的詳細資訊，請參閱：

  4. 發出 ALTER RESOURCE GOVERNOR 陳述式，向資源管理員註冊此分類函數。這個程序的範例會使用下列語法：

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

  5. 發出第二個 ALTER RESOURCE GOVERNOR 陳述式，將這些變更套用至資源管理員的記憶體中組態，如下所示：

    ALTER RESOURCE GOVERNOR RECONFIGURE;

範例 B：設定資源管理員 (Transact-SQL)

下列範例會在單一交易中執行下列步驟：

  1. 建立 pMAX_CPU_PERCENT_20 資源集區。

  2. 建立 gMAX_CPU_PERCENT_20 工作負載群組。

  3. 建立 rgclassifier_MAX_CPU() 分類函數，而且它會使用在上述範例中建立的使用者名稱。

  4. 向資源管理員註冊此分類函數。

認可交易之後，此範例就會套用在 ALTER WORKLOAD GROUP 或 ALTER RESOURCE POOL 陳述式中要求的組態變更。

重要注意事項重要事項

下列範例會使用在「範例 A：設定登入和使用者 (Transact-SQL)」中建立之範例 SQL Server 使用者的使用者名稱 domain_name\MAX_CPU。請將這個名稱取代成您打算在建立低優先權壓縮備份時使用的登入使用者名稱。

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

[回到頂端]

確認目前工作階段的分類 (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

在結果窗格中，name 資料行應該會針對您在分類函數中指定的工作負載群組名稱，列出一或多個工作階段。

[!附註]

如需有關這個 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)。

建立壓縮資料庫備份

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

下列 BACKUP 範例會在最近格式化的備份檔案 Z:\SQLServerBackups\AdvWorksData.bak 中建立 AdventureWorks 資料庫的完整壓縮備份。

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

[回到頂端]