SQL Server Resource Governor

SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.

Benefits of Resource Governor

Resource Governor enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical I/O, and memory.

By using Resource Governor, you can:

  • Provide multitenancy and resource isolation on single instances of SQL Server that serve multiple client workloads. That is, you can divide the available resources on a server among the workloads and minimize the problems that can occur when workloads compete for resources.
  • Provide predictable performance and support SLAs for workload tenants in a multi-workload and multi-user environment.
  • Isolate and limit runaway queries or throttle I/O resources for operations such as DBCC CHECKDB that can saturate the I/O subsystem and negatively impact other workloads.
  • Add fine-grained resource tracking for resource usage chargebacks and provide predictable billing to the consumers of the server resources.

Resource Governor Constraints

This release of Resource Governor has the following constraints:

  • Resource management is limited to the SQL Server Database Engine. Resource Governor cannot be used for Analysis Services, Integration Services, and Reporting Services.
  • There is no workload monitoring or workload management between SQL Server instances.
  • Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage percent.
  • The ability to govern physical I/O only applies to user operations and not system tasks. System tasks include write operations to the transaction log and Lazy Writer I/O operations. The Resource Governor applies primarily to user read operations because most write operations are typically performed by system tasks.
  • You cannot set I/O thresholds on the internal resource pool.

Resource Concepts

The following three concepts are fundamental to understanding and using Resource Governor:

  • Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance. Two resource pools (internal and default) are created when SQL Server is installed. Resource Governor also supports user-defined resource pools.
  • Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed. Resource Governor also supports user-defined workload groups.
  • Classification. The Classification process assigns incoming sessions to a workload group based on the characteristics of the session. You can tailor the classification logic by writing a user-defined function, called a classifier function. Resource Governor also supports a classifier user-defined function for implementing classification rules.

In the context of Resource Governor, you can treat the preceding concepts as components. The following illustration shows these components and their relationship with each other as they exist in the database engine environment. From a processing perspective, the simplified flow is as follows:

  • There is an incoming connection for a session (Session 1 of n).
  • The session is classified (Classification).
  • The session workload is routed to a workload group, for example, Group 4.
  • The workload group uses the resource pool it is associated with, for example, Pool 2.
  • The resource pool provides and limits the resources required by the application, for example, Application 3.

Enable Resource Governor

The Resource Governor is turned off by default. You can enable the Resource Governor by using either SQL Server Management Studio or Transact-SQL.

Before You Begin

Enabling the resource governor has the following results:

  • The classifier function is run for new connections so that their workloads 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 the Resource Governor was disabled.

Limitations and Restrictions

You cannot use the ALTER RESOURCE GOVERNOR statement to enable Resource Governor when in a user transaction.

Permissions

Enabling the Resource Governor requires CONTROL SERVER permission.

Enable Resource Governor Using Object Explorer

To enable the Resource Governor by using Object Explorer

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor, and then click Enable.

Enable Resource Governor Using Resource Governor Properties

To enable the Resource Governor by using the Resource Governor Properties page

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor and then click Properties, this opens the Resource Governor Properties page.
  3. Click the Enable Resource Governor check box, and then click OK.

Enable Resource Governor Using Transact-SQL

To enable the Resource Governor by using Transact-SQL

  1. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example enables the Resource Governor.

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Disable Resource Governor

You can disable the Resource Governor by using either SQL Server Management Studio or Transact-SQL.

Disabling the Resource Governor has the following results:

  • The classifier function is not run.
  • All new connections are automatically classified into the default workload 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 the 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 workload groups and resource pools.

Limitations and Restrictions

You cannot use the ALTER RESOURCE GOVERNOR statement to disable Resource Governor when in a user transaction.

Permissions

Disabling the Resource Governor requires CONTROL SERVER permission.

Disable Resource Governor Using Object Explorer

To disable the Resource Governor by using Object Explorer

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor, and then click Disable.

Disable Resource Governor Using Resource Governor Properties

To disable the Resource Governor by using the Resource Governor Properties page

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor and then click Properties, this opens the Resource Governor Properties page.
  3. Click the Enable Resource Governor check box, ensure that the box is not selected, and then click OK.

Disable Resource Governor Using Transact-SQL

To disable the Resource Governor by using Transact-SQL

  1. Run the ALTER RESOURCE GOVERNOR DISABLE statement.

Example (Transact-SQL)

The following example enables the Resource Governor.

ALTER RESOURCE GOVERNOR DISABLE; 

