Michael J. Swart

When I deploy database changes, I like my scripts to be quick, non-blocking, rerunnable and resumable. I’ve discovered that:

• Turning on Resource Governor is quick and online
• Turning off Resource Governor is quick and online
• Cleaning or removing configuration is easy
• Modifying configuration may take some care

Turning on Resource Governor

Just like sp_configure, Resource Governor is configured in two steps. The first step is to specify the configuration you want, the second step is to ALTER RESOURCE GOVERNOR RECONFIGURE.
But unlike sp_configure which has a “config_value” column and a “run_value” column, there’s no single view that makes it easy to determine what values are configured, and what values are in use. It turns out that the catalog views are the configured values and the dynamic management views are the current values in use:

Catalog Views (configuration)

• sys.resource_governor_configuration
• sys.resource_governor_external_resource_pools
• sys.resource_governor_resource_pools
• sys.resource_governor_workload_groups

Dynamic Management Views (running values and stats)

• sys.dm_resource_governor_configuration
• sys.dm_resource_governor_external_resource_pools
• sys.dm_resource_governor_resource_pools
• sys.dm_resource_governor_workload_groups

When a reconfigure is pending, these views can contain different information and getting them straight is the key to writing rerunnable deployment scripts.

Turning on Resource Governor (Example)

Despite Erik Darling’s warning, say you want to restrict SSMS users to MAXDOP 1:

Plot a Course

use master;
 
IF NOT EXISTS (
	SELECT *
	FROM sys.resource_governor_resource_pools
	WHERE name = 'SSMSPool'
)
BEGIN
	CREATE RESOURCE POOL SSMSPool;
END
 
IF NOT EXISTS (
	SELECT *
	FROM sys.resource_governor_workload_groups
	WHERE name = 'SSMSGroup'
)
BEGIN
	CREATE WORKLOAD GROUP SSMSGroup 
	WITH (MAX_DOP = 1)
	USING SSMSPool;
END
 
IF ( OBJECT_ID('dbo.resource_governor_classifier') IS NULL )
BEGIN
	DECLARE @SQL NVARCHAR(1000) = N'
CREATE FUNCTION dbo.resource_governor_classifier() 
	RETURNS sysname 
	WITH SCHEMABINDING
AS
BEGIN
 
	RETURN 
		CASE APP_NAME()
			WHEN ''Microsoft SQL Server Management Studio - Query'' THEN ''SSMSGroup''
			ELSE ''default''
		END;
END';
	exec sp_executesql @SQL;
END;
 
IF NOT EXISTS (
	SELECT *
	FROM sys.resource_governor_configuration /* config */
	WHERE classifier_function_id = OBJECT_ID('dbo.resource_governor_classifier') )
   AND OBJECT_ID('dbo.resource_governor_classifier') IS NOT NULL
BEGIN
	ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.resource_governor_classifier); 
END

And when you’re ready, RECONFIGURE:

Make it so

IF EXISTS (
	SELECT *
	FROM sys.dm_resource_governor_configuration
	WHERE is_reconfiguration_pending = 1
) OR EXISTS (
	SELECT *
	FROM sys.resource_governor_configuration
	WHERE is_enabled = 0
)
BEGIN
	ALTER RESOURCE GOVERNOR RECONFIGURE;
END
GO

Turning off Resource Governor

Pretty straightforward, the emergency stop button looks like this:

ALTER RESOURCE GOVERNOR DISABLE;

If you ever find yourself in big trouble (because you messed up the classifier function for example), use the Dedicated Admin Connection (DAC) to disable Resource Governor. The DAC uses the internal workload group regardless of how Resource Governor is configured.

After you’ve disabled Resource Governor, you may notice that the resource pools and workload groups are still sitting there. The configuration hasn’t been cleaned up or anything.

Cleaning Up

Cleaning up doesn’t start out too bad, deal with the classifier function, then drop the groups and pools:

ALTER RESOURCE GOVERNOR DISABLE
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); 
DROP FUNCTION IF EXISTS dbo.resource_governor_classifier;
 
IF EXISTS (
	SELECT *
	FROM sys.resource_governor_workload_groups
	WHERE name = 'SSMSGroup'
)
BEGIN
	DROP WORKLOAD GROUP SSMSGroup;
END
 
IF EXISTS (
	SELECT *
	FROM sys.resource_governor_resource_pools
	WHERE name = 'SSMSPool'
)
BEGIN
	DROP RESOURCE POOL SSMSPool;
END

You’ll be left in a state where is_reconfiguration_pending = 1 but since Resource Governor is disabled, it doesn’t really matter.

Modifying Resource Governor configuration

This is kind of a tricky thing and everyone’s situation is different. My advice would be to follow this kind of strategy:

• Determine if the configuration is correct, if not:
• Turn off Resource Governor
• Clean up
• Configure correctly (plot a course)
• Turn on (make it so)

Somewhere along the way, if you delete a workload group that some session is still using, then ALTER RESOURCE GOVERNOR RECONFIGURE may give this error message:

Msg 10904, Level 16, State 2, Line 105
Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools.
Disconnect all active sessions in the affected workload groups and try again.

You have to wait for those sessions to end (or kill them) before trying again. But which sessions? These ones:

SELECT 
	dwg.name [current work group], 
	dwg.pool_id [current resource pool], 
	wg.name [configured work group], 
	wg.pool_id [configured resource pool],
	s.*
FROM 
	sys.dm_exec_sessions s
INNER JOIN 
	sys.dm_resource_governor_workload_groups dwg /* existing groups */
	ON dwg.group_id = s.group_id
LEFT JOIN 
	sys.resource_governor_workload_groups wg /* configured groups */
	ON wg.group_id = s.group_id
WHERE 
	isnull(wg.group_id, -1) <> dwg.pool_id
ORDER BY 
	s.session_id;

If you find your own session in that list, reconnect.
Once that list is empty feel free to try again.