Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Using Microsoft SQL Server resource governor for your RDS for SQL Server instance

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.

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.

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

Manage resource pool

Create resource Pool

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:

The following parameters are optional:

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

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:

At least one of the optional parameter must be specified:

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

Usage

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

The following parameter is required:

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

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

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:

The following parameters are optional:

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

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:

The following parameters are optional:

Examples

Example to Modify default workload group change REQUEST_MAX_MEMORY_GRANT_PERCENT:

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

Usage

EXEC msdb.dbo.rds_drop_workload_group    
@group_name = value

The following parameters are required:

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

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:

The classifier function has the following characteristics and behaviors:

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:

The following parameters are optional:

(At least one of these criteria must be specified for group 1)

(If group 2 is specified, at least one criterion must be provided)

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

Usage

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

The following parameter is required:

Example

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

De-register classifier function

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:

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

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:

Resource governor configuration changes

When resource governor isn’t enabled, rds_alter_resource_governor_configuration enables resource governor. Enabling resource governor has the following results:

Usage

USE [msdb]
EXEC dbo.rds_alter_resource_governor_configuration

Bind TempDB to a resource pool

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.

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:

Unbind TempDB from a resource pool

Unbind tempdb memory optimized metadata from a resource pool.

Usage

USE [msdb]
EXEC dbo.rds_unbind_tempdb_metadata_from_resource_pool

Cleanup resource governor

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

Usage

USE [msdb]
EXEC dbo.rds_cleanup_resource_governor