GO

Configure Resource Governor Using a Template

You can configure Resource Governor by using a template that is provided in SQL Server Management Studio.

Before You Begin

Use the following steps to open and modify a template that creates a resource pool and a workload group for the pool. In addition, this template enables you to create a classifier user-defined function that routes new connections to either the default group or the workload group that you create.

Permissions

The resource governor Transact-SQL statements in the template require CONTROL SERVER permission.

Configure Resource Governor Using a Template

To configure resource governor by using a template in SQL Server Management Studio

  1. In SQL Server Management Studio, on the View menu, click Template Explorer.
  2. In Template Explorer, expand Resource Governor, and then double-click Configure Resource Governor.
  3. In Connect to Database Engine, enter the required information, and then click OK. The template Configure Resource Governor.sql is provided in the Query Editor. Use this template to create and configure a resource pool, a workload group, and a classifier function.
  4. To change the values in the template, press CTRL+SHIFT+M. In the Specify Values for Template Parameters window, enter the values that you want to use.
  5. To save the changes that you make to the template, click OK.
  6. To run the query, click Execute.

View Resource Governor Properties

You can create or configure Resource Governor entities, such as resource pools and workload groups, by using the Resource Governor Properties page in SQL Server Management Studio.

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, click the down arrow on the error message.

You can determine whether there is a configuration pending by querying the sys.dm_resource_governor_configuration dynamic management view to get the current status of is_configuration_pending.

Permissions

Viewing resource governor properties requires VIEW SERVER STATER permission. The resource governor configuration tasks require 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 recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor and then click Properties, this opens the Resource Governor Properties page.
  3. For descriptions of the fields in the page.
  4. To save any changes, click OK.

Resource Governor properties

Classifier function name
Specify the classifier function by selecting from the list.

Enable Resource Governor
Enable or disable Resource Governor by selecting or clearing the check box.

Resource pools
Create or change resource pool and external resource pool configuration by using the grid that is provided. This grid is populated with information for the predefined internal and default pools. Select a pool to work with by clicking the first column in the row for the pool. To create a new resource pool, click the row that is prefixed by the asterisk (*).

Name
Specify the name of the resource pool.

Minimum CPU %
Specify the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. Range is 0 to 100.

Maximum CPU %
Specify the maximum average CPU bandwidth that all requests in this resource pool will 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 this resource pool that can not be shared with other resource pools. Range is 0 to 100.

Maximum Memory %
Specify the total server memory that can be used by requests in this resource pool. Range is 0 to 100. The default setting is 100.

Workload groups for resource pool
Create or change the workload group configuration by using the grid that is provided. This grid is populated with information for the predefined internal and default groups. Select a group to work with by clicking the first column in the row for the pool. To create a new workload group, click the row that is prefixed by the asterisk (*).

Name
Specify the name of the workload group.

Importance
Specify the relative importance of a request in the workload group. Available settings are Low, Medium, and High.

Maximum Requests
Specify the maximum number of simultaneous requests that are allowed to execute in the workload group. Must be 0 or a positive integer.

CPU Time (sec)
Specify the maximum amount of CPU time that a request can use. Must be 0 or a positive integer. If 0, the time is unlimited.

Memory Grant %
Specify the maximum amount of 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 resource to become available before the query fails. Must be 0 or a positive integer.

Degree of Parallelism
Specify the maximum degree of parallelism (DOP) for parallel requests. Range is 0 to 64.

View Resource Governor properties using Transact-SQL

Resource Governor Resource Pool

In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.

Resource Pool Concepts

A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance. A pool has two parts. One part does not overlap with other pools, which enables minimum resource reservation. The other part is shared with other pools, which supports maximum possible resource consumption. The pool resources are defined by specifying one or more of the following settings for each resource (CPU, memory, and physical IO):

  • MIN_CPU_PERCENT and MAX_CPU_PERCENT

These settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. You can use these settings to establish predictable CPU resource usage for multiple workloads that is based on the needs of each workload. For example, assume the Sales and Marketing departments in a company share the same database. The Sales department has a CPU-intensive workload with high-priority queries. The Marketing department also has a CPU-intensive workload, but has lower-priority queries. By creating a separate resource pool for each department, you can assign a minimum CPU percentage of 70 for the Sales resource pool and a maximum CPU percentage of 30 for the Marketing resource pool. This ensures that the Sales workload receives the CPU resources it requires and the Marketing workload is isolated from the CPU demands of the Sales workload. Note that maximum CPU percentage is an opportunistic maximum. If there is available CPU capacity, the workload uses it up to 100 percent. The maximum value only applies when there is contention for CPU resources. In this example, if the Sales workload is switched off, the Marketing workload can use 100 percent of the CPU if needed.

  • CAP_CPU_PERCENT

