Resource Governor

The feature of  SQL Server 2008 that seems to get  the most attention from DBA’s is the Resource Governor.  It basically does what it says on the tin; for example you may want to reserve a portion of CPU or other resource for a user, process etc.

At the top level Resource Governor has Resource Pools, and there is always  default resource pool

Below this you create Workload groups:

CREATE WORKLOAD GROUP groupAdhoc
CREATE WORKLOAD GROUP groupReports
CREATE WORKLOAD GROUP groupAdmin

These workload groups will be belong to the default resource pool, and for this introduction I will keep it simple, by leaving it like that. It is then a matter of assigning whatever you want to the those groups by using a function like this:

CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS SYSNAME
IF (SUSER_NAME() = ‘sa’)
SET @grp_name = ‘groupAdmin’
IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%’)
OR (APP_NAME() LIKE ‘%QUERY ANALYZER%’)
SET @grp_name = ‘groupAdhoc’
IF (APP_NAME() LIKE ‘%REPORT SERVER%’)
SET @grp_name = ‘groupReports’
RETURN @grp_name
END

Notice that you can use any rule you like to create an association with a workload group e.g. users or the application.

This function is then needs to be applied to the resource governor like this:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1)

You are now ready to configure which workload group gets which resources with this syntax:

ALTER WORKLOAD GROUP groupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30)

or

WITH (MAX_CPU_PERCENT = 50)

Finally the changes need to be applied to the resource governor process running in memory

ALTER RESOURCE GOVERNOR RECONFIGURE

Changes can be applied to the Resource Governor at will and take effect immediately.  The function can be changed to move objects in to different workload groups as required and all of this will affect processes already running.