共用方式為

教學課程：資源管理員設定範例和最佳做法

  • 發行項

適用於：SQL ServerAzure SQL 受控實例

本文包含逐步解說範例，可協助您設定資源管理員，並驗證您的設定是否如預期般運作。 它會從簡單的範例開始，並進展到更複雜的範例。

本文也包含資源管理員 監視查詢 的範例，以及資源管理員 最佳做法清單。

所有範例都假設一開始會停用資源管理員並使用預設設定，而且沒有任何使用者定義的資源集區、工作負載群組和分類器函式存在。

注意

若要修改 Azure SQL 受控實例中的資源管理員組態，您必須位於主要復本上 master 資料庫的內容中。

修改預設群組

此範例會使用資源管理員來限制所有使用者查詢的記憶體授與大小上限。 這可藉由將 default 工作負載群組的 REQUEST_MAX_MEMORY_GRANT_PERCENT 設定從預設 25% 減少為 10%來完成。 這個範例不會使用 分類函數。 這表示登入處理不會受到影響，而且所有用戶會話都會繼續分類在 default 工作負載群組中。

如果查詢正在等候記憶體，您可能需要限制記憶體分配的大小，因為其他查詢保留太多記憶體。 如需詳細資訊，請參閱 針對 SQL Server中記憶體授與所造成的效能緩慢或記憶體不足問題進行疑難解答。

  1. 修改預設工作負載群組。

    ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10);

  2. 啟用資源管理器以使我們的配置生效。

    ALTER RESOURCE GOVERNOR RECONFIGURE;

  3. 驗證新的設定，包括記憶體授與的新大小上限。

    SELECT group_id,
       wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_percent,
       rp.max_memory_kb * wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_size_kb
FROM sys.resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id;

  4. 若要還原為初始設定，請執行下列腳本：

    ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER RESOURCE GOVERNOR DISABLE;

使用使用者定義的工作負載群組

此範例使用資源預算管理器，確保具有特定應用程式名稱的會話中的所有請求，其平行處理度不會高於四。 這可藉由將會話分類為工作負載群組，並將 MAX_DOP 設定設為 4 來完成。

如需設定平行處理原則最大程度的詳細資訊，請參閱 伺服器組態：平行處理原則的最大程度。

  1. 建立限制 DOP 的工作負載群組。 群組會使用 default 資源集區，因為我們只想限制特定應用程式的 DOP，但不會保留或限制 CPU、記憶體或 I/O 資源。

    CREATE WORKLOAD GROUP limit_dop
WITH (
     MAX_DOP = 4
     )
USING [default];

  2. 建立分類器函式。 函式會使用內建 APP_NAME（） 函式來判斷用戶端連接字串中指定的應用程式名稱。 如果應用程式名稱設定為 limited_dop_application，函式會傳回限制 DOP 的工作負載群組名稱。 否則，函式會傳回 default 作為工作負載組名。

    USE master;
GO

CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

IF APP_NAME() = N'limited_dop_application'
    SELECT @WorkloadGroupName = N'limit_dop';

RETURN @WorkloadGroupName;

END;
GO

  3. 修改資源管理員組態以讓組態生效，並啟用資源管理員。

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

  4. 查詢 sys.resource_governor_configuration 來驗證資源管理員已啟用，並使用我們建立的分類器函式。

    SELECT OBJECT_SCHEMA_NAME(classifier_function_id) AS classifier_schema_name,
       OBJECT_NAME(classifier_function_id) AS classifier_object_name,
       is_enabled
FROM sys.resource_governor_configuration;

    classifier_schema_name      classifier_object_name      is_enabled
----------------------      ----------------------      ----------
dbo                         rg_classifier               1

  5. 驗證具有特定應用程式名稱的會話已分類為 limit_dop 工作負載群組，而其他會話則會繼續分類在 default 工作負載群組中。 我們將使用包含 sys.dm_exec_sessions 和 sys.resource_governor_workload_groups 系統檢視的查詢，以傳回目前會話的應用程式名稱和工作負載組名。

    1. 在 SQL Server Management Studio（SSMS）中，選取主功能表上的 [檔案]，[新增]，[資料庫引擎查詢]。

    2. 在 [連接到資料庫引擎] 對話框中，指定您建立工作負載群組和分類器函式的相同資料庫引擎實例。 選擇 其他連線參數 索引標籤，然後輸入 App=limited_dop_application。 這可讓 SSMS 在連線到實例時，使用 limited_dop_application 作為應用程式名稱。

    3. 選取 [Connect]，以開啟新的連線。

    4. 在相同的查詢視窗中，執行下列查詢：

      SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.max_dop
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id = @@SPID;

      您應該會看到下列輸出，顯示工作已分類為 limit_dop 工作負載群組，並將 DOP 上限設定為四個：

      application_name            workload_group_name     max_dop
----------------            -------------------     -------
limited_dop_application     limit_dop               4

    5. 重複以上步驟，但不要在 [額外連接參數] 標籤的欄位中輸入任何內容。輸出會顯示變更，顯示預設的 SSMS 應用程式名稱以及 default 工作負載群組與預設最大 DOP 值 0。

      application_name                                    workload_group_name     max_dop
----------------                                    -------------------     -------
Microsoft SQL Server Management Studio - Query      default                 0

  6. 若要還原為此範例的初始設定，請使用 limit_dop 工作負載群組中斷所有會話的連線，然後執行下列 T-SQL 腳本。 文稿包含下列步驟：

    1. 停用資源管理器，以便卸除分類器函式。
    2. 移除工作負載群組。 這需要確保沒有任何會話正在使用此工作負載小組。
    3. 重新設定資源管理員以重載有效的設定，而不需要分類器函式和工作負載群組。 這會啟用資源管理員。
    4. 停用資源管理員以還原為初始設定。
    /* Disable resource governor so that the classifier function can be dropped. */
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;

/* Drop the workload group. This requires that no sessions are using this workload group. */
DROP WORKLOAD GROUP limit_dop;

/* Reconfigure resource governor to reload the effective configuration without the classifier function and the workload group. This enables resource governor. */
ALTER RESOURCE GOVERNOR RECONFIGURE;

/* Disable resource governor to revert to the initial configuration. */
ALTER RESOURCE GOVERNOR DISABLE;

使用多個資源集區和工作負載群組

此範例會使用資源管理員，根據一天中的時間，將訂單處理應用程式的會話分類為不同的工作負載群組和資源集區。 此設定會在尖峰處理時間將更多資源配置給應用程式，並在停機期間限制其資源。 此範例假設應用程式不會使用長時間執行的會話。

  1. 建立兩個資源集區，以進行尖峰時段和離班時間處理。

    • peak_hours_pool 集區保證至少透過 MIN_CPU_PERCENT提供 20% 的平均 CPU 頻寬，並且不會透過將 MAX_CPU_PERCENT 設定為 100來限制 CPU 頻寬。
    • off_hours_pool 集區不保留任何 CPU 帶寬，因為將 MIN_CPU_PERCENT 設定為 0，但通過將 MAX_CPU_PERCENT 設定為 50，當出現 CPU 爭用時，將 CPU 帶寬限制為 50%。
    CREATE RESOURCE POOL peak_hours_pool
WITH (
     MIN_CPU_PERCENT = 20,
     MAX_CPU_PERCENT = 100
     );

CREATE RESOURCE POOL off_hours_pool
WITH (
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 50
     );

    資源集區可以保留並限制系統資源，例如 CPU、記憶體和 I/O。 如需詳細資訊，請參閱 CREATE RESOURCE POOL。

  2. 分別建立兩個工作負載群組，每個資源集區各一個。

    • peak_hours_group 不會透過將 GROUP_MAX_REQUESTS 設定為 0的預設值來限制並行請求數量。
    • off_hours_group 藉由將 GROUP_MAX_REQUESTS 設定為 200，限制此群組中所有會話的並行要求數目。
    CREATE WORKLOAD GROUP peak_hours_group
