Tutorial: Resource governor configuration examples and best practices

Artigo
01/03/2025

Applies to: SQL Server Azure SQL Managed Instance

This article contains walkthrough examples to help you configure resource governor and validate that your configuration works as expected. It starts with a simple example and progresses to the more complex ones.

The article also includes examples of resource governor monitoring queries and a list of resource governor best practices.

All examples assume that initially, resource governor is disabled and uses default settings, and that no user-defined resource pools, workload groups, and classifier functions exist.

Note

For Azure SQL Managed Instance, you must be in the context of the master database to modify resource governor configuration.

Modify the default group

This example uses resource governor to limit the maximum size of a memory grant for all user queries. This is done by reducing the REQUEST_MAX_MEMORY_GRANT_PERCENT setting for the default workload group from the default 25% to 10%. The example does not use a classifier function. This means that login processing is not affected and all user sessions continue to be classified in the default workload group.

You might need to limit the size of memory grants if queries are waiting for memory because other queries reserved too much memory. For more information, see Troubleshoot slow performance or low memory issues caused by memory grants in SQL Server.

Modify the default workload group.

ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10);

Enable resource governor to make our configuration effective.
```
ALTER RESOURCE GOVERNOR RECONFIGURE;
```

Validate the new setting, including the new maximum size of a memory grant.

SELECT group_id,
       wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_percent,
       rp.max_memory_kb * wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_size_kb
FROM sys.resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id;

To revert to the initial configuration, execute the following script:

ALTER RESOURCE GOVERNOR DISABLE;
ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);

Use a user-defined workload group

This example uses resource governor to ensure that all requests on sessions with a specific application name don't execute with the degree of parallelism (DOP) higher than four. This is done by classifying sessions into a workload group with the MAX_DOP setting set to 4.

For more information about configuring the maximum degree of parallelism, see Server configuration: max degree of parallelism.

Create a workload group that limits DOP. The group uses the default resource pool because we only want to limit DOP for a specific application, but not reserve or limit CPU, memory, or I/O resources.
```
CREATE WORKLOAD GROUP limit_dop
WITH (
     MAX_DOP = 4
     )
USING [default];
```
Create the classifier function. The function uses the built-in APP_NAME() function to determine the application name specified in the client connection string. If the application name is set to limited_dop_application, the function returns the name of the workload group that limits DOP. Otherwise, the function returns default as the workload group name.
```
USE master;
GO

CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

IF APP_NAME() = N'limited_dop_application'
    SELECT @WorkloadGroupName = N'limit_dop';

RETURN @WorkloadGroupName;

END;
GO
```

Modify resource governor configuration to make our configuration effective, and enable resource governor.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Query sys.resource_governor_configuration to validate that resource governor is enabled and is using the classifier function we created.

SELECT OBJECT_SCHEMA_NAME(classifier_function_id) AS classifier_schema_name,
       OBJECT_NAME(classifier_function_id) AS classifier_object_name,
       is_enabled
FROM sys.resource_governor_configuration;

classifier_schema_name      classifier_object_name      is_enabled
----------------------      ----------------------      ----------
dbo                         rg_classifier               1

Validate that sessions with a specific application name are classified into the limit_dop workload group, while other sessions continue to be classified in the default workload group. We'll use a query that uses sys.dm_exec_sessions and sys.resource_governor_workload_groups system views to return the application name and workload group name for the current session.
1. In SQL Server Management Studio (SSMS), select File on the main menu, New, Database Engine Query.
2. In the Connect to Database Engine dialog, specify the same Database Engine instance where you created the workload group and the classifier function. Select the Additional Connection Parameters tab, and enter App=limited_dop_application. This makes SSMS use limited_dop_application as the application name when connecting to the instance.
3. Select Connect to open a new connection.
4. In the same query window, execute the following query:
```
SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.max_dop
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id = @@SPID;
```
  You should see the following output, showing that the session was classified into the limit_dop workload group with the maximum DOP set to four:
