The Resource Governor is one of those rarely used tools, as its impact is inherently ‘negative’. It restricts resources from a user-group, but cannot be seen to boost performance of a more favoured group.
For example, you could restrict the SA login to use just 5% of the CPU resource. The effect of this would be that SA can use 100% of the CPU resource, until another user-group wants to use more CPU than is currently available. SA will then be throttled back, and back, until it is only using 5%. Five percent is the minimum SA can use.
It is not easy to see this throttling in action – without firing up perf-mon. Also, the GUI view within SSMS is not-so-good, showing only some of the configuration. Therefore this is one of those things that I work with completely at the code level.
So, here then is my crib-sheet …
-- ResPool.sql -- 1 create custom resource pool of available hardware USE master; GO CREATE RESOURCE POOL MxResourcePool WITH ( MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 5 ); GO -- 2 create a group and link it to the resource pool USE master; GO CREATE WORKLOAD GROUP MxWorkloadGroup USING MxResourcePool; GO -- 3 populate the group USE master; GO CREATE FUNCTION dbo.fn_MxClassifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @wg AS sysname IF SUSER_NAME() = 'sa' SET @wg = 'MxWorkloadGroup' ELSE SET @wg = 'default' RETURN @wg END; GO ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_MxClassifier); ALTER RESOURCE GOVERNOR RECONFIGURE; ALTER RESOURCE GOVERNOR RECONFIGURE; -- check config SELECT s.session_id, s.host_name, s.program_name, s.nt_user_name, s.login_name, w.name WorkgroupAssignment, r.name ResourcePoolAssignment FROM sys.dm_exec_sessions s JOIN sys.dm_resource_governor_workload_groups w ON s.group_id = w.group_id JOIN sys.dm_resource_governor_resource_pools r ON w.pool_id = r.pool_id WHERE s.host_name IS NOT NULL ORDER BY nt_user_name-- s.session_id desc -- undo ALTER RESOURCE GOVERNOR DISABLE; DROP WORKLOAD GROUP MxWorkloadGroup; DROP RESOURCE POOL MxResourcePool; DROP FUNCTION dbo.fn_MxClassifier; ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); ALTER RESOURCE GOVERNOR RECONFIGURE;