Optimize database performance using resource governor on Amazon RDS for SQL Server

You can now use resource governor with Amazon Relational Database Service (Amazon RDS) for SQL Server Enterprise Edition to optimize your database performance by controlling how compute resources are allocated across different workloads. This feature helps you manage CPU, memory, and I/O resources at a granular level, helping prevent performance bottlenecks and maintain consistent response times for your mission-critical applications. For example, you can limit resource-intensive reporting queries from impacting your core transaction processing workloads or make sure each tenant in a multi-tenant environment receives their allocated share of resources.

This post shows you how to optimize your database performance using resource governor on Amazon RDS for SQL Server. We walk you through the step-by-step process of enabling and configuring the feature, including how to set up resource pools, create workload groups, and implement classifier functions for effective resource management. Through a detailed real-world example, we demonstrate how resource governor helps resolve performance challenges in multi-tenant environments where databases share compute resources.

Business value and use cases

Resource governor provides significant operational and cost benefits:

• Cost-efficiency – Optimize resource usage and reduce infrastructure costs by precisely allocating database resources where needed
• Performance predictability – Meet service-level agreements (SLAs) by supporting consistent performance for business-critical workloads
• Multi-tenancy support – Maintain reliable performance isolation between different applications and customer workloads
• Operational stability – Protect production workloads from resource-intensive operations

You can use resource governor to isolate reporting workloads from online transaction processing (OLTP) operations, manage batch processing during peak hours, and facilitate fair resource distribution across multiple customer environments in software as a service (SaaS) scenario. It also prevents long-running queries from degrading overall system performance and monitors resource consumption patterns across different workloads.

Solution overview

Resource governor consists of three core components that work together to manage your database resources:

• Resource pool – A resource pool represents a container for the physical resources of the server, such as CPU, memory, and I/O. Depending on configuration, resources in a resource pool can be shared with other pools or reserved. For more information, see Resource governor resource pool.
• Workload group – A workload group represents a container for sessions that are classified in the same way. A workload group allows for aggregate monitoring of session and request resource consumption, and defines request policies. Each workload group is in a resource pool. For more information, see Resource governor workload group.
• Classifier function – The classification process assigns incoming sessions to a workload group based on the attributes of the session, such as user name, application name, database name, or host name. After a session is classified into a workload group, requests executing on that session are subject to the workload group policies. For more information, see Resource governor classifier function.

The following diagram shows the workflow of resource governor in Amazon RDS for SQL Server. When a session connects to your database, the classifier function evaluates it against your defined criteria and routes it to the appropriate workload group. Each workload group draws resources from its designated resource pool based on your configured policies. This structure gives you precise control over how your database resources are allocated to different workloads.

To enable resource governor in Amazon RDS for SQL Server, you must configure an option group and then use RDS stored procedures for management. In this post, we walk through the following high-level steps:

1. Create or modify an existing option group.
2. Add the RESOURCE_GOVERNOR option to the option group.
3. Associate the option group with your DB instance.

Note: Adding the RESOURCE_GOVERNOR option to your option group does not require a DB instance restart. Once the option group synchronization is complete, you can immediately use RDS stored procedures to create the required Resource Governor objects and fully enable Resource Governor at the database engine level.

Prerequisites

To set up this solution, you should have the following prerequisites:

• Basic understanding about resource governor in Microsoft SQL Server
• RDS for SQL Server instance running a supported version of SQL Server Enterprise edition

We have used AWS Command Line Interface (AWS CLI) commands in a macOS terminal for the following code examples. To use these examples, you must have the AWS CLI installed and configured. See Getting started with the AWS CLI for more information.

Unless otherwise stated, all examples have unix-like quotation rules. These examples must be adapted to your terminal’s quoting rules. For more details, see Using quotation marks and literals with strings in the AWS CLI.

Create option group for RESOURCE_GOVERNOR

Create the option group using the AWS CLI with the following code:

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"

Add resource governor to option group

The following code adds the resource governor option to the option group:

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

Associate option group with DB instance

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:

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 apply resource governor to an existing RDS instance, use the following code:

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

Real-world use case: Managing multi-tenant dashboard workloads

Consider a common scenario in enterprise environments where organizations face performance challenges with their business intelligence (BI) and analytics platforms. Multiple users access dashboards containing various data visualization components simultaneously, creating sudden spikes in database activity.

This scenario typically presents several performance issues:

• Resource bottlenecks – Multiple visualization tiles loading simultaneously create sudden spikes in concurrent database queries
• Cross-application impact – Performance degradation affects other critical services, including core business applications
• Unpredictable workload patterns – Analytics workloads are often bursty and can overwhelm available database resources

To address these performance challenges, you can implement resource governor using two primary strategies that work together to provide comprehensive workload management.

Resource management

At the core of resource governor implementation is the configuration of dedicated resource pools to manage different types of workloads and facilitate optimal resource allocation across various applications.

The following code demonstrates how to create resource pools with specific CPU allocation limits for different workload types:

EXEC msdb.dbo.rds_create_resource_pool
@pool_name = 'main_pool',
@MAX_CPU_PERCENT = 60;

EXEC msdb.dbo.rds_alter_resource_governor_configuration;

EXEC msdb.dbo.rds_create_resource_pool
@pool_name = 'analytics_pool',
@MAX_CPU_PERCENT = 30;

EXEC msdb.dbo.rds_alter_resource_governor_configuration;

This configuration creates two distinct resource pools: one for main operations with 60% CPU allocation, and another for analytics workloads with 30% CPU allocation, so analytics operations can’t overwhelm the system.

Workload classification

Effective workload classification makes sure different types of database operations are properly categorized and assigned to appropriate resource pools based on their characteristics and requirements.

The following code shows how to create workload groups and associate them with the previously created resource pools:

EXEC msdb.dbo.rds_create_workload_group
@group_name = 'main_group',
@pool_name = 'main_pool';

EXEC msdb.dbo.rds_alter_resource_governor_configuration;

EXEC msdb.dbo.rds_create_workload_group
@group_name = 'analytics_group',
@pool_name = 'analytics_pool';

EXEC msdb.dbo.rds_alter_resource_governor_configuration;

The classifier function determines how incoming database sessions are assigned to workload groups based on session attributes. The following code showcases a composite classification strategy that differentiates primary application users from analytics users based on user accounts and database associations:

-- Create the classifier function with user-based classification

EXEC msdb.dbo.rds_create_classifier_function
@function_name = 'rg_classifier_user_based',
@workload_group1 = 'main_group',
@user_name1 = 'main_user',
@db_name1 = 'main',
@workload_group2 = 'analytics_group',
@user_name2 = 'analytics_user';

-- Register and apply the classifier function
EXEC msdb.dbo.rds_alter_resource_governor_configuration
@classifier_function = 'rg_classifier_user_based';

-- Validate the configuration
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;

This configuration routes the following:

• Sessions from main_user connecting to the main database are routed to the main_group workload group
• Sessions from analytics_user are routed to the analytics_group workload group
• All other sessions are routed to the default workload group

Troubleshooting common issues

If your resource limits aren’t being enforced, you can use the following to troubleshoot:

• Verify resource governor is enabled and the classifier function is 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;

• Verify workload groups are present:

  SELECT * FROM sys.resource_governor_workload_groups;

• Verify resource pool limits:

  SELECT * FROM sys.resource_governor_resource_pools;

Clean up

To de-register the classifier function, use the following code:

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

-- Apply changes
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

-- Verify the function is de-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;

To completely remove resource governor from your RDS instance, use the following code:

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

Removing the RESOURCE_GOVERNOR option only disables the feature. All resource governor objects (resource pools, workload groups, and classifier functions) remain in the primary database. To completely clean up these artifacts, run the following cleanup script:

EXEC msdb.dbo.rds_cleanup_resource_governor;

This cleanup can error out if there are active sessions on the workload group. Either wait for the active sessions to finish or stop the active sessions according to your business requirement. It’s recommended to run this during the maintenance window.

This cleanup can also 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 a maintenance window.

Conclusion

In this post, we showed how resource governor in Amazon RDS for SQL Server helps you manage database workloads and prevent resource contention. We demonstrated how to use resource pools and workload classification to control resource-intensive queries, optimize I/O operations, and maintain consistent performance across your applications. The solution’s flexible classification system lets you implement resource management strategies that align with your specific needs—from basic workload separation to sophisticated multi-criteria resource allocation.

To get started with resource governor in your environment, review the implementation steps we covered, identify your critical workloads that need resource protection, plan your resource pool and workload group structure, and configure classification rules based on your application patterns. For additional details about configuration options and best practices, see Microsoft SQL Server resource governor with RDS for SQL Server.

About the authors