```
application_name            workload_group_name     max_dop
----------------            -------------------     -------
limited_dop_application     limit_dop               4
```
5. Repeat the above steps, but don't enter anything in the box on the Additional Connection Parameters tab. The output changes, showing the default SSMS application name and the default workload group with the default 0 value for maximum DOP.
```
application_name                                    workload_group_name     max_dop
----------------                                    -------------------     -------
Microsoft SQL Server Management Studio - Query      default                 0
```

To revert to the initial configuration of this sample, execute the following T-SQL script:

ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;
DROP WORKLOAD GROUP limit_dop;

Use multiple resource pools and workload groups

This example uses resource governor to classify sessions from an order processing application into different workload groups and resource pools depending on the time of day. This configuration allocates more resources to the application during peak processing times, and limits its resources during off hours. The example assumes that the application doesn't use long-running sessions.

Create two resource pools for peak hours and off hours processing.
- The peak_hours_pool pool guarantees (reserves) a minimum of 20% of average CPU bandwidth via MIN_CPU_PERCENT, and doesn't limit CPU bandwidth by setting MAX_CPU_PERCENT to 100.
- The off_hours_pool pool doesn't reserve any CPU bandwidth by setting MIN_CPU_PERCENT to 0, but limits CPU bandwidth to 50% when CPU contention is present by setting MAX_CPU_PERCENT to 50.
```
CREATE RESOURCE POOL peak_hours_pool
WITH (
     MIN_CPU_PERCENT = 20,
     MAX_CPU_PERCENT = 100
     );

CREATE RESOURCE POOL off_hours_pool
WITH (
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 50
     );
```
Resource pools can reserve and limit system resources such as CPU, memory, and I/O. For more information, see CREATE RESOURCE POOL.
Create two workload groups, one for each resource pool respectively.
- The peak_hours_group doesn't limit the number of concurrent requests by setting GROUP_MAX_REQUESTS to the default value of 0.
- The off_hours_group limits the number of concurrent requests across all sessions classified into this group, by setting GROUP_MAX_REQUESTS to 200.
```
CREATE WORKLOAD GROUP peak_hours_group
WITH (
     GROUP_MAX_REQUESTS = 0
     )
USING peak_hours_pool;

CREATE WORKLOAD GROUP off_hours_group
WITH (
     GROUP_MAX_REQUESTS = 200
     )
USING off_hours_pool;
```
Workload groups define policies such as the maximum number of requests, the maximum degree of parallelism, and the maximum memory grant size. For more information, see CREATE WORKLOAD GROUP.

Create and populate a table that defines the peak and off hours time intervals.

Each row in the table defines the start and end time of the interval, and the name of the workload group to use during the interval.
The start and end time of each interval is inclusive.
The table is created in the master database so that it can be used in a schema-bound classifier function.

USE master;
GO

CREATE TABLE dbo.workload_interval
(
workload_group_name sysname NOT NULL,
start_time time(7) NOT NULL,
end_time time(7) NOT NULL,
CONSTRAINT pk_workload_interval PRIMARY KEY (start_time, workload_group_name),
CONSTRAINT ak_workload_interval_1 UNIQUE (end_time, workload_group_name),
CONSTRAINT ck_workload_interval_1 CHECK (start_time < end_time)
);
GO

INSERT INTO dbo.workload_interval
VALUES (N'off_hours_group', '00:00', '06:29:59.9999999'),
       (N'peak_hours_group', '06:30', '18:29:59.9999999'),
       (N'off_hours_group', '18:30', '23:59:59.9999999');

Create the classifier function.

The data in the table is expected to have a single matching row for any given time of day. If the data violates that rule, the function returns default as the workload group name.
The following example function also returns default if the application name returned by the built-in APP_NAME() function is anything other than order_processing.

USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM dbo.workload_interval
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

This is an optional step. Instead of creating a table in the master database, you can use a table-valued constructor to define the time intervals directly in the classifier function. This is the recommended approach when data size is small and the classifier function criteria isn't changed frequently. Here is an example of the same classifier that uses a table-valued constructor instead of a table in master.

USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM (
     VALUES (CAST(N'off_hours_group' AS sysname),  CAST('00:00' AS time(7)), CAST('06:29:59.9999999' AS time(7))),
            (CAST(N'peak_hours_group' AS sysname), CAST('06:30' AS time(7)), CAST('18:29:59.9999999' AS time(7))),
            (CAST(N'off_hours_group' AS sysname),  CAST('18:30' AS time(7)), CAST('23:59:59.9999999'AS time(7)))
     ) AS wg (workload_group_name, start_time, end_time)
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

Modify resource governor configuration to make our configuration effective, and enable resource governor.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Validate that resource governor is enabled, is using the specified classifier function, and that the classifier function works as expected using similar steps as in the previous example. This time, we enter App=order_processing on the Additional Connection Parameters tab in the SSMS connect dialog to match the application name in the classifier function. Execute the following query to determine the application name, workload group, resource pool, and the CPU reservation and limit for the current session:

SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.group_max_requests,
       rp.name AS resource_pool_name,
       rp.min_cpu_percent,
       rp.max_cpu_percent
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
WHERE s.session_id = @@SPID;

The results depend on the time of day. For example, if the current time is 14:30, the result shows that peak_hours_group and peak_hours_pool are used:

application_name    workload_group_name     group_max_requests      resource_pool_name      min_cpu_percent     max_cpu_percent
-----------------   --------------------    ------------------      -------------------     ---------------     ---------------
order_processing    peak_hours_group        0                       peak_hours_pool         20                  100

To revert to the initial configuration, execute the following T-SQL script:

ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;
DROP TABLE IF EXISTS dbo.workload_interval;
DROP WORKLOAD GROUP peak_hours_group;
DROP WORKLOAD GROUP off_hours_group;
DROP RESOURCE POOL peak_hours_pool;
DROP RESOURCE POOL off_hours_pool;

Monitor resource governor using system views

Example queries in this section show how you can monitor resource governor runtime statistics and behavior.

Resource governor statistics are cumulative since the last server restart. If you need to collect statistics starting from a certain time, you can reset statistics using the ALTER RESOURCE GOVERNOR RESET STATISTICS statement.

Resource pool runtime statistics

For each resource pool, resource governor tracks CPU and memory utilization, out-of-memory events, memory grants, I/O, and other statistics. For more information, see sys.dm_resource_governor_resource_pools.

The following query returns a subset of available statistics for all resource pools:

SELECT rp.pool_id,
       rp.name AS resource_pool_name,
       wg.workload_group_count,
       rp.statistics_start_time,
       rp.total_cpu_usage_ms,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count,
       rp.active_memgrant_count,
       rp.total_memgrant_count,
       rp.total_memgrant_timeout_count,
       rp.read_io_completed_total,
       rp.write_io_completed_total,
       rp.read_bytes_total,
       rp.write_bytes_total,
       rp.read_io_stall_total_ms,
       rp.write_io_stall_total_ms
FROM sys.dm_resource_governor_resource_pools AS rp
OUTER APPLY (
            SELECT COUNT(1) AS workload_group_count
            FROM sys.dm_resource_governor_workload_groups AS wg
            WHERE wg.pool_id = rp.pool_id
            ) AS wg;

Workload group runtime statistics

For each workload group, resource governor tracks CPU time, the number of requests, blocked tasks, lock wait time, query optimizations, and other statistics. For more information, see sys.resource_governor_workload_groups.

The following query returns a subset of available statistics for all workload groups:

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.statistics_start_time,
       wg.total_request_count,
       wg.total_cpu_usage_ms,
       wg.blocked_task_count,
       wg.total_lock_wait_time_ms,
       wg.total_query_optimization_count,
       wg.max_request_grant_memory_kb,
       wg.active_parallel_thread_count,
       wg.effective_max_dop,
       wg.request_max_memory_grant_percent_numeric
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id

Aggregate sessions by workload group and session attributes

The following query returns a distribution of sessions across workload groups and aggregate session statistics for each workload group.