The CAP_CPU_PERCENT setting is a hard cap limit on the CPU bandwidth for all requests in the resource pool. Workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT. Using the example above, lets assume that the Marketing department is being charged for their resource usage. They want predictable billing and do not want to pay for more than 30 percent of the CPU. This can be accomplished by setting the CAP_CPU_PERCENT to 30 for the Marketing resource pool.

  • MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that cannot be shared with other resource pools. For databases without memory-optimized tables, the memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial to be careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of overall memory.

In order to govern memory for memory-optimized tables with Resource Governor, you should bind the database to a separate resource pool.

  • AFFINITY

This setting lets you affinitize a resource pool to one or more schedulers or NUMA nodes for greater isolation of CPU resources. Using the Sales and Marketing scenario above, lets assume that the Sales department needs a more isolated environment and wants 100 percent of a CPU core at all times. By using the AFFINITY option the Sales and Marketing, workloads can be scheduled on different CPUs. Assuming the CAP_CPU_PERCENT on the Marketing pool is still in place, the Marketing workload continues to use a maximum of 30 percent of one core, while the Sales workload uses 100 percent of the other core. As far as the Sales and Marketing workloads are concerned, they are running on two isolated machines.

  • MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME

These settings are the minimum and maximum physical IO operations per second (IOPS) per disk volume for a resource pool. You can use these settings to control the physical IOs issued for user threads for a given resource pool. For example, the Sales department generates several end-of-month reports in large batches. The queries in these batches can generate IOs that can saturate the disk volume and impact the performance of other higher priority workloads in the database. To isolate this workload, the MIN_IOPS_PER_VOLUME is set to 20 and the MAX_IOPS_PER_VOLUME is set to 100 for the Sales department resource pool, which controls the level of IOs that can be issued for the workload.

When configuring CPU or Memory, the sum of MIN values across all pools cannot exceed 100 percent of the server resources. In addition, when configuring CPU or Memory, MAX and CAP values can be set anywhere in the range between MIN and 100 percent inclusive.

If a pool has a nonzero MIN defined, the effective MAX value of other pools is readjusted. The minimum of the configured MAX value of a pool and the sum of the MIN values of other pools is subtracted from 100 percent.

The following table illustrates a few of the preceding concepts. The table shows the settings for the internal pool, the default pool, and two user-defined pools.

Pool nameMIN % settingMAX % settingCalculated effective MAX %Calculated shared %Comment
internal01001000Effective MAX% and shared% are not applicable to the internal pool.
default01003030The effective MAX value is calculated as: min(100,100-(20+50)) = 30. The calculated shared % is effective MAX – MIN = 30.
Pool 1201005030The effective MAX value is calculated as: min(100,100-50) = 50. The calculated Shared % is Effective MAX – MIN = 30.
Pool 250707020The effective MAX value is calculated as: min(70,100-20) = 70. The calculated Shared % is Effective MAX – MIN = 20.

The following formulas are used for calculating the effective MAX% and the shared % in the table above:

  • Min(X,Y) means the smaller value of X and Y.
  • Sum(X) means the sum of value X across all pools.
  • Total shared % = 100 – sum(MIN %).
  • Effective MAX % = min(X,Y).
  • Shared % = Effective MAX % – MIN %.

Using the preceding table as an example, we can further illustrate the adjustments that take place when another pool is created. This pool is Pool 3 and has a MIN % setting of 5.

Pool nameMIN % settingMAX % settingCalculated effective MAX %Calculated shared %Comment
internal01001000Effective MAX % and shared % are not applicable to the internal pool.
default01002525The effective MAX value is calculated as: min(100,100-(20+50+5)) = 25. The calculated shared % is Effective MAX – MIN = 25.
Pool 1201004525The effective MAX value is calculated as: min(100,100-55) = 45. The calculated Shared % is Effective MAX – MIN = 25.
Pool 250707020The effective MAX value is calculated as: min(70,100-25) = 70. The calculated Shared % is effective MAX – MIN = 20.
Pool 351003025The effective MAX value is calculated as: min(100,100-70) = 30. The calculated Shared % is effective MAX – MIN = 25.

