Quick Scan Report – Resource Governor Configuration

What this check looks for

Resource Governor being enabled on the instance. It is an Enterprise Edition feature, so the check only produces a finding where it is available and turned on.

Why it matters

Resource Governor deliberately limits what queries can have. When it is on and nobody knows about it, every performance investigation starts from a false premise.

It works by classifying each incoming session into a workload group, which belongs to a resource pool. The pool sets limits on CPU, memory and I/O; the group sets limits on parallelism, memory grant percentage, and query duration. So an unexplained ceiling on a query’s performance may be a configured ceiling, and none of the usual diagnostics say so directly.

The specific ways it surprises people:

• REQUEST_MAX_MEMORY_GRANT_PERCENT limits how much of the pool’s memory a single query can be granted. A query that needs a large sort gets less than it asked for, spills to tempdb, and runs slowly. Nothing about that looks like a governor setting; it looks like a tempdb or a statistics problem.
• MAX_DOP on a workload group overrides the instance and query level settings. A query with an explicit OPTION (MAXDOP 8) will still run at the group’s limit. This is a common and baffling finding when somebody is tuning parallelism.
• CAP_CPU_PERCENT is a hard ceiling. Unlike MAX_CPU_PERCENT, which only applies under contention, a cap throttles the workload even when the server is idle. An instance at 30 percent CPU with queries queueing is exactly what that produces.
• REQUEST_MAX_CPU_TIME_SEC does not kill the query. It raises an event and lets it continue, so it is a signal rather than a limit, which is not what most people assume.
• The classifier function runs on every login. If it is slow, or if it queries a table that can be locked, every new connection waits on it. A badly written classifier can make the instance appear to stop accepting connections.

And the default pools are easy to misconfigure. Changing the default pool affects everything that is not explicitly classified, which is usually most of the workload including your own diagnostic sessions.

Resource Governor is a good feature used deliberately. Capping a reporting workload so it cannot starve the transactional one is a legitimate and effective design. The finding is not that it is on; it is that a configuration this consequential should be understood by whoever is looking at performance, and frequently is not, because it was set up for a reason that has since been forgotten.

How to confirm it yourself

Whether it is on, and what the classifier is:

SELECT [is_enabled],
       [classifier_function_id],
       OBJECT_SCHEMA_NAME([classifier_function_id]) + '.'
         + OBJECT_NAME([classifier_function_id]) AS [classifier_function],
       [is_reconfiguration_pending]
  FROM sys.resource_governor_configuration WITH (NOLOCK);

is_reconfiguration_pending of 1 means the stored configuration differs from what is running, which is its own small finding.

The pools and their limits:

SELECT [pool_id],
       [name],
       [min_cpu_percent],
       [max_cpu_percent],
       [cap_cpu_percent],
       [min_memory_percent],
       [max_memory_percent],
       [min_iops_per_volume],
       [max_iops_per_volume]
  FROM sys.resource_governor_resource_pools WITH (NOLOCK)
 ORDER BY [pool_id];

cap_cpu_percent below 100 is a hard ceiling that applies even on an idle server. That is the setting most likely to be producing an unexplained limit.

The workload groups, which is where the parallelism and memory grant limits live:

SELECT g.[name]                            AS [group_name],
       p.[name]                            AS [pool_name],
       g.[importance],
       g.[request_max_memory_grant_percent],
       g.[request_max_cpu_time_sec],
       g.[request_memory_grant_timeout_sec],
       g.[max_dop],
       g.[group_max_requests]
  FROM sys.resource_governor_workload_groups AS g WITH (NOLOCK)
 INNER JOIN sys.resource_governor_resource_pools AS p WITH (NOLOCK)
         ON p.[pool_id] = g.[pool_id]
 ORDER BY p.[name], g.[name];

max_dop of anything other than 0 on a group overrides both the instance setting and any query hint.

Read the classifier function, because it decides which sessions land where:

SELECT OBJECT_NAME([object_id]) AS [function_name], [definition]
  FROM sys.sql_modules WITH (NOLOCK)
 WHERE [object_id] = (SELECT [classifier_function_id]
                        FROM sys.resource_governor_configuration);

Where your sessions actually are right now:

