Resource Governor: What, How to Configure and Monitor

Resource Governor:  

RG is a new feature introduced in SQL2008, this is basically controlling/allocating resources like CPU, Memory to specific user/applications. For example on a SQL instance you have 3 application databases, one is critical and you want to give allocate some default percentage of resources all the time, other example is Reporting, the Resource Governor allows

you to specify that no more than 20% (or any figure you designate) of CPU and/or memory

resources can be allocated to running reports. When this option is turned on, then no matter how many reports are run, they can never exceed their designated resource allocation.

RG has mainly 3 parts.

1.     Resource Pool

A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.

A pool has two parts. One part does not overlap with other pools, which enables minimum resource reservation. The other part is shared with other pools, which supports maximum possible resource consumption. In this release of Resource Governor the pool resources are set by specifying one of the following for each resource:

·         MIN or MAX for CPU

·         MIN or MAX for memory

MIN and MAX represent the minimum guaranteed resource availability of the pool and the maximum size of the pool, respectively, for each of these resources.

The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value can be set anywhere in the range between MIN and 100 percent inclusive.

If a pool has a nonzero MIN defined the effective MAX value of other pools is readjusted as the minimum of the configured MAX value of a pool and the sum of the MIN values of other pools subtracted from 100 percent.

2.     WorkLoad:

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request. A workload group allows the aggregate monitoring of resource consumption and the application of a uniform policy to all the requests in the group. A group defines the policies for its members.

Resource Governor predefines two workload groups: the internal group and the default group. A user cannot change anything classified as an internal group, but can monitor it. Requests are classified into the default group when the following conditions exist:

·         There are no criteria to classify a request.

·         There is an attempt to classify the request into a non-existent group.

·         There is a general classification failure.

Resource Governor also provides DDL statements for creating, changing, and dropping workload groups. For more information,

3.     Classification Function

the classification user-defined function that specifies which connections will be put inside each of the workload groups created in the previous step. Only one user-defined classification function can be created for each SQL Server instance, so when creating your own user-defined classification function, it is important to consider all the various connections that can be made to a SQL Server instance, and deal with them appropriately by assigning them to an appropriate workload group (and its associated resource pool). If a connection is not assigned to a specific workload group (and pool) by the classification function, then the connection goes to a default workload group (and pool). See Books Online to learn how to properly configure a default workload group and pool.

the movement when we enable RG, Classification functions listens 24*7 and it will route the all

Incoming connections to appropriate pools accordingly

Configuring RG:

Unlike many other features in SQL Server, you cannot use SSMS to entirely configure and manage Resource Governor. You can either use a combination of Transact-SQL and SSMS, or you can use Transact-SQL only. To keep things as simple as possible, all of the following examples will be demonstrated using Transact-SQL only.

Creating Resource Group

--The master database must be used to configure the Resource Governor

USE MASTER

GO

--Create Resource Pool to Limit Resources for Critical applications

CREATE RESOURCE POOL poolCritical

WITH

(

MAX_CPU_PERCENT=100,

MAX_MEMORY_PERCENT=100,

MIN_CPU_PERCENT=70,

MIN_MEMORY_PERCENT=70

);

GO

--Create Resource Pool to Limit Resources for Normal applications

CREATE RESOURCE POOL poolNormal

WITH

(

MAX_CPU_PERCENT=30,

MAX_MEMORY_PERCENT=30,

MIN_CPU_PERCENT=10,

MIN_MEMORY_PERCENT=10

);

GO

Creating workload Groups:-

--Create Workload Group to Hold Connections From Critical Application and Assign Resource Pool

CREATE WORKLOAD GROUP wrkgroupCritical

USING poolCritical

GO

--Create Workload Group to Hold Connections From Normal Application and Assign Resource Pool

CREATE WORKLOAD GROUP wrkgroupNormal

USING poolNormal;

GO

Creating the Classification Function:-

--Create Classification User-Defined Function

CREATE FUNCTION rgclassifier1() RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @grp_name AS SYSNAME

IF (APP_NAME() = 'ME2ME')

SET @grp_Name = 'wrkgroupCritical'

ELSE if(App_name()='GKPRD')

set @grp_Name = 'wrkgroupCritical'

else if (App_name()='Kate_Audit')

set @grp_Name = 'wrkgroupCritical'

else

SET @grp_Name = 'wrkgroupNormal'

RETURN @grp_name

END;

Enabling Resource Governor:-

-- Register the classifier function with Resource Governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier1);

GO

Second, we need to run the following code, which enables the Resource Governor.

-- Start Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

Monitoring RG:

You can also monitor the CPU resources being allocated by SQL Server via the System Monitor (perfmon.exe) tool. Note that when you add counters that you want to monitor, the SQLServer

:Resource Pool Stats object shows instances of each of the configured resource pools, as shown in Figure 6

Similar instance-related selections have been added to SQLServer:Workload Group Stats counter. Relevant values can also be obtained by querying the sys.dm_os_performance_counters view.

To enable more detailed monitoring, additional events have been added to SQL Server, including the CPU Threshold Exceeded, PreConnect:Starting, and PreConnect:Completed events. If you want to control Resource Governor programmatically using .NET code, a ResourceGovernor class has been added to SQL Server Management Objects (SMO).

For the purposes of this article, I focused on maximum CPU percentages when configuring resource pools because you must be very careful about how you configure minimum percentages. It’s easy to greatly limit the resources on a system by configuring the minimum values too high, particularly minimum percentages for memory.