The shared part of the pool is used to indicate where available resources can go if resources are available. However, when resources are consumed they go to the specified pool and are not shared. This may improve resource utilization in cases where there are no requests in a given pool and the resources configured to the pool can be freed up for other pools.

Some extreme cases of pool configuration are:

  • All pools define minimums that in total represent 100 percent of the server resources. In this case, the effective maximums are equal to minimums. This is equivalent to dividing the server resources into non-overlapping pieces regardless of resources are consumed inside any given pool.
  • All pools have zero minimums. All the pools compete for available resources and their final sizes are based on resource consumption in each pool. Other factors such as policies play a role in shaping the final pool size.

Resource Governor predefines two resource pools, the internal pool and the default pool. You can add additional pools.

Internal Pool

The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group, and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools.

Default Pool

The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group. Beginning with SQL Server 2016 (13.x) there is a default resource pool for routine SQL Server operations, and a default external resource pool for external processes, such as executing R scripts.

External Pool

Users can define an external pool to define resources for the external processes. For R Services, this specifically governs rterm.exe, BxlServer.exe, and other processes spawned by them.

User-Defined Resource Pools

User-defined resource pools are those that you create for specific workloads in your environment. Resource Governor provides DDL statements for creating, changing, and dropping resource pools.

Create a Resource Pool

You can create a resource pool by using SQL Server Management Studio or Transact-SQL.

Before You Begin

Limitations and Restrictions

The maximum CPU percentage must be equal to or higher than the minimum CPU percentage. The maximum memory percentage must be equal to or higher than the minimum memory percentage.

The sums of the minimum CPU percentages and minimum memory percentages for all resource pools must not exceed 100.

Permissions

Creating a resource pool requires CONTROL SERVER permission.

Create a Resource Pool Using SQL Server Management Studio

To create a resource pool by using SQL Server Management Studio

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to and including Resource Governor.
  2. Right-click Resource Governor, and then click Properties.
  3. In the Resource pools grid, click the first column in the empty row. This column is labeled with an asterisk (*).
  4. Double-click the empty cell in the Name column. Type in the name that you want to use for the resource pool.
  5. Click or double-click any other cells in the row you want to change, and enter the new values.
  6. To save the changes, click OK

Create a Resource Pool Using Transact-SQL

To create a resource pool by using Transact-SQL

  1. Run the CREATE RESOURCE POOL or CREATE EXTERNAL RESOURCE POOL statement specifying the property values to be set.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example creates a resource pool named poolAdhoc.

CREATE RESOURCE POOL poolAdhoc 

WITH (MAX_CPU_PERCENT = 20); 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Change Resource Pool Settings

You can change resource pool settings by using SQL Server Management Studio or Transact-SQL.

Before You Begin

Limitations and Restrictions

The maximum CPU percentage must be equal to or higher than the minimum CPU percentage. The maximum memory percentage must be equal to or higher than the minimum memory percentage.

The sums of the minimum CPU percentages and minimum memory percentages for all resource pools must not exceed 100.

Permissions

Changing resource pool settings requires CONTROL SERVER permission.

Change Resource Pool Settings Using SQL Server Management Studio

To change resource pool settings by using SQL Server Management Studio

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to and including Resource Pools.
  2. Right-click the resource pool to be modified, and then click Properties.
  3. In the Resource Governor Properties page, select the row for the resource pool in the Resource pools grid if it is not automatically selected.
  4. Click or double-click the cells in the row to be changed, and enter the new values.
  5. To save the changes, click OK

Change Resource Pool Settings Using Transact-SQL

To change resource pool settings by using Transact-SQL

  1. Run the ALTER RESOURCE POOL or ALTER EXTERNAL RESOURCE POOL statement specifying the property values to be changed.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example changes the max CPU percentage setting for the resource pool named poolAdhoc.

ALTER RESOURCE POOL poolAdhoc 

WITH (MAX_CPU_PERCENT = 25); 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Delete a Resource Pool

You can delete a resource pool by using either SQL Server Management Studio or Transact-SQL.

Before You Begin

You cannot delete a resource pool if it contains workload groups.

Limitations and Restrictions

You cannot delete the Resource Governor default or internal resource pools. You cannot delete a resource pool if it contains workload groups.

Permissions

Deleting a resource pool requires CONTROL SERVER permission.

Delete a Resource Pool Using Object Explorer

