ALTER RESOURCE GOVERNOR (Transact-SQL)

文章
02/26/2024

這個陳述式是在 SQL Server 中用來執行下列 Resource Governor 動作：

當發出 CREATE|ALTER|DROP WORKLOAD GROUP 或 CREATE|ALTER|DROP RESOURCE POOL 或 CREATE|ALTER|DROP EXTERNAL RESOURCE POOL 陳述式時，套用指定的設定變更。
啟用或停用資源管理員。
設定傳入之要求的分類。
重設工作負載群組和資源集區統計資料。
設定每一個磁碟區的最大 I/O 作業量。

語法

ALTER RESOURCE GOVERNOR   
      { DISABLE | RECONFIGURE }  
    | WITH ( CLASSIFIER_FUNCTION = { schema_name.function_name | NULL } )  
    | RESET STATISTICS  
    | WITH ( MAX_OUTSTANDING_IO_PER_VOLUME = value )   
[ ; ]

引數

DISABLE
停用資源管理員。停用資源管理員會產生下列結果：

系統不會執行分類函數。
所有新的連接都會自動分類至預設群組中。
系統起始的要求會分類至內部工作負載群組中。
所有現有的工作負載群組和資源集區設定都會重設為預設值。在此情況下，如果到達限制，系統將不會引發任何事件。
一般的系統監視不會受到影響。
您可以變更組態，但這些變更要在資源管理員啟用之後才會生效。
重新啟動 SQL Server 時，Resource Governor 不會載入其設定，只會具有預設和內部群組與集區。

RECONFIGURE
當資源管理員未啟用時，RECONFIGURE 會啟用資源管理員。啟用資源管理員會產生下列結果：

系統會針對新的連接執行分類函數，以便將其工作負載指派給工作負載群組。
系統會接受並強制執行資源管理員組態中指定的資源限制。
啟用資源管理員之前存在的要求會受到停用資源管理員時所做的任何組態變更影響。

重要

若要讓任何組態變更生效，必須先發出 ALTER RESOURCE GOVERNOR RECONFIGURE。

CLASSIFIER_FUNCTION = { schema_name.function_name | NULL }
註冊 schema_name.function_name 所指定的分類函數。這個函數會將每個新的工作階段分類，然後將工作階段要求和查詢指派到工作負載群組。使用 NULL 時，會將新的工作階段自動指派到預設的工作負載群組。

RESET STATISTICS
針對所有工作負載群組與資源集區重設統計資料。如需詳細資訊，請參閱 sys.dm_resource_governor_workload_groups (Transact-SQL) 和 sys.dm_resource_governor_resource_pools (Transact-SQL)。

MAX_OUTSTANDING_IO_PER_VOLUME = value
適用於：SQL Server 2014 (12.x) 和更新版本。

設定每一個磁碟區已排入佇列的最大 I/O 作業量。這些 I/O 作業可以是任何規模的讀取或寫入。 MAX_OUTSTANDING_IO_PER_VOLUME 的最大值為 100。其值並非百分比。這項設定是設計來針對磁碟區的 IO 特性調整 IO 資源管理。此設定提供系統層級安全性檢查，即使其他集區的 MAX_IOPS_PER_VOLUME 設為無限制，仍可讓 SQL Server 達到資源集區的 IOPS 下限。如需有關 MAX_IOPS_PER_VOLUME 的詳細資訊，請參閱 CREATE RESOURCE POOL。

備註

ALTER RESOURCE GOVERNOR DISABLE、ALTER RESOURCE GOVERNOR RECONFIGURE 和 ALTER RESOURCE GOVERNOR RESET STATISTICS 無法用於使用者交易內。

RECONFIGURE 參數是 Resource Governor 語法的一部分，不應該與屬於個別 DDL 陳述式的 RECONFIGURE 混淆。

建議您在執行 DDL 陳述時前，先熟悉資源管理員的狀態。如需詳細資訊，請參閱 Resource Governor。

權限

需要 CONTROL SERVER 權限。

範例

A. 啟動資源管理員

首次安裝 SQL Server 時會停用 Resource Governor。下列範例會啟動資源管理員。陳述式執行後，資源管理員會執行，而且可能會使用預先定義的工作負載群組和資源集區。

ALTER RESOURCE GOVERNOR RECONFIGURE;

B. 指派新的工作階段到預設檔案群組

下列範例會從資源管理員組態移除任何現有的分類函數，以將所有新增的工作階段指派到預設的工作負載群組。當沒有任何函數被指定為分類函數時，所有新的工作階段都會指派到預設的工作負載群組。這項變更僅會套用至新的工作階段。現有的工作階段不會受到影響。

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);  
GO  
ALTER RESOURCE GOVERNOR RECONFIGURE;

C. 建立和註冊分類函數

下列範例會建立名為 dbo.rgclassifier_v1 的分類函數。這個函數會根據使用者名稱或應用程式名稱將每個新的工作階段分類，然後將工作階段要求和查詢指派到特定的工作負載群組。沒有對應到指定的使用者或應用程式名稱的工作階段會指派到預設的工作負載群組。接著會註冊此分類函數並套用組態變更。

-- Store the classifier function in the master database.  
USE master;  
GO  
SET ANSI_NULLS ON;  
GO  
SET QUOTED_IDENTIFIER ON;  
GO  
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname   
WITH SCHEMABINDING  
AS  
BEGIN  
-- Declare the variable to hold the value returned in sysname.  
    DECLARE @grp_name AS sysname  
-- If the user login is 'sa', map the connection to the groupAdmin  
-- workload group.   
    IF (SUSER_NAME() = 'sa')  
        SET @grp_name = 'groupAdmin'  
-- Use application information to map the connection to the groupAdhoc  
-- workload group.  
    ELSE IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')  
        OR (APP_NAME() LIKE '%QUERY ANALYZER%')  
            SET @grp_name = 'groupAdhoc'  
-- If the application is for reporting, map the connection to  
-- the groupReports workload group.  
    ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')  
        SET @grp_name = 'groupReports'  
-- If the connection does not map to any of the previous groups,  
-- put the connection into the default workload group.  
    ELSE  
        SET @grp_name = 'default'  
    RETURN @grp_name  
END;  
GO  
-- Register the classifier user-defined function and update the   
-- the in-memory configuration.  
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);  
GO  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

D. 重設統計資料

下列範例會重設所有的工作負載群組和資源集區統計資料。

ALTER RESOURCE GOVERNOR RESET STATISTICS;

E. 設定 MAX_OUTSTANDING_IO_PER_VOLUME 選項

下列範例會將 MAX_OUTSTANDING_IO_PER_VOLUME 選項設為 20。

ALTER RESOURCE GOVERNOR  
WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 20);

另請參閱

CREATE RESOURCE POOL (Transact-SQL)
ALTER RESOURCE POOL (Transact-SQL)
DROP RESOURCE POOL (Transact-SQL)
CREATE EXTERNAL RESOURCE POOL (Transact-SQL)
DROP EXTERNAL RESOURCE POOL (Transact-SQL)
ALTER EXTERNAL RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
ALTER WORKLOAD GROUP (Transact-SQL)
DROP WORKLOAD GROUP (Transact-SQL)
資源管理員
 sys.dm_resource_governor_workload_groups (Transact-SQL)
sys.dm_resource_governor_resource_pools (Transact-SQL)

分享方式：