A high number of sessions with the preconnect status might indicate slowness in the classifier execution.

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       s.program_name AS application_name,
       s.login_name,
       s.host_name,
       s.status,
       d.name AS database_name,
       MIN(s.login_time) AS first_login_time,
       MAX(s.login_time) AS last_login_time,
       MAX(s.last_request_start_time) AS last_request_start_time,
       COUNT(1) AS session_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON s.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         s.program_name,
         s.login_name,
         s.host_name,
         s.status,
         d.name;

Aggregate requests by workload group and request attributes

The following query returns a distribution of requests across workload groups and aggregate request statistics for each workload group:

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       r.command,
       r.status,
       d.name AS database_name,
       COUNT(1) AS request_count,
       MIN(r.start_time) AS first_request_start_time,
       MAX(r.start_time) AS last_request_start_time,
       SUM(CAST(r.total_elapsed_time AS bigint)) AS total_elapsed_time_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON r.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON r.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         r.command,
         r.status,
         d.name;

Resource governor best practices

Configure Dedicated Administrator Connection (DAC), and learn how to use it. For more information, see Diagnostic connection for database administrators. If your resource governor configuration malfunctions, you can use DAC to troubleshoot it or to disable resource governor.
When configuring resource pools, be careful specifying large values for MIN_CPU_PERCENT, MIN_MEMORY_PERCENT, and MIN_IOPS_PER_VOLUME. A MIN configuration setting reserves resources for a resource pool and makes them unavailable to other resource pools, including the default pool. For more information, see Create a resource pool.
The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.
If you need to use a table in the classifier, and the table is small and mostly static, consider using a table-valued constructor instead, as shown in an example earlier in this article.
Avoid using a frequently modified table in the classifier. That increases the risk of blocking that can delay logins and cause connection timeouts. The following workarounds can mitigate the risk, however they have downsides, including the risk of incorrect classification:
- Consider using the NOLOCK table hint, or the equivalent READUNCOMMITTED hint. For more information, see READUNCOMMITTED.
- Consider using the LOCK_TIMEOUT setting at the start of the classifier function, setting it to a low value such as 1,000 milliseconds. For more information, see SET LOCK_TIMEOUT.
You can't modify a classifier function while it is referenced in the resource governor configuration. However, you can modify the configuration to use a different classifier function. If you want to make changes to the classifier, consider creating a pair of classifier functions. For example, you might create dbo.rg_classifier_A() and dbo.rg_classifier_B(). When a change to the classifier logic is needed, follow these steps:
1. Use the ALTER FUNCTION statement to make the changes in the function not currently used in the resource governor configuration.
2. Use the ALTER RESOURCE GOVERNOR statement to make the modified classifier active, and then reconfigure resource governor. For example:
```
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier_B);
ALTER RESOURCE GOVERNOR RECONFIGURE;
```
3. If a change is needed again, follow the same steps using the other function (dbo.rg_classifier_A()).
Resource governor configuration is stored in the master database. Make sure to periodically back up master, and know how to restore it. For more information, see Back up and restore: System databases. Because there are limitations around restoring master, we recommend that you also save a copy of resource governor configuration scripts separately. You can recreate resource governor configuration from scripts if the master database needs to be rebuilt.

Compartilhar via

Tutorial: Resource governor configuration examples and best practices

Modify the default group

Use a user-defined workload group

Use multiple resource pools and workload groups

Monitor resource governor using system views

Resource pool runtime statistics

Workload group runtime statistics

Aggregate sessions by workload group and session attributes

Aggregate requests by workload group and request attributes

Resource governor best practices

Comentários

Recursos adicionais

Compartilhar via

Tutorial: Resource governor configuration examples and best practices

Modify the default group

Use a user-defined workload group

Use multiple resource pools and workload groups

Monitor resource governor using system views

Resource pool runtime statistics

Workload group runtime statistics

Aggregate sessions by workload group and session attributes

Aggregate requests by workload group and request attributes

Resource governor best practices

Related content

Comentários

Recursos adicionais