Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 15, 2010

Getting Acquainted with SQL Server 2008's Resource Governor

By Gregory A. Larsen

With the introduction of SQL Server 2008 came some new features. One of those new features is Resource Governor. With this new feature, you can control resource allocations based on rules. In this article I will explain how to setup resource governor, how to use it and the limitations associated with this new resource allocation tool.

What is the Resource Governor?

The Resource Governor is new technology that was incorporated into SQL Server 2008 that allows you to control processes running in SQL Server. Resource Governor makes sure processes don’t consume more resources (CPU and memory) then they are allotted. The Resource Governor now allows you to throttle down the resource intensive processes so they don’t adversely affect all of the other processes running within a SQL Server instance. The DBA defines a function to identify those potential resource intensive processes, and then classifies them into the appropriate groups. Along with these classifications, the DBA identifies resource thresholds that these potential resource intensive processes should not exceed. SQL Server uses the classification and the resource thresholds to identify and control these resource intensive processes to better manage the valuable machine resources for all processes.

Setting Up Resource Governor to Control Resource Intensive Processes

In order to control your resources you need to tell Resource Governor how it should manage the resources. Resource governor is able to control CPU and memory for processes. It does this by using the resource configurations defined within resource pools and workload groups. A classifier function is also used to identify which workload group a particular process falls into.

Let me go through an example of setting up Resource Governor to handle a particular situation. For the purpose of this article, let’s say we have some monthly processing requirements that are resource intensive. Let’s set up resource governor to restrict the CPU and memory usage of any one of these monthly processes. Here is some information about our monthly process and some resource requirements we need to enforce with Resource Governor:

  • Each monthly process will run under a SQL Server authenticated account named MONTHLY_BATCH
  • Need to have resources control whenever a monthly process is run in the first 10 days of the month
  • Each monthly process must consume no more than 10% of the CPU and 20% of the memory

Now let me walk you through setting up Resource Governor to meet these requirements.

The first step of this process is to write a classifier function that will be able to identify my monthly processes. The monthly processes will be determined by using the SQL Server login under which the process runs. If a process is run using the MONTHLY_BATCH login, then it will be determined to be a monthly process. When the classifier function identifies a monthly process, it will assign the process to a workload group that is associated with a resource throttled workload group. I can use the SUSER_NAME() function to determine the SQL Server login for the connection. Then with this information, I can assign the process to the appropriate workload group. Here is the classifier function I wrote. I will create it in the master database:

USE master;
go
ALTER FUNCTION WhichWorkloadGroup()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @DAY int;
	DECLARE @Workload_Group sysname;
	SET @DAY = DATEPART(DAY,GETDATE());
-- If sessions running first part of month
    IF @DAY <= 10 and SUSER_NAME()= 'MONTHLY_BATCH'
       SET @Workload_Group = N'MONTHLY_BATCH_WG'
    ELSE
       SET @Workload_Group =  N'default'
    RETURN @Workload_Group
END

If you look at the code, you can see I am using the DATEPART function to pull out the DAY from the value returned from the GETDATE function. I then check to see if the current day is in the first of the month (@DAY <= 10). If it is, I return the value “MONTHLY_BATCH_WG” from this function, otherwise I return the value “default”. This classifier function will be used by Resource Governor to determine which workload group should be used to control each process run on my server.

You need to thoroughly test any classifier function you create. If it is not functioning as you designed, it could lead to an unresponsive SQL Server. If this should happen, you will either need to use the Dedicated Administrator Connection (DAC), or bring up SQL Server in single user mode to disable the Resource Governor.

Now that I have my classifier function defined I can move on and define my Resource Pool that will allow me to control the resources consumed by processes that are run under the MONTHLY_BATCH login account during the first part of a month (DAY <=10). A Resource Pool is the mechanism that will allow me to identify the maximum CPU and Memory settings I want to use to control my monthly batch processing. Here is the script I will use to set up my Resource Pool:

CREATE RESOURCE POOL [MONTHLY_BATCH_RP] WITH(
		max_cpu_percent=10, 
		max_memory_percent=20);

If you review this, you can see I am creating a Resource Pool with a name “MONTHLY_BATCH_RP” that has a CPU max of 10% and a Memory max of 20%. These are the resource limit requirements I mentioned above.

Next, I need to create a workload group, with the same name I returned from my classifier function that is associated with my Resource Pool. To do that I run the following command:

CREATE WORKLOAD GROUP [MONTHLY_BATCH_WG] WITH(
            request_max_memory_grant_percent=25)
  USING [MONTHLY_BATCH_RP];

Here I even further constrained a single process to use 25% of the memory associated with the MONTHLY_BATCH _RP. This will allow more than a single monthly process to run at a time without one of them taking up all of the resource pool resources. If I only wanted one MONTHLY_BATCH process to run at a time I could do this by setting the “request_max_memory_grant_percentage” to more than 50.

Now that I have my Resource Pool and Workload group defined, I need to associate the classifier function I created earlier with Resource Governor. Keep in mind there can only be one classifier function associated with Resource Governor. To make this association I’ll need to run the following ALTER statement:

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

At this point, you probably are thinking I’m now done setting up Resource Governor for my situation. Wrong! I need to reconfigure the Resource Governor before my classifier function will take effect and start controlling my resource. To do that I run the following statement:

ALTER RESOURCE GOVERNOR RECONFIGURE;

Alternatively, I could have also stopped and restarted my SQL Server instance to get my classifier function to start working.

Once you have connected your classifier function with Resource Governor, and reconfigured you will not be able to make changes to your function with an ALTER, or DROP statement. Even disabling Resource Governor does not allow you to ALTER or DROP the classifier function. If you want to change your classifier function, you must associate the Resource Governor with a new classifier function or set the classifier function to NULL. Here is an example of how to set the classifier function to NULL:

ALTER RESOURCE GOVERNOR DISABLE WITH (CLASSIFIER_FUNCTION = NULL);

Monitoring Resource Governor

There are some new Dynamic Management Views (DMVs) that allow you to retrieve information about Resource Governor. There are three different DMVs that you can use to determine how Resource Governor is running and configured.

The first DMV is “sys.dm_resource_governor_configuration”. Like the name implies this DMV can be used to identify configuration information. Using this DMV, you can determine whether a classifier function is associated with Resource Governor and whether or not it is pending a reconfigure operation.

The second DMV allows you to obtain some resource usage statistics associated with Resource Governor. This DMV is “sys.dm_resource_governor_resource_pools”. This DMV allows you to determine how much memory and CPU has been consumed overtime by your resource pools.

The last DMV is named “sys.dm_resource_governor_workload_groups”. This DMV allows you to return resources consumed by your different workload groups.

Managing Resources

It is great now that we have a tool with SQL Server 2008 that allows us to throttle those heavy resource intensive processes. Being able to do this allows us to provide a way to ensure that one process, or a set of resource intensive processes don’t consume all the CPU and memory of our instance and thus make our SQL Server sluggish or seem like it is totally unresponsive. In the past, people controlled resource intensive processes by running them during off hours or on a separate box all together. Now that we have Resource Governor, we can start thinking about consolidating some of those instances we separated because there was no throttling method in prior releases of SQL Server. Keep in mind that Resource Governor is not instance aware, so it can’t control resources completely if you have multiple instances running on a server. Hopefully in future version for SQL Server, Microsoft will be able to incorporate some changes to handle resources across all instances on a machine, but for now Resource governor is a great method for controlling resources on a per instance basis.

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM