Disable Resource Governor: Why RECONFIGURE Turns It On

To disable Resource Governor, run ALTER RESOURCE GOVERNOR DISABLE, and remember that ALTER RESOURCE GOVERNOR RECONFIGURE turns it on. That one word is how the feature gets enabled by accident.

How It Got Enabled

A client struggled with memory problems, and the server ran slowly. The cause was Resource Governor. One of their administrators had run ALTER RESOURCE GOVERNOR RECONFIGURE, believing the statement only configured the feature. The statement contains no word like enable, so the administrator moved on to other work and never came back.

Nothing else explained the slowness. I checked Resource Governor as a test of the theory and disabled it. Performance came back at once. The statement that disables it is short, and the lesson is to check the state before and after every change.

Read the State First

Before you disable Resource Governor, read its state. Two catalog queries show what it does on a server. The first reads the saved state and the classifier function, the function that sorts new sessions into workload groups. A classifier ID of 0 means there is none.

SELECT c.is_enabled AS IsEnabled, c.classifier_function_id AS ClassifierFunctionId,
       OBJECT_NAME(c.classifier_function_id, 1) AS ClassifierFunction
FROM sys.resource_governor_configuration AS c;

On the test server, Resource Governor is off and has no classifier. The second query lists the resource pools and their limits.

SELECT p.name, p.min_cpu_percent, p.max_cpu_percent, p.min_memory_percent, p.max_memory_percent
FROM sys.resource_governor_resource_pools AS p
ORDER BY p.pool_id;

Both pools allow 0 to 100 percent, so a default setup limits nothing. Trouble starts when someone has saved limits in a pool or a classifier function.

The memory columns matter most. A minimum reserves memory for its pool, and other pools cannot use it. A pool with a high minimum can squeeze the default pool, where every session lands when no classifier function exists. Those settings stay stored in master while the feature is off. The first RECONFIGURE turns every one of them on at once. A memory limit in a pool then caps the memory grants of its queries.

Enable and Disable on a Test Server

To disable Resource Governor safely, test the statements first. The next script records the state in three steps. It runs RECONFIGURE, then DISABLE, and it restores the state it found. If Resource Governor is already enabled on your server, the script only reads and changes nothing. The script ends with the feature disabled again. If saved pool limits exist, they apply between the two statements, so run it only on a test server. Each statement is the undo of the other.

DECLARE @WasEnabled bit = (SELECT is_enabled FROM sys.resource_governor_configuration);
DECLARE @Log TABLE (Step nvarchar(40), SavedEnabled bit, ReconfigurePending bit);
INSERT @Log
SELECT N'Before', c.is_enabled, d.is_reconfiguration_pending
FROM sys.resource_governor_configuration AS c CROSS JOIN sys.dm_resource_governor_configuration AS d;
BEGIN TRY
    IF @WasEnabled = 0 ALTER RESOURCE GOVERNOR RECONFIGURE;
    INSERT @Log
    SELECT N'After RECONFIGURE', c.is_enabled, d.is_reconfiguration_pending
    FROM sys.resource_governor_configuration AS c CROSS JOIN sys.dm_resource_governor_configuration AS d;
    IF @WasEnabled = 0 ALTER RESOURCE GOVERNOR DISABLE;
    INSERT @Log
    SELECT N'After DISABLE', c.is_enabled, d.is_reconfiguration_pending
    FROM sys.resource_governor_configuration AS c CROSS JOIN sys.dm_resource_governor_configuration AS d;
END TRY
BEGIN CATCH
    IF @WasEnabled = 0 AND (SELECT is_enabled FROM sys.resource_governor_configuration) = 1 ALTER RESOURCE GOVERNOR DISABLE;
    THROW;
END CATCH;
SELECT Step, SavedEnabled, ReconfigurePending FROM @Log;

The TRY and CATCH wrapper switches the feature off again if a step fails. The middle row proves the point. RECONFIGURE switched the feature on, and DISABLE switched it off again. Neither statement needs a restart. The saved pools and groups stay in master after DISABLE, so a later RECONFIGURE brings them back. The last column stays 0 in every row. It turns to 1 when someone edits a pool and has not yet run RECONFIGURE. A 1 there means saved changes are waiting.

On your own server, the two statements below are all you need. They change a server setting. Read the state first, and note it, because the old state is your undo.

ALTER RESOURCE GOVERNOR DISABLE;

-- undo
-- ALTER RESOURCE GOVERNOR RECONFIGURE;

Check the State After Every Change

Make the check a habit. Read is_enabled before you run a Resource Governor statement, and read it again afterward. Reading the value before and after shows the 1 appear. The statement needs the CONTROL SERVER permission, so only a few people on a server can run it.

Add the state query to your regular health check. A feature that someone switched on months ago shows up in one line, long before it explains a slowdown.

You could argue that Resource Governor is worth keeping on, and I agree for the right setup. I think it works well with memory-optimized tables, and it can fence off a runaway query. Used with a plan, it is one of the best controls SQL Server has. Used by accident, it is a mystery. Have a second person review the pool and classifier settings before you enable them.

What to Remember

RECONFIGURE enables Resource Governor when it is off. To disable Resource Governor, run DISABLE, which keeps the saved settings. Read the state with the first query above, and write down what you find before you touch anything.

When a server shows memory trouble with no clear cause, check Resource Governor early. It takes one query, and a disabled feature rules the whole area out.

Resource Governor is not a setting you configure, it is a switch you can flip by accident.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.