To delete a resource pool by using SQL Server Management Studio

  1. In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to and including Resource Governor.
  2. Right-click the resource pool to be deleted, and then click Delete.
  3. In the Delete Object window, the resource pool is listed in the Object to be deleted list. To delete the resource pool, click OK.

Delete a Resource Pool Using Transact-SQL

To delete a resource pool by using Transact-SQL

  1. Run the DROP RESOURCE POOL or DROP EXTERNAL RESOURCE POOL statement specifying the name of the resource pool to delete.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example drops a workload group named poolAdhoc.

DROP RESOURCE POOL poolAdhoc; 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Resource Governor Workload Group

In the SQL Server Resource Governor, a workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool, which represents a subset of the physical resources of an instance of the Database Engine. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the policies assigned to the workload group and the resources defined for the resource pool.

Workload Group Concepts

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request. A workload group allows the aggregate monitoring of resource consumption and the application of a uniform policy to all the requests in the group. A group defines the policies for its members.

Resource Governor predefines two workload groups: the internal group and the default group. A user cannot change anything classified as an internal group, but can monitor it. Requests are classified into the default group when the following conditions exist:

  • There are no criteria to classify a request.
  • There is an attempt to classify the request into a non-existent group.
  • There is a general classification failure.

Resource Governor also provides DDL statements for creating, changing, and dropping workload groups.

Create a Workload Group

You can create a workload group by using SQL Server Management Studio or Transact-SQL.

REQUEST_MAX_MEMORY_GRANT_PERCENT

