Resource Governor

Before we understand the Resource Governor let us consider an example where we have hosted two databases App1 and App2 on a single SQL Server 2005 with a query execution time out of 600 seconds. Lets say App1 database should not allow queries to execute for more than 5 minutes which is 300 seconds and App2 should allow 600 seconds. This was not easy to achieve on SQL 2005. (We may argue that we could specify connection timeout on the client connection string in ADO.Net, but that’s just a work around), Alright, what if I want to give connections of App1 database more priority than App2 database connections when server resources are limited?

One of the new features in SQL 2008 (Katmai) is the Resource Governer. It is a component that allows administrators to configure pools of resources. Consider this an analogy to the Application Pool in IIS 6.

The 2 main components in the Resource Governer that matter are the Resource Pool and the Workload Group.



Resource Pool

The Resource Pool allows you set server (as in the physical server on which SQL server is installed) level resource usage such as

MAX_CPU_PERCENT, MIN_CPU_PERCENT and MAX_MEMORY_PERCENT, MIN_MEMORY_PERCENT

Using TSQL

CREATE RESOURCE POOL poolNorthwind
WITH
(
MAX_CPU_PERCENT = 30,
MAX_MEMORY_PERCENT = 50
);

Workload Group.

The Workload Group allows you to control some specific SQL Server related conditions, as well as apply Resource Pools. It allows you to control settings such as the connection priority, query time out, max parallel requests and other parameters as mentioned below

IMPORTANCE = MEDIUM , REQUEST_MAX_MEMORY_GRANT_PERCENT, REQUEST_MAX_CPU_TIME_SEC , REQUEST_MEMORY_GRANT_TIMEOUT_SEC , MAX_DOP , GROUP_MAX_REQUESTS

Using TSQL

CREATE WORKLOAD GROUP workGroupNorthwind
WITH
(
IMPORTANCE = MEDIUM , -- default is medium, its like OS thread priority REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 300,-- max query timeout to 5 min
MAX_DOP = 8, -- maximum degree of parallelism to 8
GROUP_MAX_REQUESTS = 30
)
USING poolNorthwind;

In order for SQL Server to be able to use the Workload Groups that you've created, you need to classify each connection to determine which group that specific connections falls into. This is done through the use of a Classifier Function. Classifier functions are new in SQL Server 2008 and execute each time a new connection is made to the server. Classifier functions are scalar user-defined functions and are basically used to return the name of the target Workload Group for each user connection.

Code Example

CREATE FUNCTION ufnNorthwindRsGovMgr()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME

IF (APP_NAME() = 'Microsoft SQL Server Management Studio')
SET @grpName = 'workGroupNorthwind'
ELSE
SET @grpName = 'default'
RETURN @grpNameEND;

The final step in the configuration of the Resource Governor is to assign the classifier function and activate the configuration.

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

ALTER RESOURCE GOVERNOR RECONFIGURE;

Using the Management Studio

If we use the Management Studio which comes with the Developer versrion of SQL 2008 CTP allows you to create the Resource Pool using the Resource Governor properties dialog, shown in the picture on this entry



I have created a solution with the above code . Download