WITH (
     GROUP_MAX_REQUESTS = 0
     )
USING peak_hours_pool;

CREATE WORKLOAD GROUP off_hours_group
WITH (
     GROUP_MAX_REQUESTS = 200
     )
USING off_hours_pool;

    工作負載群組會定義原則，例如要求數目上限、平行處理原則的最大程度，以及記憶體授與大小上限。 如需詳細資訊，請參閱 CREATE WORKLOAD GROUP。

  3. 建立並填入定義尖峰和離班時間間隔的數據表。

    • 數據表中的每個數據列都會定義間隔的開始和結束時間，以及間隔期間要使用的工作負載群組名稱。
    • 每個間隔的開始和結束時間都是內含的。
    • 數據表會在 master 資料庫中建立，以便可用於架構系結分類器函式。
    USE master;
GO

CREATE TABLE dbo.workload_interval
(
workload_group_name sysname NOT NULL,
start_time time(7) NOT NULL,
end_time time(7) NOT NULL,
CONSTRAINT pk_workload_interval PRIMARY KEY (start_time, workload_group_name),
CONSTRAINT ak_workload_interval_1 UNIQUE (end_time, workload_group_name),
CONSTRAINT ck_workload_interval_1 CHECK (start_time < end_time)
);
GO

INSERT INTO dbo.workload_interval
VALUES (N'off_hours_group', '00:00', '06:29:59.9999999'),
       (N'peak_hours_group', '06:30', '18:29:59.9999999'),
       (N'off_hours_group', '18:30', '23:59:59.9999999');

  4. 建立分類器函式。

    • 數據表中的數據預期會在一天的任何指定時間都有單一相符的數據列。 如果數據違反該規則，函式會傳回 default 作為工作負載組名。
    • 如果內建 APP_NAME（） 函式所傳回的應用程式名稱不是 order_processing以外的任何專案，下列範例函式也會傳回 default。
    USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM dbo.workload_interval
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

  5. 這是選擇性步驟。 您可以使用 數據表值建構函式，直接在分類函數中定義時間間隔，而不是在 master 資料庫中建立數據表。 當數據大小較小且分類函式準則不會經常變更時，這是建議的方法。 以下是一個使用表格值建構子的相同分類器範例，而非 master中的數據表。

    USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM (
     VALUES (CAST(N'off_hours_group' AS sysname),  CAST('00:00' AS time(7)), CAST('06:29:59.9999999' AS time(7))),
            (CAST(N'peak_hours_group' AS sysname), CAST('06:30' AS time(7)), CAST('18:29:59.9999999' AS time(7))),
            (CAST(N'off_hours_group' AS sysname),  CAST('18:30' AS time(7)), CAST('23:59:59.9999999'AS time(7)))
     ) AS wg (workload_group_name, start_time, end_time)
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

  6. 修改資源管理員組態以讓組態生效，並啟用資源管理員。

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

  7. 驗證資源管理員已啟用、使用指定的分類器函式，以及分類器函式如預期般運作，如先前範例中的類似 步驟。 這次，我們會在 [SSMS 連線] 對話方塊的 [其他連線參數] 索引標籤上輸入 App=order_processing，以符合分類器函式中的應用程式名稱。 執行下列查詢來判斷應用程式名稱、工作負載群組、資源集區，以及目前會話的CPU保留和限制：

    SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.group_max_requests,
       rp.name AS resource_pool_name,
       rp.min_cpu_percent,
       rp.max_cpu_percent
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
WHERE s.session_id = @@SPID;

    結果取決於一天中的時間。 例如，如果目前時間是 14：30，結果會顯示使用 peak_hours_group 和 peak_hours_pool：

    application_name    workload_group_name     group_max_requests      resource_pool_name      min_cpu_percent     max_cpu_percent
-----------------   --------------------    ------------------      -------------------     ---------------     ---------------
order_processing    peak_hours_group        0                       peak_hours_pool         20                  100

  8. 若要還原為此範例的初始設定，請使用 peak_hours_group 和 off_hours_group 工作負載群組來中斷所有會話的連線，然後執行下列 T-SQL 腳本。 文稿包含下列步驟：

    1. 停用資源控制器，以便卸除分類器函式。
    2. 卸除工作負載群組。 必須確保這些工作負載群組沒有被任何會話使用。
    3. 卸除工作負載群組後，再卸除資源集區。
    4. 重新設定資源管理員以重載有效的設定，而不需要分類器函式和用戶定義的工作負載群組和資源集區。 這會啟用資源管理員。
    5. 停用資源管理員以還原為初始設定。
    /* Disable resource governor so that the classifier function can be dropped. */
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;
DROP TABLE IF EXISTS dbo.workload_interval;

/* Drop the workload groups. This requires that no sessions are using these workload groups. */
DROP WORKLOAD GROUP peak_hours_group;
DROP WORKLOAD GROUP off_hours_group;

/* Once the workload groups are dropped, drop the resource pools. */
DROP RESOURCE POOL peak_hours_pool;
DROP RESOURCE POOL off_hours_pool;

/* Reconfigure resource governor to reload the effective configuration without the classifier function and user-defined workload groups and resource pools. This enables resource governor. */
ALTER RESOURCE GOVERNOR RECONFIGURE;

/* Disable resource governor to revert to the initial configuration. */
ALTER RESOURCE GOVERNOR DISABLE;

使用系統檢視監視資源管理員

本節中的範例查詢示範如何監視資源管理員運行時間統計數據和行為。

資源管理員統計數據是自上次伺服器重新啟動后累計的。 如果您需要從某個時間開始收集統計數據，您可以使用 ALTER RESOURCE GOVERNOR RESET STATISTICS 語句來重設統計數據。

資源集區運行時間統計數據

針對每個資源集區，資源管理員會追蹤 CPU 和記憶體使用率、記憶體不足事件、記憶體授與、I/O 和其他統計數據。 如需詳細資訊，請參閱 sys.dm_resource_governor_resource_pools。

下列查詢會傳回所有資源集區的可用統計資料子集：

SELECT rp.pool_id,
       rp.name AS resource_pool_name,
       wg.workload_group_count,
       rp.statistics_start_time,
       rp.total_cpu_usage_ms,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count,
       rp.active_memgrant_count,
       rp.total_memgrant_count,
       rp.total_memgrant_timeout_count,
       rp.read_io_completed_total,
       rp.write_io_completed_total,
       rp.read_bytes_total,
       rp.write_bytes_total,
       rp.read_io_stall_total_ms,
       rp.write_io_stall_total_ms
FROM sys.dm_resource_governor_resource_pools AS rp
OUTER APPLY (
            SELECT COUNT(1) AS workload_group_count
            FROM sys.dm_resource_governor_workload_groups AS wg
            WHERE wg.pool_id = rp.pool_id
            ) AS wg;

工作負載群組運行時間統計數據

針對每個工作負載群組，資源管理員會追蹤 CPU 時間、要求數目、封鎖的工作、鎖定等候時間、查詢優化和其他統計數據。 如需詳細資訊，請參閱 sys.resource_governor_workload_groups。

下列查詢會傳回所有工作負載群組的可用統計數據子集：

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.statistics_start_time,
       wg.total_request_count,
       wg.total_cpu_usage_ms,
       wg.blocked_task_count,
       wg.total_lock_wait_time_ms,
       wg.total_query_optimization_count,
       wg.max_request_grant_memory_kb,
       wg.active_parallel_thread_count,
       wg.effective_max_dop,
       wg.request_max_memory_grant_percent_numeric
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id

依工作負載群組和會話屬性匯總會話

下列查詢會傳回跨工作負載群組的會話分佈，並匯總每個工作負載群組的會話統計數據。

具有 preconnect 狀態的大量會話可能表示分類器執行速度緩慢。

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       s.program_name AS application_name,
       s.login_name,
       s.host_name,
       s.status,
       d.name AS database_name,
       MIN(s.login_time) AS first_login_time,
       MAX(s.login_time) AS last_login_time,
       MAX(s.last_request_start_time) AS last_request_start_time,
       COUNT(1) AS session_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON s.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         s.program_name,
         s.login_name,
         s.host_name,
         s.status,
         d.name;

依工作負載群組和要求屬性匯總要求

下列查詢會傳回跨工作負載群組的要求分佈，並匯總每個工作負載群組的要求統計數據：

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       r.command,
       r.status,
       d.name AS database_name,
       COUNT(1) AS request_count,
       MIN(r.start_time) AS first_request_start_time,
       MAX(r.start_time) AS last_request_start_time,
       SUM(CAST(r.total_elapsed_time AS bigint)) AS total_elapsed_time_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON r.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON r.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         r.command,
         r.status,
         d.name;

資源管理員最佳做法

  • 設定專用系統管理員連線 （DAC），並瞭解如何使用它。 如需詳細資訊，請參閱 資料庫管理員的診斷連線。 如果您的資源管控器配置出現故障，您可以使用 DAC 進行疑難解答，或 停用資源管控器。
  • 設定資源集區時，請小心指定 MIN_CPU_PERCENT、MIN_MEMORY_PERCENT和 MIN_IOPS_PER_VOLUME的大型值。 MIN 組態設定會保留資源集區的資源，並使其無法供其他資源集區使用，包括 default 集區。 如需詳細資訊，請參閱 建立資源集區。
  • 分類器函式會擴充登入處理時間。 請避免在分類器中執行複雜的邏輯和長時間執行或資源密集型查詢，特別是當查詢使用大型數據表時。 過於複雜的函式可能會導致登入延遲或連線逾時。
  • 如果您需要在分類器中使用數據表，而且數據表很小且大部分是靜態的，請考慮改用 數據表值建構函式，如本文稍早 範例所示。
  • 避免在分類器中使用經常修改的數據表。 這會增加封鎖可能會延遲登入並導致連線逾時的風險。 下列因應措施可以降低風險，但有缺點，包括不正確的分類風險：
    • 請考慮使用 NOLOCK 數據表提示，或對等的 READUNCOMMITTED 提示。 如需詳細資訊，請參閱 READUNCOMMITTED。
    • 請考慮在分類函式開頭使用 LOCK_TIMEOUT 設定，將它設定為低值，例如 1,000 毫秒。 如需詳細資訊，請參閱 SET LOCK_TIMEOUT。
  • 在資源管理員組態中參考分類函數時，您無法修改分類函數。 不過，您可以修改組態以使用不同的分類器函式。 如果您想要變更分類器，請考慮建立一組分類函式。 例如，您可以建立 dbo.rg_classifier_A()，並 dbo.rg_classifier_B()。 需要變更分類器邏輯時，請遵循下列步驟：
    1. 使用 ALTER FUNCTION 語句來對函式內進行變更，該函式 目前並未 被資源管理員組態使用。
    2. 使用 ALTER RESOURCE GOVERNOR 語句，讓修改過的分類器成為使用中，然後重新設定資源管理員。 例如：
      ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier_B);
ALTER RESOURCE GOVERNOR RECONFIGURE;
    3. 如果需要再次變更，請使用其他函式遵循相同的步驟（dbo.rg_classifier_A()）。
  • 資源管理員組態會儲存在 master 資料庫中。 請務必定期備份 master，並知道如何還原。 如需詳細資訊，請參閱 備份和還原：系統資料庫。 由於還原 master有限制，因此建議您另外儲存資源管理員設定腳本的複本。 如果需要重建 master 資料庫，您可以從腳本重新建立資源管理員組態。

相關內容