ALTER RESOURCE GOVERNOR (Transact-SQL)

مقالة
05/16/2013

This command is used to perform the following actions:

Apply the configuration changes specified when the CREATE|ALTER|DROP WORKLOAD GROUP or CREATE|ALTER|DROP RESOURCE POOL statements are issued.
Enable or disable Resource Governor.
Configure classification for incoming requests.
Reset workload group and resource pool statistics.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

Term	Definition
DISABLE \| RECONFIGURE	DISABLE 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. Upon restarting SQL Server, the Resource Governor will not load its configuration, but instead will have only the default and internal groups and pools. 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. When Resource Governor is running, RECONFIGURE applies any configuration changes requested when the CREATE\|ALTER\|DROP WORKLOAD GROUP or CREATE\|ALTER\|DROP RESOURCE POOL statements are executed. Important ALTER RESOURCE GOVERNOR RECONFIGURE must be issued in order for any configuration changes to take effect.
CLASSIFIER_FUNCTION = { schema_name.function_name \| NULL }	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. When NULL is used, new sessions are automatically assigned to the default workload group.
RESET STATISTICS	Resets statistics on all workload groups and resource pools. For more information, see sys.dm_resource_governor_workload_groups (Transact-SQL) and sys.dm_resource_governor_resource_pools (Transact-SQL).

Remarks

ALTER RESOURCE GOVERNOR DISABLE, ALTER RESOURCE GOVERNOR RECONFIGURE, and ALTER RESOURCE GOVERNOR RESET STATISTICS cannot be used inside a user transaction.

The RECONFIGURE parameter is part of the Resource Governor syntax and should not be confused with RECONFIGURE, which is a separate DDL statement.

We recommend being familiar with Resource Governor states before you execute DDL statements. For more information, see Resource Governor.

Permissions

Requires CONTROL SERVER permission.

Examples

A. Starting the Resource Governor

When SQL 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. 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. Creating and registering a classifier function

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. Resetting Statistics

The following example resets all workload group and pool statistics.

ALTER RESOURCE GOVERNOR RESET STATISTICS;

مشاركة عبر