Getting Acquainted with SQL Server 2008’s Resource Governor

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

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles