Managing Resource Allocations with SQL Server 2008's Resource Governor

The economic tough times are affecting everyone these days. Most, if not all, corporations are looking for ways to save money and get more value out of their existing infrastructure. One of the areas getting a lot of attention from IT managers is how they can consolidate servers to help minimize hardware and license costs.

With any consolidation effort you need to be concerned about how well merged application and/or database services will play together. One application can potentially steal valuable resources away from another, decreasing the performance of your critical applications. As you start to think about how your corporation might take advantage of consolidation for your SQL Server environment, you might want to look at SQL Server 2008. A new feature called Resource Governor can help you manage CPU and memory usage by throttling down resource-intensive processes.

What Problem Does Resource Governor Solve?
For the sake of this article let's say you consolidated two applications that each had their own instance of SQL Server running on their own dedicated hardware. One of the consolidated applications is an online transaction processing system that is used to enter new orders from customers over the phone. The other consolidated application is a data warehouse application used to support a management reporting system.

Shortly after consolidating these two applications you start receiving complaints. The phone operators that take customer order are complaining about sporadic response time in the online order entry system. It isn't slow all the time, but occasionally it is slow for periods of 10 to 20 minutes, four or five times a day.

After you undertake some performance analysis you identify that the slowdowns occurs whenever certain management reports are run against your data warehouse. This slow down is caused because excessive memory and CPU cycles are used to process the data required for these reports.

The Resource Governor feature of SQL Server 2008 can help you solve this problem. You can set up the Resource Governor to control the CPU and memory consumed by data warehouse queries. Let me walk through how you would do this.

Controlling Resource Usage with Resource Governor
The scenario I presented in the prior section has two applications running, and I want to throttle the resources down for one of those applications. To do this, I will use SQL Server 2008 Resource Governor to limit CPU and memory for all non-Order Entry users. For the purpose of this example, let me call the online transaction processing system OE for Order Entry, and this application connects to SQL Server with only one SQL Server Login, known as OrderEntry.

Our goal for setting up the Resource Governor will be to make sure we can throttle resources based on the following requirements:

• Need to constraint resources for all non OE users during work hours. Work hours are defined between 8 a.m. and 5 p.m.
• All non-OE queries should consume no more than 10 percent of the CPU and 20 percent of the memory during work hours.

Using these goals as a guide let me go through setting up the SQL Server 2008 Resource Governor to control my non-OE queries to not exceed the resource requirements identified above.

The first step in setting up the Resource Governor is to create a function that can be used to classify which workload group a session should be associated with. This function is known as a classifier function. The function is used to determine what workload group a session should be associated with. You need a way to clearly identify a workload if you want to control their resources with the Resource Governor. A number of system functions can be used to identify users and applications to map them to the appropriate workload group. Here a partial list of some system functions that could be used in your classifier functions: HOST_NAME(), APP_NAME(), SUSER_NAME, SUSER_SNAME(), IS_SERVERROLEMEMBER(), and IS_MEMBER().

For the situation identified in my scenario above here is my classifier function to identify when the DataWarehouse workload group should be used:

USE master;
go
CREATE FUNCTION WhichWorkloadGroup()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @TIME time;
DECLARE @DAY int;
DECLARE @Workload_Group sysname
SET @TIME = CONVERT(time,getdate());
SET @DAY = DATEPART(weekday,getdate());
-- If during work hours limit DataWarehouse Requests
IF @TIME >= '8:00 AM' and @TIME <= '5:00 PM' and
@DAY > 1 and @DAY < 7 and SUSER_NAME()<> 'OrderEntry'
set @Workload_Group = N'DataWarehouse_WG'
else
set @Workload_Group = N'default'
return @Workload_Group
END

Here you can see that the DataWarehouse_WG workload group is returned from this function when the current date is between 8 a.m. and 5 p.m., Monday through Friday, when the user is not equal to OrderEntry. Note I used the SUSER_NAME function to identify the sessions that are associated with my Order Entry System. By using this function the Resource Governor will be able to make sure all users that are not using the Order Entry system will be classified as being in the DataWarehouse workload group.

