ALTER RESOURCE GOVERNOR (Transact-SQL)ALTER RESOURCE GOVERNOR (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此语句用于在 SQL ServerSQL Server 中执行以下资源调控器操作:This statement is used to perform the following Resource Governor actions in SQL ServerSQL Server:

  • 应用在发出 CREATE|ALTER|DROP WORKLOAD GROUP 或 CREATE|ALTER|DROP RESOURCE POOL 或 CREATE|ALTER|DROP EXTERNAL RESOURCE POOL 语句时指定的配置更改。Apply the configuration changes specified when the CREATE|ALTER|DROP WORKLOAD GROUP or CREATE|ALTER|DROP RESOURCE POOL or CREATE|ALTER|DROP EXTERNAL RESOURCE POOL statements are issued.

  • 启用或禁用资源调控器。Enable or disable Resource Governor.

  • 配置传入请求的分类。Configure classification for incoming requests.

  • 重置工作负荷组和资源池统计信息。Reset workload group and resource pool statistics.

  • 设置每个磁盘卷的最大 I/O 操作数。Sets the maximum I/O operations per disk volume.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

DISABLEDISABLE
禁用资源调控器。Disables Resource Governor. 禁用资源调控器会产生下列结果:Disabling Resource Governor has the following results:

  • 不执行分类器函数。The classifier function is not executed.

  • 所有新连接被自动分类到默认组中。All new connections are automatically classified into the default group.

  • 系统发起的请求被分类到内部工作负荷组中。System-initiated requests are classified into the internal workload group.

  • 所有现有的工作负荷组和资源池设置被重置为其默认值。All existing workload group and resource pool settings are reset to their default values. 在这种情况下,到达限制时不触发任何事件。In this case, no events are fired when limits are reached.

  • 正常的系统监视不受影响。Normal system monitoring is not affected.

  • 可以进行配置更改,但是这些更改直到启用资源调控器之后才会生效。Configuration changes can be made, but the changes do not take effect until Resource Governor is enabled.

  • 在重新启动 SQL ServerSQL Server 时,资源调控器不会加载其配置,而是只具有默认的和内部的组和池。Upon restarting SQL ServerSQL Server, the Resource Governor will not load its configuration, but instead will have only the default and internal groups and pools.

RECONFIGURERECONFIGURE
当未启用资源调控器时,RECONFIGURE 可启用资源调控器。When the Resource Governor is not enabled, RECONFIGURE enables the Resource Governor. 启用资源调控器会产生下列结果:Enabling Resource Governor has the following results:

  • 为新连接执行分类器函数,以便可以将其工作负荷分配到工作负荷组。The classifier function is executed for new connections so that their workload can be assigned to workload groups.

  • 遵守并强制执行资源调控器配置中指定的资源限制。The resource limits that are specified in the Resource Governor configuration are honored and enforced.

  • 禁用资源调控器时所做的任何配置更改会影响在启用资源调控器之前就已存在的请求。Requests that existed before enabling Resource Governor are affected by any configuration changes that were made when Resource Governor was disabled.

当资源调控器正在运行时,RECONFIGURE 可应用执行 CREATE|ALTER|DROP WORKLOAD GROUP 或 CREATE|ALTER|DROP RESOURCE POOL 或 CREATE|ALTER|DROP EXTERNAL RESOURCE POOL 语句时请求的任何配置更改。When Resource Governor is running, RECONFIGURE applies any configuration changes requested when the CREATE|ALTER|DROP WORKLOAD GROUP or CREATE|ALTER|DROP RESOURCE POOL or CREATE|ALTER|DROP EXTERNAL RESOURCE POOL statements are executed.

重要

必须发出 ALTER RESOURCE GOVERNOR RECONFIGURE 才能使任何配置更改生效。ALTER RESOURCE GOVERNOR RECONFIGURE must be issued in order for any configuration changes to take effect.

CLASSIFIER_FUNCTION = { schema_name.function_name | NULL } CLASSIFIER_FUNCTION = { schema_name.function_name | NULL }
注册由 schema_name.function_name 指定的分类函数 。Registers the classification function specified by schema_name.function_name. 该函数将每个新会话进行分类并将会话请求和查询分配到工作负荷组。This function classifies every new session and assigns the session requests and queries to a workload group. 如果使用 NULL,新会话将自动分配到默认工作负荷组。When NULL is used, new sessions are automatically assigned to the default workload group.

RESET STATISTICSRESET STATISTICS
重置有关所有工作负荷组和资源池的统计信息。Resets statistics on all workload groups and resource pools. 有关详细信息,请参阅 sys.dm_resource_governor_workload_groups (Transact-SQL)sys.dm_resource_governor_resource_pools (Transact-SQL)For more information, see sys.dm_resource_governor_workload_groups (Transact-SQL) and sys.dm_resource_governor_resource_pools (Transact-SQL).

MAX_OUTSTANDING_IO_PER_VOLUME = value MAX_OUTSTANDING_IO_PER_VOLUME = value
适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

设置每个磁盘卷的最大排队 I/O 操作数。Sets the maximum queued I/O operations per disk volume. 这些 I/O 操作可以是任何大小的读取或写入。These I/O operations can be reads or writes of any size. MAX_OUTSTANDING_IO_PER_VOLUME 的最大值为 100。The maximum value for MAX_OUTSTANDING_IO_PER_VOLUME is 100. 它不是百分比。It is not a percent. 此设置用于将 IO 资源调控优化为磁盘卷的 IO 特性。This setting is designed to tune IO resource governance to the IO characteristics of a disk volume. 我们建议试验不同的值并考虑使用 IOMeter、DiskSpd 或 SQLIO(不推荐使用)等校准工具以找出存储子系统的最大值。We recommend that you experiment with different values and consider using a calibration tool such as IOMeter, DiskSpd, or SQLIO (deprecated) to identify the max value for your storage subsystem. 此设置进行系统级安全检查,它使 SQL Server 可满足资源池的最小 IOPS,即使其他池将 MAX_IOPS_PER_VOLUME 设置为无限也是如此。This setting provides a system-level safety check that allows SQL Server to meet the minimum IOPS for resource pools even if other pools have the MAX_IOPS_PER_VOLUME set to unlimited. 有关 MAX_IOPS_PER_VOLUME 的详细信息,请参阅 CREATE RESOURCE POOLFor more information about MAX_IOPS_PER_VOLUME, see CREATE RESOURCE POOL.

RemarksRemarks

ALTER RESOURCE GOVERNOR DISABLE、ALTER RESOURCE GOVERNOR RECONFIGURE 和 ALTER RESOURCE GOVERNOR RESET STATISTICS 无法在用户事务内部使用。ALTER RESOURCE GOVERNOR DISABLE, ALTER RESOURCE GOVERNOR RECONFIGURE, and ALTER RESOURCE GOVERNOR RESET STATISTICS cannot be used inside a user transaction.

RECONFIGURE 参数是资源调控器语法的一部分,不应与 RECONFIGURE 混淆,后者是一个单独的 DDL 语句。The RECONFIGURE parameter is part of the Resource Governor syntax and should not be confused with RECONFIGURE, which is a separate DDL statement.

建议您先了解资源调控器的状态,然后再执行 DDL 语句。We recommend being familiar with Resource Governor states before you execute DDL statements. 有关详细信息,请参阅 Resource GovernorFor more information, see Resource Governor.

权限Permissions

需要 CONTROL SERVER 权限。Requires CONTROL SERVER permission.

示例Examples

A.A. 启动资源调控器Starting the Resource Governor

初次安装 SQL ServerSQL Server 时,资源调控器处于禁用状态。When SQL ServerSQL Server is first installed Resource Governor is disabled. 以下示例启动资源调控器。The following example starts Resource Governor. 执行语句后,资源调控器将运行并且可使用预先定义的工作负荷组和资源池。After the statement executes, Resource Governor is running and can use the predefined workload groups and resource pools.

ALTER RESOURCE GOVERNOR RECONFIGURE;  

B.B. 将新会话分配到默认组Assigning new sessions to the default group

以下示例通过从资源调控器配置删除任何现有分类器函数,将所有新会话分配到默认工作负荷组。The following example assigns all new sessions to the default workload group by removing any existing classifier function from the Resource Governor configuration. 如果没有函数指定为分类器函数,所有新会话将分配到默认工作负荷组。When no function is designated as a classifier function, all new sessions are assigned to the default workload group. 此更改仅应用于新会话。This change applies to new sessions only. 现有会话不受影响。Existing sessions are not affected.

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

C.C. 创建和注册分类器函数Creating and registering a classifier function

以下示例创建一个名为 dbo.rgclassifier_v1 的分类器函数。The following example creates a classifier function named dbo.rgclassifier_v1. 该函数基于用户名或应用程序名称对每个新会话进行分类,并且将会话请求和查询分配到特定工作负荷组。The function classifies every new session based on either the user name or application name and assigns the session requests and queries to a specific workload group. 未映射到指定用户或应用程序名称的会话将分配到默认工作负荷组。Sessions that do not map to the specified user or application names are assigned to the default workload group. 然后,注册分类器函数并应用配置更改。The classifier function is then registered and the configuration change is applied.

-- 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.D. 重置统计信息Resetting Statistics

以下示例重置所有工作负荷组和资源池统计信息。The following example resets all workload group and resource pool statistics.

ALTER RESOURCE GOVERNOR RESET STATISTICS;  

E.E. 设置 MAX_OUTSTANDING_IO_PER_VOLUME 选项Setting the MAX_OUTSTANDING_IO_PER_VOLUME option

以下示例将 MAX_OUTSTANDING_IO_PER_VOLUME 选项设置为 20。The following example set the MAX_OUTSTANDING_IO_PER_VOLUME option to 20.

ALTER RESOURCE GOVERNOR  
WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 20);   

另请参阅See Also

CREATE RESOURCE POOL (Transact-SQL) CREATE RESOURCE POOL (Transact-SQL)
ALTER RESOURCE POOL (Transact-SQL) ALTER RESOURCE POOL (Transact-SQL)
DROP RESOURCE POOL (Transact-SQL) DROP RESOURCE POOL (Transact-SQL)
CREATE EXTERNAL RESOURCE POOL (Transact-SQL) CREATE EXTERNAL RESOURCE POOL (Transact-SQL)
DROP EXTERNAL RESOURCE POOL (Transact-SQL) DROP EXTERNAL RESOURCE POOL (Transact-SQL)
ALTER EXTERNAL RESOURCE POOL (Transact-SQL) ALTER EXTERNAL RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
ALTER WORKLOAD GROUP (Transact-SQL) ALTER WORKLOAD GROUP (Transact-SQL)
DROP WORKLOAD GROUP (Transact-SQL) DROP WORKLOAD GROUP (Transact-SQL)
资源调控器 Resource Governor
sys.dm_resource_governor_workload_groups (Transact-SQL) sys.dm_resource_governor_workload_groups (Transact-SQL)
sys.dm_resource_governor_resource_pools (Transact-SQL)sys.dm_resource_governor_resource_pools (Transact-SQL)