Craft and Control Resource Pools on MSSQL

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 underused but powerful feature in SQL Server.
With a simple function and resource pool, you can dramatically improve stability and predictability in multi-user environments.

In this post, I’ll show how to use SQL Server Resource Governor to,

• Detect connections coming from SSMS,
• Assign them to a limited workload group,
• Restrict their CPU and memory usage.

What is Resource Governor ?
Resource Governor is a SQL Server feature that allows you to control how much CPU and memory different workloads can consume.
This approach is especially useful for production safety, or DBA-controlled access.

After this setup any query executed from Microsoft SQL Server Management Studio will be assigned to a limited resource pool.
All other connections remain in the default pool.

This also means you can customize resource pools to suit your specific needs by modifying the function below.

You can control CPU, RAM resources for:

• Specific SQL users,
• Reporting or BI tools,
• ETL jobs,
• Ad-hoc users,
• Third-party applications,
• Nightly or background workloads. All you need to do is modify the function and route those sessions into the appropriate workload group.

Let's begin with example on limiting CPU & RAM for sessions comming from SSMS Studio,
1. Enable Governor.

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

2. Create your function.
This will 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

Also, if you want to restrict specific SQL users, you can easily modify the this function by adding a additional condition like this:

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

This allows you to route specific users to a resource pool with customized limits.

3. Create a Resource Pool.
This ensures SSMS queries can never consume more than 30% CPU, 30% memory.

CREATE RESOURCE POOL [LimitedResourcePool]
WITH
(min_cpu_percent=0,
max_cpu_percent=30,
min_memory_percent=0,
max_memory_percent=30
)

4. Create a Workload Group.
Now we link a workload group to the limited resource pool.

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

5. Enable the Classifier Function.
Finally, tell Resource Governor to use our function and reconfigure it.

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

Now you check what you have created with these commands,

SELECT * FROM sys.resource_governor_configuration
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups

Keep in mind that,

• These changes apply only to new sessions, not to existing ones. You should restart the SQL service or terminate sessions for changes to affect.
• Resource Governor is available in SQL Server Standard Edition starting with SQL Server 2025. For all previous versions, it is an Enterprise-only feature.

To monitor which sessions are currently using your resource pool, you can run the following query. This will help you ensure that the sessions are properly classified and using the intended resources.

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;

Now go ahead and put those resource pools to work.
I hope this helps you keep your SQL Server running smoothly!
Don’t miss my other posts for more tips and best practices.