使用資源管理員進行備份壓縮,以限制 CPU 使用率 (Transact-SQL)Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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 使用量的工作階段中,建立低優先權的壓縮備份。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 Function 以及 Resource 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. 如需詳細資訊,請參閱 啟用資源管理員For 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. 建立 「分類函數」 (Classifier Function),它是使用者定義的函數 (UDF),而且資源管理員會使用其傳回值來分類工作階段,以便將它們路由傳送至適當的工作負載群組。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.

注意

如需有關資源管理員資源集區、工作負載群組和分類的相關資訊,請參閱 資源管理員For 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% percent (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:設定 Resource Governor (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.

重要

下列範例會使用在「範例 A:設定登入和使用者 (Transact-SQL)」中所建立範例 SQL ServerSQL Server 使用者的使用者名稱:網域名稱 \MAX_CPUThe 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 Governor