You need to make sure your classifier function is setup correctly or it can lead to an unresponsive system. If this happens you will either need to use the Dedicated Administrator Connection (DAC) or run SQL Server in single user mode in order to disable the Resource Governor until you can fix the classifier function.

Once your classifier function is set up you can move on and define a Resource Pool. A Resource Pool needs to be defined so you can identify the minimum and maximum resources that all queries in the pool can consume. Here is my Resource Pool definition.

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

Here I have identified a Resource Pool named DataWarehouse_RP, where the maximum amount of CPU that can be used is only 10 percent and the maximum memory percentage is 20 percent.

The last thing I need to specify is the Workload Group that will be associated with my DateWarehouse_RP Resource Pool. To do this I run the following command:

CREATE WORKLOAD GROUP [DataWarehouse_WG] WITH(
request_max_memory_grant_percent=25)
USING [DataWarehouse_RP];

With this command I have defined a Work Group named DataWarehouse_WG. This work group further constrains individual queries in this workload group to not use more than 25 percent of the memory that the workload group is authorized.

Now once I have this done, all that I need to do is make sure that my classifier function is associated with the Resource Governor. To do that I would run the following command:

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

Just because I have ALTER'd the resource governor to have a new classifier function doesn't mean it will be enabled. I still need to apply this configuration change to make the Resource Governor use this classifier function. To do that I need to run the following command:

ALTER RESOURCE GOVERNOR RECONFIGURE;

If for some reason I wanted to turn off the Resource Governor I could execute the following command:

ALTER RESOURCE GOVERNOR DISABLE;

Note that you will not be able to drop and re-create your classifier function while it is the classifier function for Resource Governor. You cannot even ALTER your classifier while it is associated with the Resource Governor. In addition, disabling the Resource Governor still doesn't allow you to ALTER, or DROP the Resource Governor Classifier function. If you want to change your classifier function you will first need to associate the Resource Governor with a different classifier function, or set the classifier function to NULL by using the following statement:

ALTER RESOURCE GOVERNOR DISABLE WITH (CLASSIFIER_FUNCTION = NULL);

Dynamic Management Views for Resource Governor
There are a three different Dynamic Management views (DMV) used to obtain Resource Governor information. These DMVs can be used determine how you have configured the Resource Governor and how it is running.

The first DMV is sys.dm_resource_governor_configuration. This DMV allows you to identify which classifier function is being used and whether or not there is a pending reconfiguration required. There are two columns returned by this DMV: classifier_function_id, is_ reconfiguration_pending. If the classifier_function_id value is 0 then no classifier function has been associated with the Resource Governor. When the is_reconfiguration_pending value is 0 there are no configurations pending, but if it is "1" then some pending configuration changes are waiting to be activated. To activate pending changes, as mentioned above, requires you to run the "ALTER RESOURCE GOVENOR RECONFIGURE" command, or you need to restart your instance.

The next DMV is sys.dm_resource_governor_resource_pools. This DMV allows you to obtain some statistics on how your Resource Pools have been consuming resources. You can use this DMV to determine how much memory and CPU your pools have consumed over time.

The last DMV provided is sys.dm_resouce_governor_workload_groups. This DMV returns information about how individual workloads have been consuming resource, and there current configuration settings. You can use this DMV in conjunction with sys.dm_resource_governor_resource_pools to get a good picture of how all your workload groups and resources pools are doing to help control your SQL Server resources.

Conclusion
Prior to SQL Server 2008 you did not have a tool that would allow you an easy mechanism to control resources for applications running in a consolidated environment. Having a method to control resource usage by applications allows you to consider consolidating SQL Server instances knowing you can control resources if necessary. Being able to control your machine resource when more than one application is placed on a single SQL Server machine, and running that machine closer to capacity, will allow your organization to reduce hardware and license costs associated with SQL Server 2008.