SQL Performance: Resource Governance

1. Introduction to Resource Governance

Effective resource governance ensures that critical applications and services receive the necessary CPU, memory, and I/O resources, preventing resource starvation for important tasks due to uncontrolled consumption by less critical ones. This helps maintain a predictable and stable performance environment.

2. Resource Governor Overview

Resource Governor enables administrators to define policies for resource allocation. It works by grouping incoming user requests (workloads) into workload groups, which are then mapped to resource pools. Each resource pool is assigned specific resource limits and settings.

Key components include:

• Resource Pools: Containers for managing resources. Default pools include Internal (for SQL Server system tasks) and Default (for unclassified workloads). You can create custom pools like FinancePool or ReportingPool.
• Workload Groups: Group similar requests. A workload group is associated with a single resource pool.
• Classifier Functions: User-defined functions that determine which workload group a request belongs to based on criteria like login name, application name, or IP address.

3. Managing Workload Groups

Workload groups define the resource governance settings for requests within them. You can configure settings such as:

• CPU: Maximum CPU percentage, minimum CPU percentage, importance.
• Memory: Minimum memory percentage, maximum memory percentage.
• I/O: Disk reads per second, disk writes per second.

Example SQL Syntax:

CREATE WORKLOAD GROUP SalesWorkload
WITH (
    RESOURCE_POOL = 'SalesPool',
    CAP_CPU_PERCENT = 70,
    CAP_MEMORY_GRANT_PERCENT = 50,
    REQUEST_MAX_CPU_TIME_SEC = 300,
    REQUEST_MAX_MEMORY_GRANT_MB = 1024
);
                

4. Configuring Resource Pools

Resource pools are the actual containers that manage the allocated resources. You can create new pools or modify existing ones.

Example SQL Syntax:

CREATE RESOURCE POOL SalesPool
WITH (
    MAX_MEMORY_PERCENT = 30,
    MIN_MEMORY_PERCENT = 10,
    MAX_CPU_PERCENT = 80
);
                

When creating or altering a resource pool, consider the overall system resources and the needs of different workloads.

5. Creating Classifier Functions

Classifier functions are T-SQL functions that inspect session or request properties and return the name of the workload group to which the session should be assigned. This is the core of directing traffic to appropriate resource pools.

Example Classifier Function:

CREATE FUNCTION fn_SalesClassifier()
RETURNS VARCHAR(128)
AS
BEGIN
    DECLARE @workload_group VARCHAR(128);

    IF(SUSER_SNAME() = 'SalesUser')
        SET @workload_group = 'SalesWorkload';
    ELSE IF APP_NAME() LIKE 'SalesApp%'
        SET @workload_group = 'SalesWorkload';
    ELSE
        SET @workload_group = 'Default'; -- Assign to default if no specific match

    RETURN @workload_group;
END;
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_SalesClassifier);
GO

ENABLE RESOURCE GOVERNOR;
GO
                

6. Monitoring and Troubleshooting

Regular monitoring is crucial to ensure Resource Governor is working as intended and to identify potential bottlenecks. Key Dynamic Management Views (DMVs) include:

• sys.dm_resource_governor_resource_pools: Information about resource pools.
• sys.dm_resource_governor_workload_groups: Information about workload groups.
• sys.dm_exec_requests: Can show information related to resource grants.

Use SQL Server Management Studio's Activity Monitor or custom queries against these DMVs to track resource usage by workload group and pool.

Troubleshooting Tips:

• Verify classifier function logic.
• Check if workloads are correctly assigned to groups.
• Monitor CPU and memory grants for sessions within restricted groups.
• Ensure resource pool settings align with expected demands.