Craft and Control Resource Pools on MSSQL

Source: Dev.to

Introduction

Time to take control of your server’s resources.

On your MS SQL instance not all workloads should be treated equally.
One heavy query executed from SQL Server Management Studio (SSMS) can easily consume CPU and memory, impacting production workloads.

Resource Governor is an under‑used but powerful feature in SQL Server. With a simple function and resource pool, you can dramatically improve stability and predictability in multi‑user environments.

What is Resource Governor?

Resource Governor is a SQL Server feature that lets you control how much CPU and memory different workloads can consume. This is especially useful for production safety or DBA‑controlled access.

After the setup, any query executed from Microsoft SQL Server Management Studio will be assigned to a limited resource pool, while all other connections remain in the default pool. You can customize resource pools to suit your specific needs by modifying the classifier function.

Typical scenarios include limiting resources for:

• Specific SQL users
• Reporting or BI tools
• ETL jobs
• Ad‑hoc users
• Third‑party applications
• Nightly or background workloads

Implementation

1. Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

2. Create the Classifier Function

The function decides which workload group a session should use.

CREATE FUNCTION [dbo].[ResourceGroup_Users]
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME;
    DECLARE @ProgramName SYSNAME;

    SET @ProgramName = CONVERT(SYSNAME, PROGRAM_NAME());

    IF @ProgramName LIKE 'Microsoft SQL Server Management Studio%'
    BEGIN
        SET @WorkloadGroup = 'LimitedResourceGroup';
    END
    ELSE
    BEGIN
        SET @WorkloadGroup = 'default';
    END

    RETURN @WorkloadGroup;
END
GO

Optional: To restrict specific SQL users, add an additional condition, e.g.:

IF SUSER_NAME() = 'testuser' THEN ...

3. Create a Resource Pool

This pool limits SSMS queries to no more than 30 % CPU and 30 % memory.

CREATE RESOURCE POOL [LimitedResourcePool]
WITH
(
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 30,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 30
);
GO

4. Create a Workload Group

Link the workload group to the limited resource pool.

CREATE WORKLOAD GROUP [LimitedResourceGroup]
USING [LimitedResourcePool];
GO

5. Enable the Classifier Function

Tell Resource Governor to use the function and reconfigure.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = [dbo].[ResourceGroup_Users]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Monitoring

Run the following query to see which sessions are using each resource pool:

SELECT
    s.session_id,
    s.login_name,
    rg.name AS resource_pool_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_resource_governor_workload_groups AS wg
    ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools AS rg
    ON wg.pool_id = rg.pool_id
WHERE s.is_user_process = 1;

Notes

• Changes apply only to new sessions. Restart the SQL service or terminate existing sessions for the changes to take effect.
• Resource Governor is available in SQL Server Standard Edition starting with SQL Server 2025; in earlier versions it is an Enterprise‑only feature.

Now go ahead and put those resource pools to work. I hope this helps you keep your SQL Server running smoothly!