# Microsoft SQL Server resource governor with RDS for SQL Server
<a name="Appendix.SQLServer.Options.ResourceGovernor"></a>

Resource governor is a SQL Server Enterprise Edition feature that gives you precise control over your instance resources. It enables you to set specific limits on how workloads use CPU, memory, and physical I/O resources. With resource governor, you can:
+ Prevent resource monopolization in multi-tenant environments by managing how different workloads share instance resources
+ Deliver predictable performance by setting specific resource limits and priorities for different users and applications

You can enable resource governor on either an existing or new RDS for SQL Server DB instance.

Resource governor uses three fundamental concepts:
+ **Resource pool** - A container that manages your instance physical resources (CPU, memory, and I/O). You get two built-in pools (internal and default) and you can create additional custom pools.
+ **Workload group** - A container for database sessions with similar characteristics. Every workload group belongs to a resource pool. You get two built-in workload groups (internal and default) and you can create additional custom workload groups.
+ **Classification** - The process that determines which workload group handles incoming sessions based on user name, application name, database name or host name.

For additional details about resource governor functionality in SQL Server, see [Resource Governor](https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver16) in the Microsoft documentation.

**Contents**
+ [

## Supported versions and Regions
](#ResourceGovernor.SupportedVersions)
+ [

## Limitations and recommendations
](#ResourceGovernor.Limitations)
+ [

# Enabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Enabling.md)
  + [

## Creating the option group for `RESOURCE_GOVERNOR`
](ResourceGovernor.Enabling.md#ResourceGovernor.OptionGroup)
  + [

## Adding the `RESOURCE_GOVERNOR` option to the option group
](ResourceGovernor.Enabling.md#ResourceGovernor.Add)
  + [

## Associating the option group with your DB instance
](ResourceGovernor.Enabling.md#ResourceGovernor.Apply)
+ [

# Using Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Using.md)
  + [

## Manage resource pool
](ResourceGovernor.Using.md#ResourceGovernor.ManageResourcePool)
    + [

### Create resource Pool
](ResourceGovernor.Using.md#ResourceGovernor.CreateResourcePool)
    + [

### Alter resource pool
](ResourceGovernor.Using.md#ResourceGovernor.AlterResourcePool)
    + [

### Drop resource pool
](ResourceGovernor.Using.md#ResourceGovernor.DropResourcePool)
  + [

## Manage workload groups
](ResourceGovernor.Using.md#ResourceGovernor.ManageWorkloadGroups)
    + [

### Create workload group
](ResourceGovernor.Using.md#ResourceGovernor.CreateWorkloadGroup)
    + [

### Alter workload group
](ResourceGovernor.Using.md#ResourceGovernor.AlterWorkloadGroup)
    + [

### Drop workload group
](ResourceGovernor.Using.md#ResourceGovernor.DropWorkloadGroup)
  + [

## Create and register classifier function
](ResourceGovernor.Using.md#ResourceGovernor.ClassifierFunction)
  + [

## Drop classifier function
](ResourceGovernor.Using.md#ResourceGovernor.DropClassifier)
  + [

## De-register classifier function
](ResourceGovernor.Using.md#ResourceGovernor.DeregisterClassifier)
  + [

## Reset statistics
](ResourceGovernor.Using.md#ResourceGovernor.ResetStats)
  + [

## Resource governor configuration changes
](ResourceGovernor.Using.md#ResourceGovernor.ConfigChanges)
  + [

## Bind TempDB to a resource pool
](ResourceGovernor.Using.md#ResourceGovernor.BindTempDB)
  + [

## Unbind TempDB from a resource pool
](ResourceGovernor.Using.md#ResourceGovernor.UnbindTempDB)
  + [

## Cleanup resource governor
](ResourceGovernor.Using.md#ResourceGovernor.Cleanup)
+ [

## Considerations for Multi-AZ deployment
](#ResourceGovernor.Considerations)
+ [

## Considerations for read replicas
](#ResourceGovernor.ReadReplica)
+ [

# Monitor Microsoft SQL Server resource governor using system views for your RDS for SQL Server instance
](ResourceGovernor.Monitoring.md)
  + [

## Resource pool runtime statistics
](ResourceGovernor.Monitoring.md#ResourceGovernor.ResourcePoolStats)
+ [

# Disabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
](ResourceGovernor.Disabling.md)
+ [

# Best practices for configuring resource governor on RDS for SQL Server
](ResourceGovernor.BestPractices.md)

## Supported versions and Regions
<a name="ResourceGovernor.SupportedVersions"></a>

Amazon RDS supports resource governor for the following SQL Server versions and editions in all AWS Regions where RDS for SQL Server is available:
+ SQL Server 2022 Developer and Enterprise Editions
+ SQL Server 2019 Enterprise Edition
+ SQL Server 2017 Enterprise Edition
+ SQL Server 2016 Enterprise Edition

## Limitations and recommendations
<a name="ResourceGovernor.Limitations"></a>

The following limitations and recommendations apply to resource governor:
+ Edition and service restrictions:
  + Available only in SQL Server Enterprise Edition.
  + Resource management is limited to the SQL Server Database Engine. Resource governor for Analysis Services, Integration Services, and Reporting Services are not supported.
+ Configuration restrictions:
  + Must use Amazon RDS stored procedures for all configurations.
  + Native DDL statements and SQL Server Management Studio GUI configurations aren't supported.
+ Resource pool parameters:
  + Pool names starting with `rds_` aren't supported.
  + Internal and default resource pool modifications aren't permitted.
  + For the user-defined resource pools the following resource pool parameters aren't supported:
    + `MIN_MEMORY_PERCENT`
    + `MIN_CPU_PERCENT`
    + `MIN_IOPS_PER_VOLUME`
    + `AFFINITY`
+ Workload group parameters:
  + Workload group names starting with `rds_` aren't supported.
  + Internal workload group modification isn't permitted.
  + For the default workload group:
    + Only the `REQUEST_MAX_MEMORY_GRANT_PERCENT` parameter can be modified.
    + For the default workload group, `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70.
    + All other parameters are locked and can't be changed.
  + User-defined workload groups allow modification of all parameters.
+ Classifier function limitations:
  + Classifier function routes connections to custom workload groups based on specified criteria (user name, database, host, or application name).
  + Supports up to two user-defined workload groups with their respective routing conditions.
  + Combines criterion with `AND` conditions within each group.
  + Requires at least one routing criterion per workload group.
  + Only the classification methods listed above are supported.
  + Function name must start with `rg_classifier_`.
  + Default group assignment if no conditions match.

# Enabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
<a name="ResourceGovernor.Enabling"></a>

Enable resource governor by adding the `RESOURCE_GOVERNOR` option to your RDS for SQL Server DB instance. Use the following process:

1. Create a new option group, or choose an existing option group.

1. Add the `RESOURCE_GOVERNOR` option to the option group.

1. Associate the option group with the DB instance.

**Note**  
Enabling resource governor through an option group doesn't require a reboot.

## Creating the option group for `RESOURCE_GOVERNOR`
<a name="ResourceGovernor.OptionGroup"></a>

To enable resource governor, create an option group or modify an option group that corresponds to the SQL Server edition and version of the DB instance that you plan to use. To complete this procedure, use the AWS Management Console or the AWS CLI.

### Console
<a name="ResourceGovernor.OptionGroup.Console"></a>

Use the following procedure to create an option group for SQL Server Enterprise Edition 2022.

**To create the option group**

1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).

1. In the navigation pane, choose **Option groups**.

1. Choose **Create group**.

1. In the **Create option group** window, do the following:

   1. For **Name**, enter a name for the option group that is unique within your AWS account, such as **resource-governor-ee-2022**. The name can contain only letters, digits, and hyphens.

   1. For **Description**, enter a brief description of the option group, such as **RESOURCE\$1GOVERNOR option group for SQL Server EE 2022**. The description is used for display purposes.

   1. For **Engine**, choose **sqlserver-ee**.

   1. For **Major engine version**, choose **16.00**.

1. Choose **Create**.

### CLI
<a name="ResourceGovernor.OptionGroup.CLI"></a>

The following procedure creates an option group for SQL Server Enterprise Edition 2022.

**To create the option group**
+ Run one of the following commands.  
**Example**  

  For Linux, macOS, or Unix:

  ```
  aws rds create-option-group \
      --option-group-name resource-governor-ee-2022 \
      --engine-name sqlserver-ee \
      --major-engine-version 16.00 \
      --option-group-description "RESOURCE_GOVERNOR option group for SQL Server EE 2022"
  ```

  For Windows:

  ```
  aws rds create-option-group ^
      --option-group-name resource-governor-ee-2022 ^
      --engine-name sqlserver-ee ^
      --major-engine-version 16.00 ^
      --option-group-description "RESOURCE_GOVERNOR option group for SQL Server EE 2022"
  ```

## Adding the `RESOURCE_GOVERNOR` option to the option group
<a name="ResourceGovernor.Add"></a>

Next, use the AWS Management Console or the AWS CLI to add the `RESOURCE_GOVERNOR` option to your option group.

### Console
<a name="ResourceGovernor.Add.Console"></a>

**To add the RESOURCE\$1GOVERNOR option**

1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).

1. In the navigation pane, choose **Option groups**.

1. Choose the option group that you just created, **resource-governor-ee-2022** in this example.

1. Choose **Add option**.

1. Under **Option details**, choose **RESOURCE\$1GOVERNOR** for **Option name**.

1. Under **Scheduling**, choose whether to add the option immediately or at the next maintenance window.

1. Choose **Add option**.

### CLI
<a name="ResourceGovernor.Add.CLI"></a>

**To add the `RESOURCE_GOVERNOR` option**
+ Add the `RESOURCE_GOVERNOR` option to the option group.  
**Example**  

  For Linux, macOS, or Unix:

  ```
  aws rds add-option-to-option-group \
      --option-group-name resource-governor-ee-2022 \
      --options "OptionName=RESOURCE_GOVERNOR" \
      --apply-immediately
  ```

  For Windows:

  ```
  aws rds add-option-to-option-group ^
      --option-group-name resource-governor-ee-2022 ^
      --options "OptionName=RESOURCE_GOVERNOR" ^
      --apply-immediately
  ```

## Associating the option group with your DB instance
<a name="ResourceGovernor.Apply"></a>

To associate the `RESOURCE_GOVERNOR` option group with your DB instance, use the AWS Management Console or the AWS CLI.

### Console
<a name="ResourceGovernor.Apply.Console"></a>

To finish activating resource governor, associate your `RESOURCE_GOVERNOR` option group with a new or existing DB instance:
+ For a new DB instance, associate them when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, associate them by modifying the instance. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).

### CLI
<a name="ResourceGovernor.Apply.CLI"></a>

You can associate the `RESOURCE_GOVERNOR` option group with a new or existing DB instance.

**To create an instance with the `RESOURCE_GOVERNOR` option group**
+ Specify the same DB engine type and major version that you used when creating the option group.  
**Example**  

  For Linux, macOS, or Unix:

  ```
  aws rds create-db-instance \
      --db-instance-identifier mytestsqlserverresourcegovernorinstance \
      --db-instance-class db.m5.2xlarge \
      --engine sqlserver-ee \
      --engine-version 16.00 \
      --license-model license-included \
      --allocated-storage 100 \
      --master-username admin \
      --master-user-password password \
      --storage-type gp2 \
      --option-group-name resource-governor-ee-2022
  ```

  For Windows:

  ```
  aws rds create-db-instance ^
      --db-instance-identifier mytestsqlserverresourcegovernorinstance ^
      --db-instance-class db.m5.2xlarge ^
      --engine sqlserver-ee ^
      --engine-version 16.00 ^
      --license-model license-included ^
      --allocated-storage 100 ^
      --master-username admin ^
      --master-user-password password ^
      --storage-type gp2 ^
      --option-group-name resource-governor-ee-2022
  ```

**To modify an instance and associate the `RESOURCE_GOVERNOR` option group**
+ Run one of the following commands.  
**Example**  

  For Linux, macOS, or Unix:

  ```
  aws rds modify-db-instance \
      --db-instance-identifier mytestinstance \
      --option-group-name resource-governor-ee-2022 \
      --apply-immediately
  ```

  For Windows:

  ```
  aws rds modify-db-instance ^
      --db-instance-identifier mytestinstance ^
      --option-group-name resource-governor-ee-2022 ^
      --apply-immediately
  ```

# Using Microsoft SQL Server resource governor for your RDS for SQL Server instance
<a name="ResourceGovernor.Using"></a>

After adding the resource governor option to your option group, resource governor is not yet active at the database engine level. To fully enable resource governor, you must use RDS for SQL Server stored procedures to enable it and create the necessary resource governor objects. For more information, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).

First, connect to your SQL Server database, then call the appropriate RDS for SQL Server stored procedures to complete the configuration. For instructions on connecting to your database, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md).

For instructions on how to call each stored procedure, see the following topics:

**Topics**
+ [

## Manage resource pool
](#ResourceGovernor.ManageResourcePool)
+ [

## Manage workload groups
](#ResourceGovernor.ManageWorkloadGroups)
+ [

## Create and register classifier function
](#ResourceGovernor.ClassifierFunction)
+ [

## Drop classifier function
](#ResourceGovernor.DropClassifier)
+ [

## De-register classifier function
](#ResourceGovernor.DeregisterClassifier)
+ [

## Reset statistics
](#ResourceGovernor.ResetStats)
+ [

## Resource governor configuration changes
](#ResourceGovernor.ConfigChanges)
+ [

## Bind TempDB to a resource pool
](#ResourceGovernor.BindTempDB)
+ [

## Unbind TempDB from a resource pool
](#ResourceGovernor.UnbindTempDB)
+ [

## Cleanup resource governor
](#ResourceGovernor.Cleanup)

## Manage resource pool
<a name="ResourceGovernor.ManageResourcePool"></a>

### Create resource Pool
<a name="ResourceGovernor.CreateResourcePool"></a>

Once resource governor is enabled on the option group, you can create custom resource pools using `rds_create_resource_pool`. These pools let you allocate specific percentages of CPU, memory, and IOPS to different workloads.

**Usage**

```
USE [msdb]
EXEC dbo.rds_create_resource_pool    
    @pool_name=value,
    @MAX_CPU_PERCENT=value,
    @CAP_CPU_PERCENT=value,
    @MAX_MEMORY_PERCENT=value,
    @MAX_IOPS_PER_VOLUME=value
```

The following parameters are required:
+ `@group_name` - Is the name of an existing user-defined workload group.
+ `@pool_name` - Is the user-defined name for the resource pool. *pool\$1name* is alphanumeric, can be up to 128 characters, must be unique within a Database Engine instance, and must comply with the rules for database identifiers.

The following parameters are optional:
+ `@MAX_CPU_PERCENT` - Specifies the maximum average CPU bandwidth that all requests in resource pool receive when there's CPU contention. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@CAP_CPU_PERCENT` - Specifies a hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to be the same as the specified value. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_MEMORY_PERCENT` - Specifies the maximum amount of query workspace memory that requests in this resource pool can use. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_IOPS_PER_VOLUME` - Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for *value* is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.

**Examples**

Example of creating resource pool with all default values:

```
--This creates resource pool 'SalesPool' with all default values
USE [msdb]
EXEC rds_create_resource_pool @pool_name = 'SalesPool';
     
--Apply changes
USE [msdb]
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
     
--Validate configuration
select * from sys.resource_governor_resource_pools
```

Example of creating resource pool with different parameters specified:

```
--creates resource pool
USE [msdb]
EXEC dbo.rds_create_resource_pool    
@pool_name='analytics',
@MAX_CPU_PERCENT = 30,
@CAP_CPU_PERCENT = 40,
@MAX_MEMORY_PERCENT = 20;
            
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
    
--Validate configuration
select * from sys.resource_governor_resource_pools
```

### Alter resource pool
<a name="ResourceGovernor.AlterResourcePool"></a>

**Usage**

```
USE [msdb]
EXEC dbo.rds_alter_resource_pool    
    @pool_name=value,
    @MAX_CPU_PERCENT=value,
    @CAP_CPU_PERCENT=value,
    @MAX_MEMORY_PERCENT=value,
    @MAX_IOPS_PER_VOLUME=value;
```

The following parameters are required:
+ `@pool_name` - Is the name of an existing user-defined resource pool. Altering default resource pool isn't allowed in Amazon RDS SQL Server.

At least one of the optional parameter must be specified:
+ `@MAX_CPU_PERCENT` - Specifies the maximum average CPU bandwidth that all requests in resource pool receive when there's CPU contention. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@CAP_CPU_PERCENT` - Specifies a hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to be the same as the specified value. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_MEMORY_PERCENT` - Specifies the maximum amount of query workspace memory that requests in this resource pool can use. *value* is an integer with a default setting of 100. The allowed range for *value* is from 1 through 100.
+ `@MAX_IOPS_PER_VOLUME` - Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for *value* is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.

**Examples**

```
--This alters resource pool
USE [msdb]
EXEC dbo.rds_alter_resource_pool    
    @pool_name='analytics',
    @MAX_CPU_PERCENT = 10,
    @CAP_CPU_PERCENT = 20,
    @MAX_MEMORY_PERCENT = 50;

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

--Validate configuration.
select * from sys.resource_governor_resource_pools
```

### Drop resource pool
<a name="ResourceGovernor.DropResourcePool"></a>

**Usage**

```
USE [msdb]
EXEC dbo.rds_drop_resource_pool    
@pool_name=value;
```

The following parameter is required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.

**Note**  
Dropping Internal or default resource pool isn't allowed in SQL Server.

**Examples**

```
--This drops resource pool
USE [msdb]
EXEC dbo.rds_drop_resource_pool    
@pool_name='analytics'

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

--Validate configuration
select * from sys.resource_governor_resource_pools
```

## Manage workload groups
<a name="ResourceGovernor.ManageWorkloadGroups"></a>

Workload groups, created and managed with `rds_create_workload_group` and `rds_alter_workload_group`, allow you to set importance levels, memory grants, and other parameters for groups of queries.

### Create workload group
<a name="ResourceGovernor.CreateWorkloadGroup"></a>

**Usage**

```
USE [msdb]
EXEC dbo.rds_create_workload_group 
@group_name = value, 
@IMPORTANCE ={ LOW | MEDIUM | HIGH }, 
@REQUEST_MAX_MEMORY_GRANT_PERCENT =value, 
@REQUEST_MAX_CPU_TIME_SEC = value , 
@REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value, 
@MAX_DOP = value, 
@GROUP_MAX_REQUESTS = value, 
@pool_name = value
```

The following parameters are required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.
+ `@group_name` - Is the name of an existing user-defined workload group.

The following parameters are optional:
+ `@IMPORTANCE` - Specifies the relative importance of a request in the workload group. The default value is `MEDIUM`.
+ `@REQUEST_MAX_MEMORY_GRANT_PERCENT` - Specifies the maximum amount of query workspace memory that a single request can take from the pool. *value* is a percentage of the resource pool size defined by `MAX_MEMORY_PERCENT`. Default value is 25.
+ `@REQUEST_MAX_CPU_TIME_SEC` - Specifies the maximum amount of CPU time, in seconds, that a batch request can use. *value* must be 0 or a positive integer. The default setting for *value* is 0, which means unlimited.
+ `@REQUEST_MEMORY_GRANT_TIMEOUT_SEC` - Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. *value* must be 0 or a positive integer. The default setting for *value*, 0, uses an internal calculation based on query cost to determine the maximum time.
+ `@MAX_DOP` - Specifies the maximum degree of parallelism (`MAXDOP`) for parallel query execution. The allowed range for *value* is from 0 through 64. The default setting for *value*, 0, uses the global setting.
+ `@GROUP_MAX_REQUESTS` = Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. *value* must be 0 or a positive integer. The default setting for *value* is 0, and allows unlimited requests.
+ `@pool_name` = Associates the workload group with the user-defined resource pool identified by *pool\$1name*, or with the `default` resource pool. If *pool\$1name* isn't provided, the workload group is associated with the built-in `default` pool.

**Examples**

```
--This creates workload group named 'analytics'
USE msdb;
EXEC dbo.rds_create_workload_group 
    @group_name = 'analytics',
    @IMPORTANCE = 'HIGH',
    @REQUEST_MAX_MEMORY_GRANT_PERCENT = 25, 
    @REQUEST_MAX_CPU_TIME_SEC = 0, 
    @REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0, 
    @MAX_DOP = 0, 
    @GROUP_MAX_REQUESTS = 0, 
    @pool_name = 'analytics';

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
  
--Validate configuration
select * from sys.resource_governor_workload_groups
```

### Alter workload group
<a name="ResourceGovernor.AlterWorkloadGroup"></a>

**Usage**

```
EXEC msdb.dbo.rds_alter_workload_group
    @group_name = value,
    @IMPORTANCE = 'LOW|MEDIUM|HIGH',
    @REQUEST_MAX_MEMORY_GRANT_PERCENT = value,
    @REQUEST_MAX_CPU_TIME_SEC = value,
    @REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value,
    @MAX_DOP = value,
    @GROUP_MAX_REQUESTS = value,
    @pool_name = value
```

The following parameters are required:
+ `@group_name` - Is the name of default or an existing user-defined workload group.

**Note**  
Changing only `REQUEST_MAX_MEMORY_GRANT_PERCENT` parameter on the default workload group is supported. For default workload group the `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70. No other parameters can be modified in default workload group. All parameters can be modified in the user-defined workload group.

The following parameters are optional:
+ `@IMPORTANCE` - Specifies the relative importance of a request in the workload group. The default value is MEDIUM.
+ `@REQUEST_MAX_MEMORY_GRANT_PERCENT` - Specifies the maximum amount of query workspace memory that a single request can take from the pool. *value* is a percentage of the resource pool size defined by `MAX_MEMORY_PERCENT`. Default value is 25. On Amazon RDS, `REQUEST_MAX_MEMORY_GRANT_PERCENT` must be between 1 and 70.
+ `@REQUEST_MAX_CPU_TIME_SEC` - Specifies the maximum amount of CPU time, in seconds, that a batch request can use. *value* must be 0 or a positive integer. The default setting for *value* is 0, which means unlimited.
+ `@REQUEST_MEMORY_GRANT_TIMEOUT_SEC` - Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. *value* must be 0 or a positive integer. The default setting for *value*, 0, uses an internal calculation based on query cost to determine the maximum time.
+ `@MAX_DOP` - Specifies the maximum degree of parallelism (MAXDOP) for parallel query execution. The allowed range for *value* is from 0 through 64. The default setting for *value*, 0, uses the global setting.
+ `@GROUP_MAX_REQUESTS` - Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. *value* must be 0 or a positive integer. The default setting for *value* is 0, and allows unlimited requests.
+ `@pool_name` - Associates the workload group with the user-defined resource pool identified by *pool\$1name*.

**Examples**

Example to Modify default workload group change REQUEST\$1MAX\$1MEMORY\$1GRANT\$1PERCENT:

```
--Modify default workload group (set memory grant cap to 10%)
USE msdb
EXEC dbo.rds_alter_workload_group    
    @group_name = 'default',
    @REQUEST_MAX_MEMORY_GRANT_PERCENT=10;
    
--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

--Validate configuration
SELECT * FROM sys.resource_governor_workload_groups WHERE name='default';
```

Example to modify non-default workload group:

```
EXEC msdb.dbo.rds_alter_workload_group    
    @group_name = 'analytics',
    @IMPORTANCE = 'HIGH',
    @REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
    @REQUEST_MAX_CPU_TIME_SEC = 3600,
    @REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
    @MAX_DOP = 4,
    @GROUP_MAX_REQUESTS = 100;

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
```

Example to move a Non-Default Workload Group to another resource pool:

```
EXEC msdb.dbo.rds_alter_workload_group    
@group_name = 'analytics',
@pool_name='abc'

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

--Validate configuration
select * from sys.resource_governor_workload_groups
```

### Drop workload group
<a name="ResourceGovernor.DropWorkloadGroup"></a>

**Usage**

```
EXEC msdb.dbo.rds_drop_workload_group    
@group_name = value
```

The following parameters are required:
+ `@group_name` - Is the name of an existing user-defined workload group.

**Examples**

```
--Drops a Workload Group:
EXEC msdb.dbo.rds_drop_workload_group    
@group_name = 'analytics';

--Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

--Validate configuration
select * from sys.resource_governor_workload_groups
```

## Create and register classifier function
<a name="ResourceGovernor.ClassifierFunction"></a>

This procedure creates a resource governor classifier function in master database that routes connections to custom workload groups based on specified criteria (user name, database, host, or application name). If resource governor is enabled and a classifier function is specified in the resource governor configuration, then the function output determines the workload group used for new sessions. In the absence of a classifier function, all sessions are classified into the `default` group.

**Features:**
+ Supports up to two workload groups with their respective routing conditions.
+ Combines criterion with `AND` conditions within each group.
+ Requires at least one routing criterion per workload group.
+ Function name must start with `rg_classifier_`.
+ Default group assignment if no conditions match.

The classifier function has the following characteristics and behaviors:
+ The function is defined in the server scope (in the master database).
+ The function is defined with schema binding.
+ The function is evaluated for every new session, even when connection pooling is enabled.
+ The function returns the workload group context for the session. The session is assigned to the workload group returned by the classifier for the lifetime of the session.
+ If the function returns NULL, default, or the name of a nonexistent workload group, the session is given the default workload group context. The session is also given the default context if the function fails for any reason.
+ You can create multiple classifier functions. However, SQL Server allows only one classifier function to be registered at a time.
+ The classifier function can't be dropped unless its classifier status is removed using the de-register procedure (`EXEC dbo.msdb.rds_alter_resource_governor_configuration @deregister_function = 1;`) that sets the function name to NULL or another classifier function is registered using (`EXEC dbo.msdb.rds_alter_resource_governor_configuration @classifier_function = <function_name>;`)
+ In the absence of a classifier function, all sessions are classified into the default group.
+ 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 `rg_classifier_a` and `rg_classifier_b`.

**Usage**

```
EXEC msdb.dbo.rds_create_classifier_function 
@function_name = value,
@workload_group1 = value, 
@user_name1 = value,
@db_name1 = value,
@host_name1 = value, 
@app_name1 = value, 
@workload_group2 = value,
@user_name2 = value,
@db_name2 = value,
@host_name2 = value,
@app_name2 = value
```

The following parameters are required:
+ `@function_name` - Name of the classifier function. Must start with `rg_classifier_`
+ `@workload_group1` - Name of the first workload group

The following parameters are optional:

(At least one of these criteria must be specified for group 1)
+ `@user_name1` - Login name for group 1
+ `@db_name1` - Database name for group 1
+ `@host_name1` - Host name for group 1
+ `@app_name1` - Application name for group 1

(If group 2 is specified, at least one criterion must be provided)
+ `@workload_group2` - Name of the second workload group
+ `@user_name2` - Login name for group 2
+ `@db_name2` - Database name for group 2
+ `@host_name2` - Host name for group 2
+ `@app_name2` - Application name for group 2

**Note**  
System accounts, databases, applications and host are restricted.

**Examples**

Basic Example with One Workload Group:

```
/*Create a classifier to route all requests from 'PowerBI' app to workload group 
'reporting_group'*/

EXEC msdb.dbo.rds_create_classifier_function
@function_name = 'rg_classifier_a',
@workload_group1 = 'reporting_group',
@app_name1 = 'PowerBI';

--Register the classifier
EXEC msdb.dbo.rds_alter_resource_governor_configuration
@classifier_function = 'rg_classifier_a';

-- Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration

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

use master
go
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;
```

## Drop classifier function
<a name="ResourceGovernor.DropClassifier"></a>

**Usage**

```
USE [msdb]
EXEC dbo.rds_drop_classifier_function
@function_name = value;
```

The following parameter is required:
+ `@function_name` - Is the name of an existing user-defined classifier function

**Example**

```
EXEC msdb.dbo.rds_drop_classifier_function
@function_name = 'rg_classifier_b';
```

## De-register classifier function
<a name="ResourceGovernor.DeregisterClassifier"></a>

Use this procedure to de-register classifier function. After the function is de-registered, new sessions are automatically assigned to the default workload group.

**Usage**

```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration    
@deregister_function = 1;
```

For de-registration the following parameter is required:
+ `@deregister_function` must be 1

**Example**

```
EXEC msdb.dbo.rds_alter_resource_governor_configuration 
    @deregister_function = 1;
GO

-- Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;
```

## Reset statistics
<a name="ResourceGovernor.ResetStats"></a>

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 following Amazon RDS stored procedure.

**Usage**

```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration  
@reset_statistics = 1;
```

For reset stats the following parameter is required:
+ `@reset_statistics` must be 1

## Resource governor configuration changes
<a name="ResourceGovernor.ConfigChanges"></a>

When resource governor isn’t enabled, `rds_alter_resource_governor_configuration` enables resource governor. Enabling resource governor has the following results:
+ The classifier function, if any, is executed for new sessions, assigning them to workload groups.
+ The resource limits that are specified in resource governor configuration are honored and enforced.
+ The resource limits that are specified in resource governor configuration are honored and enforced.
+ Requests that existed before enabling resource governor might be affected by any configuration changes made when resource governor is enabled.
+ Existing requests, before enabling resource governor, might be affected by any configuration changes made when resource governor is enabled.
+ On RDS for SQL Server, `EXEC msdb.dbo.rds_alter_resource_governor_configuration` must be executed for any resource governor configuration changes to take effect. 

**Usage**

```
USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration
```

## Bind TempDB to a resource pool
<a name="ResourceGovernor.BindTempDB"></a>

You can bind tempdb memory optimized metadata to a specific resource pool using `rds_bind_tempdb_metadata_to_resource_pool` in Amazon RDS SQL Server version 2019 and above.

**Note**  
Memory-optimized tempdb metadata feature must be enabled before binding tempdb metadata to resource pool. To enable this feature on Amazon RDS its a static parameter `tempdb metadata memory-optimized`.

Enable the static parameter on Amazon RDS and perform a reboot without failover for the parameter to take effect:

```
aws rds modify-db-parameter-group \
    --db-parameter-group-name test-sqlserver-ee-2022 \
    --parameters "ParameterName='tempdb metadata memory-optimized',ParameterValue=True,ApplyMethod=pending-reboot"
```

**Usage**

```
USE [msdb]
EXEC dbo.rds_bind_tempdb_metadata_to_resource_pool  
@pool_name=value;
```

The following parameter is required:
+ `@pool_name` - Is the name of an existing user-defined resource pool.

**Note**  
This change also requires sql service reboot without failover to take effect, even if Memory-optimized TempDB metadata feature is already enabled.

## Unbind TempDB from a resource pool
<a name="ResourceGovernor.UnbindTempDB"></a>

Unbind tempdb memory optimized metadata from a resource pool.

**Note**  
This change also requires sql service reboot without failover to take effect

**Usage**

```
USE [msdb]
EXEC dbo.rds_unbind_tempdb_metadata_from_resource_pool
```

## Cleanup resource governor
<a name="ResourceGovernor.Cleanup"></a>

This procedure is to clean up all associated objects after you have removed the resource governor option from the option group. This disables resource governor, reverts default workload group to default settings, remove custom workload groups, resource pools, and classifier functions.

**Key features**
+ Reverts default workload group to default settings
+ Disables resource governor
+ Removes custom workload groups
+ Removes custom resource pools
+ Drops classifier functions
+ Removes tempdb resource pool binding if enabled

**Important**  
This cleanup can error out if there are active sessions on the workload group. Either wait for the active sessions to finish or terminate the active sessions as per your business requirement. It's recommended to run this during the maintenance window.   
This cleanup can error out if a resource pool was bound to tempdb and reboot without failover hasn't been taken place yet. If you bound a resource pool to tempdb or unbound a resource pool from tempdb earlier, perform a reboot without failover to make the change effective. It's recommended to run this during the maintenance window.

**Usage**

```
USE [msdb]
EXEC dbo.rds_cleanup_resource_governor
```

## Considerations for Multi-AZ deployment
<a name="ResourceGovernor.Considerations"></a>

RDS for SQL Server replicates resource governor to secondary instance in a Multi-AZ deployment. You can verify when modified and new resource governor last synchronized with the secondary instance.

Use the following query to check the `last_sync_time` of the replication:

```
SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time();
```

In the query results, if the sync time is past the resource governor updated or creation time, then the resource governor syncs with the secondary.

To perform a manual DB failover to confirm that the resource governor replicate, wait for the `last_sync_time` to update first. Then, proceed with the Multi-AZ failover.

## Considerations for read replicas
<a name="ResourceGovernor.ReadReplica"></a>
+ For SQL Server replicas in the same Region as the source DB instance, use the same option group as the source. Changes to the option group propagate to replicas immediately, regardless of their maintenance windows.
+ When you create a SQL Server cross-Region replica, RDS creates a dedicated option group for it.
+ You can't remove an SQL Server cross-Region replica from its dedicated option group. No other DB instances can use the dedicated option group for a SQL Server cross-Region replica.
+ Resource governor option is non-replicated options. You can add or remove non-replicated options from a dedicated option group.
+ When you promote a SQL Server cross-Region read replica, the promoted replica behaves the same as other SQL Server DB instances, including the management of its options.

**Note**  
When using Resource governor on a read replica, you must manually ensure that resource governor has been configured on your read replica using Amazon RDS stored procedures after the option is added to the option group. Resource governor configurations do not automatically replicate to the read replica. Also, the workload on read replica is typically different than the primary instance. Hence, it's recommended to apply the resource configuration on the replica based on your workload and instance type. You can run these Amazon RDS stored procedures on read replica independently to configure resource governor on read replica.

# Monitor Microsoft SQL Server resource governor using system views for your RDS for SQL Server instance
<a name="ResourceGovernor.Monitoring"></a>

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 following Amazon RDS stored procedure:

```
EXEC msdb.dbo.rds_alter_resource_governor_configuration  
@reset_statistics = 1;
```

## Resource pool runtime statistics
<a name="ResourceGovernor.ResourcePoolStats"></a>

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\$1resource\$1governor\$1resource\$1pools](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-resource-governor-resource-pools-transact-sql?view=sql-server-ver17).

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;
```

# Disabling Microsoft SQL Server resource governor for your RDS for SQL Server instance
<a name="ResourceGovernor.Disabling"></a>

When you disable resource governor on RDS for SQL Server, the service stops managing workload resources. Before you disable resource governor, review how this affects your database connections and configurations.

Disabling resource governor has the following results:
+ The classifier function isn't executed when a new connection is opened.
+ New connections are automatically classified into the default workload group.
+ All existing workload group and resource pool settings are reset to their default values.
+ No events are fired when limits are reached.
+ Resource governor configuration changes can be made, but the changes don't take effect until resource governor is enabled.

To disable resource governor, remove the `RESOURCE_GOVERNOR` option from its option group.

## Console
<a name="ResourceGovernor.Disabling.Console"></a>

The following procedure removes the `RESOURCE_GOVERNOR` option.

**To remove the RESOURCE\$1GOVERNOR option from its option group**

1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).

1. In the navigation pane, choose **Option groups**.

1. Choose the option group with the `RESOURCE_GOVERNOR` option (`resource-governor-ee-2022` in the previous examples).

1. Choose **Delete option**.

1. Under **Deletion options**, choose **RESOURCE\$1GOVERNOR** for **Options to delete**.

1. Under **Apply immediately**, choose **Yes** to delete the option immediately, or **No** to delete it during the next maintenance window.

1. Choose **Delete**.

## CLI
<a name="ResourceGovernor.Disabling.CLI"></a>

The following procedure removes the `RESOURCE_GOVERNOR` option.

**To remove the RESOURCE\$1GOVERNOR option from its option group**
+ Run one of the following commands.  
**Example**  

  For Linux, macOS, or Unix:

  ```
  aws rds remove-option-from-option-group \
      --option-group-name resource-governor-ee-2022 \
      --options RESOURCE_GOVERNOR \
      --apply-immediately
  ```

  For Windows:

  ```
  aws rds remove-option-from-option-group ^
      --option-group-name resource-governor-ee-2022 ^
      --options RESOURCE_GOVERNOR ^
      --apply-immediately
  ```

# Best practices for configuring resource governor on RDS for SQL Server
<a name="ResourceGovernor.BestPractices"></a>

To control resource consumption, RDS for SQL Server supports Microsoft SQL Server resource governor. The following best practices help you avoid common configuration issues and optimize database performance.

1. Resource governor configuration is stored in the `master` database. We recommend that you always save a copy of resource governor configuration scripts separately.

1. The classifier function extends login processing time hence it's recommended to avoid complex logic in the classifier. An overly complex function can cause login delays or connection timeouts including Amazon RDS automation sessions. This can impact the ability of Amazon RDS automation to monitor the instance health. Hence, it's always recommended to test the classifier function in a pre-production environment before implementing in production environments.

1. Avoid setting high values (above 70) for `REQUEST_MAX_MEMORY_GRANT_PERCENT` in workload groups, as this can prevent the database instance from allocating sufficient memory for other concurrent queries, potentially resulting in memory grant timeout errors (Error 8645). Conversely, setting this value too low (less than 1) or to 0 might prevent queries that need memory workspace (like those involving sort or hash operations) from executing properly in user-defined workload groups. RDS enforces these limits by restricting values to between 1 and 70 on default workload groups.

1. For binding tempdb to resource pool, after binding memory optimized tempdb metadata to a pool, the pool might reach its maximum setting, and any queries that use `tempdb` might fail with out-of-memory errors. Under certain circumstances, the SQL Server could potentially stop if an out-of-memory error occurs. To reduce the chance of this happening, set the memory pool's `MAX_MEMORY_PERCENT` to a high value.