資源調控器工作負荷管理方案

可以按各種方式使用資源調控器來監視和管理 SQL Server 系統上的工作負荷。本主題概述瞭如何配置資源調控器並舉例說明如何使用資源調控器。所提供的方案包括用於建立和更改工作負荷組和資源池的 Transact-SQL 程式碼示例。

 配置資源調控器
安裝 SQL Server 2008 後，即可使用資源調控器，但需要先啟用。內部和預設工作負荷組及其相應資源池已存在。

若要建立和使用自己的資源池和工作負荷組，必須完成以下步驟：

建立一個具有指定限制的資源池。

建立一個具有指定限制和策略的工作負荷組，並標識該工作負荷組所屬的資源池。

定義並啟用一個要用於傳入請求的分類器函式。

完成上述步驟後，便可看到活動的資源調控器配置和已分類的所有活動請求的狀態。

確定資源池和工作負荷組要求
問題是要確定希望在 SQL Server 環境中使用的工作負荷組和資源池配置。為此，必須瞭解應用程式目前對 SQL Server 資源的佔用情況。

解決方案是建立一個位於預設資源池中的工作負荷組。先執行應用程式若干天，然後使用收集的資訊來回答以下問題。

CPU 要求是什麼？
確定 CPU 頻寬估計值，方法是通過每個請求的平均 CPU 總使用率乘以每秒平均請求數。

可以使用最大、平均和標準 CPU 使用率偏差來確定是否需要最大 CPU 限制。

併發請求數是多少？
使用最大和平均併發請求數統計資訊來確定最小併發因子。

所需的總記憶體是多少？
藉助所用的最大和平均記憶體總量來確定記憶體要求。

單個查詢需要多少記憶體？
使用每個查詢的最大和平均記憶體統計資訊來確定所需的記憶體量。

是否存在查詢因記憶體不足而未執行？
使用平均記憶體佇列等待時間統計資訊來確定是否有任何查詢因記憶體可用性而被阻止。

 方案
我們提供了七個方案作為示例。方案 1 到 5 是累積性的；每個方案都基於前面的方案。方案 6 與前面的方案無關，而方案 7 基於方案 6。

方案 1
問：我剛剛安裝了 SQL Server 新版本，希望使用資源調控器。如何在我的環境下使用？

答：請考慮使用資源調控器監視工作負荷的資源消耗情況。請執行下列步驟來設定資源調控器環境。下面是一個配置示例。

針對您的工作負荷建立工作負荷組。

建立一個分類函式。

使用資源調控器註冊該分類函式。

啟用資源調控器。

監視資源調控器效能計數器並查詢將返回有關工作負荷組的資源使用情況資訊的 DMV。

示例
注意：
下面的配置沒有指定工作負荷組應使用的資源池。預設情況下，工作負荷組將使用預設池。
 
BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs.
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 2
問：根據方案 1 的監視結果，我希望隨時檢視特殊組 (GroupAdhoc) 中查詢的執行時間超過 30 秒的事件。

答：按照下列步驟更改當前的資源調控器配置。下面是一個配置示例。

為特殊組設定 CPU 使用率限制。

監視 SQL 跟蹤事件（資源調控器管理類事件）。

對事件執行操作。例如，忽略事件、傳送電子郵件、傳送頁面或對請求執行 KILL 命令。

示例
-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 3
問：我希望進一步限制特殊組，以便在累積所有請求時該組的 CPU 使用率不超過 50%。

答：由於前面的方案使用的是預設池，因此必須建立新的資源池。按照下列步驟更改當前的資源調控器配置。下面是一個配置示例。

建立一個新的資源池並配置 CPU 限制。

配置特殊工作負荷組用於新的資源池。

示例
BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool.
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 4
問：現在我想保證管理組始終有執行診斷查詢的資源，但是這些查詢佔用的伺服器記憶體資源不能超過 10%。

答：為了執行此操作，必須建立新的資源池。按照下列步驟更改當前的資源調控器配置。下面是一個配置示例。

建立一個新的資源池並設定資源限制。

配置管理工作負荷組用於新的池。

示例
BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 5
問：由於我的報表當前位於預設資源池中，但使用單獨的工作負荷組 (GroupReports)，因此，我希望確保預設資源組中的所有內容的資源使用優先順序都比報表低。

答：若要實現此目的，必須在預設組中配置 IMPORTANCE 設定。

注意：
您可以更改預設組的設定，但不能更改預設池。如果您認為需要更改預設池，則充分說明了您需要建立新的資源池。
 

按照下列步驟更改當前的資源調控器配置。下面是一個配置示例。

更改預設組的設定。

示例
-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default]
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 6
問：我想要建立一個使用現有資源池的新工作負荷組。然後我希望更改分類器函式，以便將該組移到新的資源池。

答：使用以下步驟來設定此新的資源調控器環境。下面是一個配置示例。

使用預設設定建立新的資源池。

建立一個位於現有池中的新工作負荷組。

建立並註冊一個用於處理請求的新分類器函式。

示例
 複製程式碼
BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 ()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO方案 7
問：我決定不再需要工作負荷組（在方案 6 中建立），以便刪除工作負荷組及其所在的資源池。

答：使用以下步驟可以更改資源調控器環境。下面是一個配置示例。

建立並註冊新的分類器函式，將請求分類到其餘的工作負荷組。

刪除工作負荷組。

刪除資源池。

應用配置更改。

示例
 複製程式碼
BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 ()
RETURNS sysnameE
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515842/，如需轉載，請註明出處，否則將追究法律責任。