Edit

Share via

View and modify resource governor properties

  • Article

Applies to: SQL Server Azure SQL Managed Instance

You can create and configure resource governor resource pools and workload groups by using the resource governor properties page in SQL Server Management Studio.

Reconfigure resource governor

When you select OK after adding, deleting, or moving a workload group or resource pool, the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed.

If the create or reconfigure operation for the resource pool or workload group fails, a summary error message appears below the title of the property page. To see a detailed error message, select the down arrow on the error message.

You can determine whether there is a pending configuration change by checking the value of the is_configuration_pending column in the sys.dm_resource_governor_configuration dynamic management view.

Permissions

Viewing resource governor properties requires the VIEW SERVER STATE permission, or the VIEW SERVER PERFORMANCE STATE permission in SQL Server 2022 (16.x) and later. The resource governor configuration tasks require the CONTROL SERVER permission.

Resource governor properties page

To view resource governor properties by using the resource governor properties page in SQL Server Management Studio:

  1. In SQL Server Management Studio, open Object Explorer and expand the Management node down to Resource Governor.
  2. Use the Resource Governor context menu and select Properties.
  3. For descriptions of resource pool and workload group properties, see resource governor properties.
  4. To save any changes, select OK.

Resource governor properties

Property Description
Classifier function name Specify the classifier function by selecting from the list. To create a classifier function and for more information, see Resource governor classifier function.
Enable resource governor Enable or disable resource governor by selecting or clearing the check box.

Resource pool properties

Create or modify resource pool and external resource pool configuration by using the grids. Each grid displays the current configuration of the existing resource pools, including the built-in internal and default pools. Select a pool to work with by selecting a row. When the Enable Resource Governor check box is selected, you can create a new resource pool by selecting the row marked with an asterisk (*).

Property Description
Name Specify the name of the resource pool.
Minimum CPU % Specify the guaranteed average CPU bandwidth for requests in the resource pool when there is CPU contention. Range is 0 to 100. The default setting is 0.
Maximum CPU % Specify the maximum average CPU bandwidth that requests in this resource pool receive when there is CPU contention. Range is 0 to 100. The default setting is 100.
Minimum Memory % Specify the minimum amount of memory reserved for requests in this resource pool that can not be shared with other resource pools. Range is 0 to 100. The default setting is 0.
Maximum Memory % Specify the total server memory that requests in this resource pool can use. Range is 0 to 100. The default setting is 100.

For more information, see CREATE RESOURCE POOL and CREATE EXTERNAL RESOURCE POOL.

Workload group properties

Create or modify the workload group configuration by using the grid. The grid displays the current configuration of the existing workload groups, including the built-in internal and default groups. Select a group to work with by selecting a row. When the Enable Resource Governor check box is selected, and a resource pool other than internal is selected, you can create a new workload group in that resource pool by selecting the row marked with an asterisk (*).

Property Description
Name Specify the name of the workload group.
Importance Specify the relative importance for requests in the workload group. Available settings are Low, Medium, and High.
Maximum Requests Specify the maximum number of concurrent requests that are allowed to execute in the workload group. Must be 0 (not limited by resource governor) or a positive integer.
CPU Time (sec) Specify the maximum amount of CPU time that a request can use. Must be 0 (not limited by resource governor) or a positive integer.
Memory Grant % Specify the maximum amount of query grant memory that a single request can take from the pool. Range is 0 to 100.
Grant Time-out (sec) Specify the maximum time that a query can wait for a memory grant to become available before the query fails. Must be 0 (not limited by resource governor) or a positive integer.
Degree of Parallelism Specify the maximum degree of parallelism (DOP) for requests using intra-query parallelism. Range is 0 (not limited by resource governor) to 64.

For more information, see CREATE WORKLOAD GROUP.

View resource governor properties using Transact-SQL

  1. To view the persisted resource governor configuration, use resource governor catalog views. If the persisted resource governor configuration is modified, it doesn't become effective until the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed.
  2. To view the currently effective runtime resource governor configuration and statistics, use resource governor dynamic management views.

In addition to viewing and modifying resource governor properties, you can use the Resource Governor Properties page to perform several configuration tasks. For more information, see:

Related content