資源管理員工作負載管理案例
您可以運用各種方式來使用資源管理員，以便監視和管理 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
問：我想要進一步限制特定群組，防止它在累積所有要求時超過 50% 的 CPU 使用量。
答：由於前面的案例使用了預設集區，所以您必須建立新的資源集區。請遵循下列步驟來變更目前的資源管理員組態。在此為您提供一個組態範例。
建立新的資源集區並設定 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