As you might have noticed, in an earlier post I mentioned several new CTP5 features for SQL Server 2008, "Katmai". One of these new features is the Resource Governor. For those of us who've worked with SQL Server in larger-scale production systems, the Resource Governor is a godsend. I've recently had the opportunity to play around with the Resource Governor and thought I'd share some concepts here.
Resource Governor Overview
Simply put, the Resource Governor is a component of SQL Server 2008 that allows administrators to configure pools of resources (Think IIS application pool, although it isn't quite that simple) and set specific limits on those resources. There are 2 main components that make up the Resource Governor:
- Workload Group - A Workload Group is a container that is used to limit certain SQL Server-specific resources, such as Degree of Parallelism or user requests.
- Resource Pool - A Resource Pool is a collection of system resources such as memory or cpu.
A Workload Pool is assigned to a Workload Group, which is assigned to the Resource Governor.
Configuring Resource Pools
Resource Pools (at least in CTP5 of SQL Server 2008) consist of settings for the minimum and maximum CPU and Memory utilization. The following code creates a pool named "limitedPool" and sets the max memory utilization to 50% and the max CPU utilization to 30%.
CREATE RESOURCE POOL poolLimited
WITH
(
MAX_CPU_PERCENT=30,
MAX_MEMORY_PERCENT=50
);
You can also set the MIN percentage of either or both of these settings as well.
Configuring Workload Groups
Workload Groups are a little more complicated because they allow you to control some specific SQL Server related conditions, as well as apply Resource Pools. The following code creates a Workload Group named wrkGroupLimited:
CREATE WORKLOAD GROUP wrkgroupLimited
WITH
(
IMPORTANCE = MEDIUM ,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MAX_CPU_TIME_SEC = 300,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 300,
MAX_DOP = 8,
GROUP_MAX_REQUESTS = 30
)
USING poolLimited;
This code sets the overall importance of the group to "Medium" which is the default setting (This is an enumeration that is used very similar to the OS "priority" setting for an execution thread), limits each request to 50% of the memory size (no single request can consume more than 50% of the memory allocated to the group), sets both the maximum query time and the maximum "wait for resources" time to 5 minutes, sets the maximum degree of parallelism to 8, and sets the maximum number of outstanding requests in the group to 30. The "poolLimited" Resource Pool is assigned to this group as well.
Creating a Classifier Function
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. The following code creates a Classifier Function that tests to see if the application making the connection is Management Studio, and if so, assigns the connection to the "wrkLimited" Workload Group. If the application is not Management Studio, the default Workload Group is used.
CREATE FUNCTION fnsRsGovMgr() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME
IF (APP_NAME() = 'Microsoft SQL Server Management Studio')
SET @grpName = 'wrkgroupLimited'
ELSE
SET @grpName = 'default'
RETURN @grpName
END;
Assigning a Classifier Function to the Resource Governor
The final step in the configuration of the Resource Governor is to assign the classifier function and activate the configuration. The following code assigns the fnsRsGovMgr function to the Resource Governor:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fnsRsGovMgr);
And finally, the following code activates the configuration:
ALTER RESOURCE GOVERNOR RECONFIGURE;
Now, when a user connects to the SQL Server instance using Management Studio, their system resource utilization will be very limited.
