Executive Summary:

This article is about the Resource Governor, a new feature introduced in SQL Server 2008. The article starts from the basics and leads the reader through details of the feature. Special focus is placed on the T-SQL implementation of the new feature.

What is Resource Governor?

Before SQL Server 2008, it was not possible to differentiate between different workloads in SQL Server. We could not prioritize or limit different workloads, or resource requests, in SQL Server. We did not have the option to discourage run-away queries (which can take up most of the resources of the system). With all those requirements in mind, Microsoft has introduced an interesting and exciting new feature in SQL Server 2008; it is named the Resource Governor. It allows the user to limit the CPU and memory usage by a specific application or user. It would help in preventing any particular user / application to eat up all the resources of the SQL Server.

Microsoft has provided two options to configure the resource governor. They are as follows:
- T-SQL Commands
- Object Explorer

Both of these options are also available with SQL Server Management Studio.

The Difference Between the Work Load Governor and Resource Governor

Now many readers may confuse Resource Governor with the Workload Governor, which was part of some earlier versions of SQL Server. This governor was used to limit the performance, but it was used to limit the performance of the SQL Server instance as a whole. When the number of connections used to be increased by a certain level, then it used to limit the responsiveness of the SQL Server. It was not possible to limit the resources available to any particular application or user. This is available in SQL Server 2008 in the form of Resource Governor, so we should not confuse the two of them.

Terminologies:

The Resource Governor is implemented by defining resource pools, workload groups and Classifier functions. So we must have an understanding of all of three of them. Each is described below.

Resource Pool

It is the collection of physical resources of the database management system. It is viewed as a virtual SQL Server instance within the SQL Server as visible by the application. It is defined with minimum and maximum values of resource (memory and CPU) utilization. SQL Server 2008 allows a maximum of 18 user defined pools and two built-in pools. There are following built-in resource pools in SQL Server 2008:

Internal: Used solely by the database engine. This cannot be re-configured by the user.
Default: Used by all those workloads that have not been assigned to any specific resource pool.

The database administrator may also define their own custom resource pools. For these resource pools, they may define the limit of the usage of each resource by any workload by defining minimum and maximum values of allowed resources for the particular workload. Now what value could be assigned to minimum and maximum values? The minimum values are defined in a way that sum of minimum values of each workload does not exceed 100. The max value of each can vary between the minimum value and 100.

Now it seems impossible that any workload could be assigned resources equal to its maximum value because other resource pools are working with at least their minimum resource pool, so a new term is coined called the Effective Maximum Rate. It is equal to the difference between the maximum value of said resource pool and sum of minimum values of all others.

To understand this, we consider an instance in which there are 3 custom resource pools. Now it must be understood that the internal resource pool is on the highest priority list of the server, so it is said to have Effective Max % equal to its Maximum value (100). It means that it is the task that will always have full resources of the server even if it means it violates the requirements of resources for other pools.

The shared % is the difference between Effective Max % and Minimum % for any resource pool. It is to have some idea if no workload is under process for any pool then how much of the resources can be shared by the said resource pool.

Now we would discuss how to define any resource pool. First we consider defining the resource pool using T-SQL statement. See Code Listing 1.

There are also DROP and ALTER statements available for the resource pool.

Note: It must be remembered to make any changes effective for the SQL Server Resource governor, the Resource Governor must be reconfigured by running the command: ALTER RESOURCE GOVERNOR RECONFIGURE.

You can also define new resource pool using SQL Server management studio. See Figure 1.

To define a new pool Resource Governor must be enabled. When you select 'New Resource Pool', the following form appears: (See Figure 2)

Workload Group

Now you might guess that these resource pools are then assigned to particular users. Though it seems OK for a little number of users, but for an organizational environment this would be very difficult to manage. To make this management easier for database administrator, workload groups are introduced. All workloads are assigned to some specific resource pool. A resource pool may have zero or more workload groups assigned to it.

Like built-in resource pools, there are built-in workload groups as well. They are as follows:

Internal: Assigned to the internal resource pool. Used by database engine.
Default: All those sessions that do not classify to any other workload group, are assigned to it.

CREATE WORKLOAD GROUP Workload_Name
USING ResourcePool_Assigned

ALTER and DROP statements are also available for workload group.

Classifier function

Now you may ask how each user session would be assigned to any particular workload group. Well the answer is the Classifier function. It is a normal user defined function (UDF) defined in SQL Server (scalar valued). Any property may be used to do this job like IP Address, Application Name and Username etc. See Code Listing 2.

Assigning Classifier function to Resource Governor:
How would Resource Governor know which classifier function to use. For that a classifier function has to be assigned to the resource governor.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.My_Classifer_func)

Enabling Resource Governor:
There are two states of Resource Governor. They are as follows:
1. Enabled
2. Disabled

For performing its operation a Resource Governor must be enabled. You can execute following statement to enable resource governor:

ALTER RESOURCE GOVERNOR RECONFIGURE

It can also be done through SQL Server management studio by the popup menu shown when resource governor is right clicked in Object Browser. You can have a look at the following to understand this: (See Figure 3)

Alternatively, it can also be enabled when a new Resource pool, Workload etc is defined. See Figure 4.

Disabling Resource Governor
If you think it is some feature that you don't need in your organization, then you may disable this feature altogether. After disabling this, all the resources are assigned by the server in nearly the same fashion as SQL Server 2005.

ALTER RESOURCE GOVERNOR DISABLE

The same result can also be achieved by taking the properties of Resource Manager in SQL Server Management Studio 2008. Just un-check the 'Enable Resource Governor' check box (See Figure 4).

After disabling this, all new session requests are assigned to the default workload group. All the sessions which are already assigned to some group remain assigned to that until they are disconnected.

How this all works?

Whenever there is a request for a connection, the request is classified through classifier function and a workload group is identified that this session request should be handled with. Now a workload group may be assigned to only a single resource pool. This resource pool is used to assign and limit the resources required by the session.

Performance Monitoring of Resource Governor:

You can check the performance through Reliability and Performance Monitor. There are following performance counters available:
1. SQLServer: Resource Pool Stats
2. SQLServer: Workload Stats.

See Figure 5.

Catalog Views

There are following catalog views introduced for Resource governor.
1. sys.resource_governor_configuration
2. sys.resource_governor_resource_pools
3. sys.resource_governor_workload_groups

Dynamic Management Views (DMVs) for Resource Governor

There are following DMVs introduced to support resource governor.
1. sys.dm_resource_governor_workload_groups
2. sys.dm_resource_governor_configuration
3. sys.dm_resource_governor_resource_pools

Limitations:

There are also some limitations to the Resource Governor. They are as follows:
1. It is only limited to the Database engine. But there are some services which are out of the database engine like Analysis, Integration and Reporting services. For these, we still have to rely on other solutions to manage the resource problem i.e. installing separate SQL Server instances.
2. Only a single instance can be managed through this. An organization may have more than a single instance, but must manage each separately.
3. Limited to only two resources i.e. CPU bandwidth and memory management.

Code Listing 1

CREATE RESOURCE POOL MyPool
WITH
(
 MIN_CPU_PERCENT=25,
 MAX_CPU_PERCENT=75,
 MIN_MEMORY_PERCENT=25,
 MAX_MEMORY_PERCENT=50
)

Code Listing 2

CREATE FUNCTION My_Classifer_func()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 --workload definition based on login names
 DECLARE @WorkLoadName sysname 
 IF SUSER_SNAME()= 'admin' 
 BEGIN
 SET @WorkLoadName='AdminUsers'; 
 END

--workload definition based on application
 IF APP_NAME() = 'My Application'
 BEGIN
 SET @WorkLoadName='MyApp'; 
 END

RETURN @WorkLoadName;
END

Author:

My name is Muhammad Shujaat Siddiqi. I am B.E in Computer and Information Systems from NED University, Karachi. Currently, I am working as a software consultant in New Jersey, USA.

Blog Address: shujaatsiddiqi.blogspot.com