ALTER RESOURCE GOVERNOR (Transact-SQL)
This statement is used to perform the following Resource Govenor actions in SQL Server:
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.
Sets the maximum I/O operations per disk volume.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
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 resource pool statistics.
ALTER RESOURCE GOVERNOR RESET STATISTICS;
E. Setting the MAX_OUTSTANDING_IO_PER_VOLUME option
The following example set the MAX_OUTSTANDING_IO_PER_VOLUME option to 20.
ALTER RESOURCE GOVERNOR WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 20);