SELECT s.[session_id],
       s.[login_name],
       s.[host_name],
       s.[program_name],
       g.[name] AS [workload_group],
       p.[name] AS [resource_pool]
  FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
 INNER JOIN sys.resource_governor_workload_groups  AS g WITH (NOLOCK)
         ON g.[group_id] = s.[group_id]
 INNER JOIN sys.resource_governor_resource_pools   AS p WITH (NOLOCK)
         ON p.[pool_id] = g.[pool_id]
 WHERE s.[is_user_process] = 1
 ORDER BY [resource_pool], [workload_group];

Run that from the session you are troubleshooting with. Discovering that your own diagnostic connection is in a throttled pool explains a great deal.

And what the limits are actually costing, which is the evidence:

SELECT p.[name]                       AS [pool_name],
       s.[total_cpu_usage_ms],
       s.[cache_memory_kb] / 1024     AS [cache_mb],
       s.[used_memgrant_kb] / 1024    AS [used_grant_mb],
       s.[out_of_memory_count]
  FROM sys.dm_resource_governor_resource_pools AS s WITH (NOLOCK)
 INNER JOIN sys.resource_governor_resource_pools AS p WITH (NOLOCK)
         ON p.[pool_id] = s.[pool_id];

SELECT g.[name]                            AS [group_name],
       s.[total_request_count],
       s.[total_queued_request_count],
       s.[total_suboptimal_plan_generation_count],
       s.[total_reduced_memgrant_count]
  FROM sys.dm_resource_governor_workload_groups AS s WITH (NOLOCK)
 INNER JOIN sys.resource_governor_workload_groups AS g WITH (NOLOCK)
         ON g.[group_id] = s.[group_id];

total_reduced_memgrant_count and total_suboptimal_plan_generation_count above zero are direct evidence that the governor is changing query behavior. Those two columns are the answer to “is Resource Governor affecting us”.

How to fix it

Understand it before changing it. A governor configuration that has been in place for years is probably holding something back deliberately.

1. Document what exists, from the queries above: the pools, the groups, the classifier logic and which sessions land where.
2. Establish why. Resource Governor is rarely enabled by accident, so there was a reason. The usual ones are protecting a transactional workload from reporting, capping a specific application, or limiting a backup or maintenance window.
3. Check whether the reason still applies, and whether the limits are still right for current hardware. A max_memory_percent set for a 32 GB server is a different amount on a 256 GB one.
4. Read the classifier for problems. It runs on every login, so it must be fast and must not query anything that can be blocked:

-- a classifier should look like this: no table access, no waits
CREATE FUNCTION [dbo].[fn_RGClassifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @group SYSNAME;
    IF APP_NAME() LIKE 'Reporting%'   SET @group = N'ReportingGroup';
    ELSE IF SUSER_NAME() = N'etl_user' SET @group = N'ETLGroup';
    ELSE                               SET @group = N'default';
    RETURN @group;
END;

A classifier that reads a lookup table is the classic way to lock everyone out, because a lock on that table blocks every new login.

5. Keep the DAC usable. The dedicated administrator connection bypasses the classifier, which is what gets you back in when a classifier goes wrong. That is a strong reason to have remote DAC enabled on any instance running Resource Governor.

To change a limit:

ALTER RESOURCE POOL [ReportingPool]
  WITH (MAX_CPU_PERCENT = 40, MAX_MEMORY_PERCENT = 25);

ALTER WORKLOAD GROUP [ReportingGroup]
  WITH (MAX_DOP = 4, REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);

ALTER RESOURCE GOVERNOR RECONFIGURE;

The RECONFIGURE is required. Without it, the change is stored and not applied, which is what is_reconfiguration_pending reports.

To turn it off entirely, if it is no longer wanted:

ALTER RESOURCE GOVERNOR DISABLE;

That leaves the configuration in place so it can be re-enabled, and every session returns to the internal default with no limits. Do that only once you know why it was on, because whatever it was protecting is now unprotected.

How long it takes

About half an hour to document the configuration and confirm whether it is affecting current workloads. Redesigning the pools is longer and needs a reason.

Related reports

Related checks

Frequently asked questions

Is Resource Governor a problem? No, it is a deliberate tool. The finding asks you to know it is on, because it changes what the usual performance diagnostics mean.

Why is my MAXDOP hint being ignored? A workload group’s MAX_DOP overrides both the instance setting and query hints. Check the group your session is classified into.

Why is CPU stuck at a certain percentage? Look at cap_cpu_percent on the pool. Unlike max_cpu_percent, a cap applies even when the server is otherwise idle.

How do I get in if the classifier breaks? The dedicated administrator connection bypasses the classifier. That is a good reason to enable remote DAC on any instance using Resource Governor.