Resource Governor

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
	N/A	N/A	Use the resource limit for each user.

SQL Server Usage

SQL Server Resource Governor provides the capability to control and manage resource consumption. Administrators can specify and enforce workload limits on CPU, physical I/O, and Memory. Resource configurations are dynamic and you can change them in real time.

In SQL Server 2019 configurable value for the REQUEST_MAX_MEMORY_GRANT_PERCENT option of CREATE WORKLOAD GROUP and ALTER WORKLOAD GROUP has been changed from an integer to a float data type to allow more granular control of memory limits. For more information, see ALTER WORKLOAD GROUP (Transact-SQL) and CREATE WORKLOAD GROUP (Transact-SQL) in the SQL Server documentation.

Use Cases

The following list identifies typical Resource Governor use cases:

• Minimize performance bottlenecks and inconsistencies to better support Service Level Agreements (SLA) for multiple workloads and users.

• Protect against runaway queries that consume a large amount of resources or explicitly throttle I/O intensive operations. For example, consistency checks with DBCC that may bottleneck the I/O subsystem and negatively impact concurrent workloads.

• Allow tracking and control for resource-based pricing scenarios to improve predictability of user charges.

Concepts

The three basic concepts in Resource Governor are Resource Pools, Workload Groups, and Classification.

• Resource Pools represent physical resources. Two built-in resource pools, internal and default, are created when SQL Server is installed. You can create custom user-defined resource pools for specific workload types.

• Workload Groups are logical containers for session requests with similar characteristics. Workload Groups allow aggregate resource monitoring of multiple sessions. Resource limit policies are defined for a Workload Group. Each Workload Group belongs to a Resource Pool.

• Classification is a process that inspects incoming connections and assigns them to a specific Workload Group based on the common attributes. User-defined functions are used to implement Classification. For more information, see User-Defined Functions.

Examples

Turn on the Resource Governor.

ALTER RESOURCE GOVERNOR RECONFIGURE;

Create a Resource Pool.

CREATE RESOURCE POOL ReportingWorkloadPool
    WITH (MAX_CPU_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Create a Workload Group.

CREATE WORKLOAD GROUP ReportingWorkloadGroup USING poolAdhoc;

ALTER RESOURCE GOVERNOR RECONFIGURE;

Create a classifier function.

CREATE FUNCTION dbo.WorkloadClassifier()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
    RETURN (CASE
        WHEN HOST_NAME()= 'ReportServer'
        THEN 'ReportingWorkloadGroup'
        ELSE 'Default'
    END)
END;

Register the classifier function.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.WorkloadClassifier);

ALTER RESOURCE GOVERNOR RECONFIGURE;

For more information, see Resource Governor in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) doesn’t support a server-wide, granular, resource-based, workload resource isolation and management capability similar to SQL Server Resource Governor. However, Aurora MySQL does support the feature User Resource Limit Options that you can use to achieve similar high-level functionality for limiting resource consumption of user connections.

You can specify User Resource Limit Options as part of the CREATE USER statement to place the following limits on users:

• The number of total queries in hour an account is allowed to issue.

• The number of updates in hour an account is allowed to issue.

• The number of times in hour an account can establish a server connection.

• The total number of concurrent server connections allowed for the account.

For more information, see Users and Roles.

Syntax

CREATE USER <User Name> ...
WITH
MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count

Migration Considerations

Although both SQL Server Resource Manager and Aurora MySQL User Resource Limit Options provide the same basic function — limiting the amount of resources for distinct types of workloads — they differ significantly in scope and flexibility.

SQL Server Resource Manager is a dynamically configured independent framework based on actual run-time resource consumption. User Resource Limit Options are defined as part of the security objects and requires application connection changes to map to limited users. To modify these limits, you must alter the user object.

User Resource Limit Options don’t allow limiting workload activity based on actual resource consumption, but rather provides a quantitative limit for the number of queries or number of connections. A runaway query that consumes a large amount of resources may slow down the server.

Another important difference is how exceeded resource limits are handled. SQL Server Resource Governor throttles the run; Aurora MySQL raises errors.

Example

Create a resource-limited user.

CREATE USER 'ReportUsers'@'localhost'
IDENTIFIED BY 'ReportPassword'
WITH
MAX_QUERIES_PER_HOUR 60
MAX_UPDATES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

Summary

Feature	SQL Server Resource Governor	Aurora MySQL User Resource Limit Options	Comments
Scope	Dynamic workload pools and workload groups, mapped to a classifier function.	For each user.	Application connection strings need to use specific limited users.
Limited resources	IO, CPU, and memory.	Number of queries, number of connections.
Modifying limits	ALTER RESOURCE POOL	ALTER USER	Application may use a dynamic connection string.
When resource threshold limit is reached.	Throttles and queues runs.	Raises an error.	Application retry logic may need to be added.

For more information, see CREATE USER Resource-Limit Options and Setting Account Resource Limits in the MySQL documentation.