The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit, (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the workload group setting, this index creation may fail. Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 compatibility, the user may be able to run the same index creation in the default workload group, if the default resource pool has enough total memory configured to run such a query.

Index creation is allowed to use more memory workspace than initially granted for performance. This special handling is supported by Resource Governor, however, the initial grant and any additional memory grant are limited by the workload group and resource pool settings.

Permissions

Creating a workload group requires CONTROL SERVER permission.

Create a Workload Group Using SQL Server Management Studio

To create a workload group by using SQL Server Management Studio

  1. In Object Explorer, recursively expand the Management node down to and including the resource pool that contains the workload group to be modified.
  2. Right-click the Workload Groups folder, and then click New Workload Group.
  3. In the Resource pools grid, ensure the resource pool where you want to add the workload group is highlighted.
  4. The Workload groups for resource pool grid will have a new line with a blank name and default values in the other columns.
  5. Click the Name cell and enter a name for the workload group.
  6. Click or double-click any other cells in the row you want to change from their default settings, and enter the new values.
  7. To save the changes, click OK

Create a Workload Group Using Transact-SQL

To create a workload group by using Transact-SQL

  1. Run the CREATE WORKLOAD GROUP statement specifying the property values to be set.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example creates a workload group named groupAdhoc in the resource pool named poolAdhoc.

SQL

CREATE WORKLOAD GROUP groupAdhoc 

USING poolAdhoc; 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Change Workload Group Settings

You can change workload group settings by using SQL Server Management Studio.

You can change the settings of the default workload group and user-defined workload groups.

REQUEST_MAX_MEMORY_GRANT_PERCENT

The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit, (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the workload group setting, this index creation may fail. Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 compatibility, the user may be able to run the same index creation in the default workload group, if the default resource pool has enough total memory configured to run such a query.

Index creation is allowed to use more memory workspace than initially granted for performance. This special handling is supported by Resource Governor, however, the initial grant and any additional memory grant are limited by the workload group and resource pool settings.

Permissions

Changing workload group settings requires CONTROL SERVER permission.

Change Workload Group Settings Using SQL Server Management Studio

To change workload group settings by using SQL Server Management Studio

  1. In Object Explorer, recursively expand the Management node down to and including the Workload Groups folder that contains the workload group to be modified.
  2. Right-click the workload group to be modified, and then click Properties.
  3. In the Resource Governor Properties page, select the row for the workload group in the Workload groups for resource pool grid if it is not automatically selected.
  4. Click or double-click the cells in the row to be changed, and enter the new values.
  5. To save the changes, click OK

Change Workload Group Settings Using Transact-SQL

To change workload group settings by using Transact-SQL

  1. Run the ALTER WORKLOAD GROUP statement specifying the property values to be changed.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example changes the max memory grant percent setting for the workload group named groupAdhoc.

ALTER WORKLOAD GROUP groupAdhoc 

WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 30); 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Move a Workload Group

You can move a Resource Governor workload group to a different resource pool by using either SQL Server Management Studio or Transact-SQL.

You cannot move a workload group if there is a pending Resource Governor configuration operation.

You cannot move a workload group if there is a pending Resource Governor configuration operation. You can determine whether there is a configuration pending by querying the sys.dm_resource_governor_configuration (Transact-SQL) dynamic management view to get the current status of is_configuration_pending.

Permissions

Moving a workload group requires CONTROL SERVER permission.

Move a Workload Group Using SQL Server Management Studio

To move a workload group by using Management Studio

  1. In Object Explorer, recursively expand the Management node down to Resource Governor.
  2. Right-click Resource Governor and then click Properties, this opens the Resource Governor Properties page.
  3. In the Resource Pools window, click the resource pool containing the workload group to be moved. The Workload Groups window now lists the workload groups in that resource pool.
  4. In the Workload Groups window, right-click the right arrow to the left of the workload group to be moved, and click Move to. This displays a Move Workload Group window.
  5. Available resource pools are displayed in the window. Click the name of the resource pool that you want to move the workload group to, and then click OK to carry out this action.
  6. This action is not completed until after you click OK. When you click OK, the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed.
  7. If the create or reconfigure operation fails for the resource pool or workload group, a summary error message appears below the title of the property page. To see a detailed error message, click the down arrow on the error message.

Move a Workload Group Using Transact-SQL

To move a workload group by using Transact-SQL

  1. Run the ALTER WORKLOAD GROUP statement specifying the name of the workload group to be moved and the resource pool to which it should be moved.
  2. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example moves a workload group named groupAdhoc to the default resource pool.

ALTER WORKLOAD GROUP groupAdhoc 

USING [default]; 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO 

Delete a Workload Group

You can delete a workload group or resource pool by using either SQL Server Management Studio or Transact-SQL.

You cannot delete a workload group if it contains active sessions.

Limitations and Restrictions

If a workload group contains active sessions, deleting or moving the workload group to a different resource pool will fail when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is called to apply the change. To avoid this problem, you can take one of the following actions:

  • Wait until all the sessions from the affected group have disconnected, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
  • Explicitly stop sessions in the affected group by using the KILL command, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement. If you decide that you do not want to explicitly stop sessions after you use Delete but before you stop active sessions, re-create the group by using the original name and move the group to the original resource pool.
  • Restart the server. After the restart process is completed, the deleted group will not be created, and a moved group will use the new resource pool assignment.

Permissions

Deleting a workload group requires CONTROL SERVER permission.

Delete a Workload Group Using Object Explorer

To delete a workload group by using Object Explorer

  1. InSQL Server Management Studio, open Object Explorer and recursively expand the Management node down to and including Resource Pools.
  2. Recursively expand Resource Pools down to and including the Workload Groups node in the resource pool that contains the workload group to be deleted.
  3. Right-click the workload group, and then click Delete.
  4. In the Delete Object window, the workload group is listed in the Object to be deleted list. To delete the workload group, click OK.

Delete a Workload Group Using Resource Governor Properties

To delete a workload group by using the Resource Governor Properties page

  1. In Object Explorer, recursively expand the Management node down to and including Resource Pools.
  2. Right-click the resource pool that contains the workload group to be deleted, and then click Properties. This opens the Resource Governor Properties page.
  3. In the Workload groups for resource pool window, click the line for the workload group to be deleted, then right-click the right arrow on the left side of the line, and then click Delete.
  4. To delete the workload group, click OK.

Delete a Workload Group Using Transact-SQL

To delete a workload group by using Transact-SQL

  1. Run the DROP WORKLOAD GROUP statement specifying the name of the workload group to delete.
  2. Before you issue the ALTER RESOURCE GOVERNOR RECONFIGURE statement, verify that there are no active requests in the workload group being deleted. If there are active requests, ALTER RESOURCE GOVERNOR will fail. To avoid this issue, you can take one of the following actions:
    1. Wait until all the sessions from the workload group have disconnected.
    1. Explicitly stop sessions in the workload group by using the KILL command.
    1. Restart the server. The workload group will not be re-created.
    1. In a scenario in which you have issued the DROP WORKLOAD GROUP statement but decide that you do not want to explicitly stop sessions to apply the change, you can re-create the group by using the same name that it had before you issued the DROP statement, and then move the group to the original resource pool.
  3. Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example (Transact-SQL)

The following example drops a workload group named groupAdhoc.

DROP WORKLOAD GROUP groupAdhoc; 

GO 

ALTER RESOURCE GOVERNOR RECONFIGURE; 

GO