Implementing the Resource Governer

The resource governor allows you to throttle how much resource is spent per process or per user. You may for example only want the ReportingUser to utilize upto 20% of the CPU.

By default, the recourse governor is disable, before you can implement it, you need to enable it.

ALTER RESOURCE GOVERNOR RECONFIGURE;

In this example, I'm going to configure the resource governor to recognize only two types of user, a power user or sysadmin, and a typical SQL User. The theory being we only want power users to have full resources available to them; regular users will be throttled.

We create two 'Resource Pools' - in these pools we specify the resource settings, that is how much CPU or Memory to assign or throttle.


CREATE RESOURCE POOL ResourceDefaultPool
WITH
(
MAX_CPU_PERCENT = 35,
MIN_CPU_PERCENT = 05
)

CREATE RESOURCE POOL ResourceAdminPool
WITH
(
MAX_CPU_PERCENT = 95,
MIN_CPU_PERCENT = 05
)

Then we create two 'Workload Groups' these are mapped to a 'Resource Pool' and additionally specify the priority for that group.

CREATE WORKLOAD GROUP ResourceDefault
WITH
(
IMPORTANCE = MEDIUM
) USING ResourceDefaultPool

CREATE WORKLOAD GROUP ResourceAdmin
WITH
(
IMPORTANCE = HIGH
) USING ResourceAdminPool

The Resource Governor will determine the amount of resource available to each user based on a master function that will classify the incoming requests, based on their user name. This function is called ResourceClassifier, but it can be called anything you like.

Note how the classifier picks up the username, and the function returns a Resource Group name, depending on the username.

CREATE FUNCTION dbo.ResourceClassifier() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @GroupName AS sysname

IF (SUSER_NAME() = 'sa')
SET @GroupName = 'ResourceAdmin'
ELSE
SET @GroupName = 'ResourceDefault'

RETURN @GroupName
END

Now we can implement the resource governer, and have it use our ResourceClassifier() function.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

With this implemented, we have set up two types of user a power user (sa) and a regular user, you should notice from the resource pools that the regular user can only utilize 35% of the CPU resource at MOST, and the power user can acquire up to 95%

These values are not intended as suggestions, and are purely for demonstrative purposes, as usual your mileage will probably need to vary depending on the environment.

You can disable the Resource Governor with

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO