This is an excerpt from my free eBook, Brad’s Sure Guide to SQL Server 2008.

I think most of us are familiar with this situation: a SQL Server database is the backend of an OLTP (Online Transaction Processing) application, but you are required to allow users to run any reports they want on the production data, any time they want. This often results in long-running reports that negatively affect OLTP performance. Haven’t you ever wished you could limit the amount of hardware resources allocated to reporting, so that normal production activity is not affected?

In SQL Server 2008, you can. The Resource Governor, available only in the Enterprise Edition of SQL Server 2008, is a technology that allows you to selectively control how much CPU and memory resources are assigned to a particular workload. For example, the Resource Governor allows you to specify that no more than 20% (or any figure you designate) of CPU and/or memory resources can be allocated to running reports. When this option is turned on, then no matter how many reports are run, they can never exceed their designated resource allocation. Of course, this will reduce the performance of reporting, but at least now production OLTP activity won’t be as negatively affected by reports.

Currently, Resource Governor only works within the database engine. It does not work with Integration Services, Reporting Services, or Analysis Services.

In this chapter you will learn how you can use the Resource Governor to better prioritize and manage a SQL Server’s workload, how Resource Governor works under the hood, and how to configure it for use.

Uses for Resource Governor

Resource Governor is designed to help DBAs four different ways:

Managing Unpredictable Workloads

As in my reporting example above, it is often difficult to know how your SQL Server resources are being used. While OLTP activity might follow a predictable pattern, there is no knowing when a junior marketing executive will start running reports against your server. Resource Governor allows you to classify different types of loads on your server, which in turn allows you to control how server resources are assigned to a given activity. In SQL Server 2005 and earlier, queries fought amongst themselves to decide which one would grab the necessary resources first, and it was hard to predict who would win out. By using Resource Governor, I can instruct SQL Server to limit the resources a particular activity can access. In this way, I can ensure that my predictable OLTP performance is not hurt by unpredictable activity.

Putting Limits on Run-Away Queries
Closely related to the problem of managing unpredictable workloads is the difficulty of dealing with “run-away queries”. If you have been a DBA for very long at all, you will have seen queries that seem to take forever to run, tying up valuable and limited physical resources, and hurting the performance of other activity on your server. By limiting user or applications that have the potential for executing run-away queries, then run-away queries become much less painful because they can never use more resources than have been assigned to them.

Establishing Workload Priority
Most SQL Server instances have different demands placed on them throughout the day or week. For example, an OLTP server may need to be up 24/7 and maintain a specific service level. On the other hand, this same server may need to run SSIS jobs and maintenance jobs in order to properly function. When these jobs kick in, there is the potential that they can interfere with production work. Resource Governor allows you to allocate resources in such a way as to ensure that priority OLTP activity runs without interference from necessary maintenance jobs.

Prioritizing Preferred Users
For better or worse, Resource Governor allows you to assign specific users, or groups, higher priority than others, on an arbitrary basis. One of the inevitable consequences of this is that, as soon as managers hear about it, you may be asked to give priority to certain users over other users. Hopefully you can avoid this political battle, but you may not be able to. As George Orwell puts it in his novel, Animal Farm: “All animals are equal, but some animals are more equal than others”.

How Resource Governor Works

The best way to explain how the Resource Governor works is to walk through an example. Let’s assume we have a SQL Server 2008 instance, with a single database, that is designed to store data from an ERP (Enterprise Resource Planning) application. This is essentially a 100% pure OLTP application that must provide a specified service level 24/7. Let’s also assume that the reporting for this ERP application is not done through the application, but via a third-party reporting tool, directly against the database. In addition to running standard reports, the reporting tool permits users to run ad-hoc reports anytime they want. Essentially, this reporting can be considered an OLAP (Online Analytical Processing) activity.

This is a classic example of OLTP and OLAP activities running on the same server, against the same database, at the same time. As most of you know, this is a terrible architecture because it can result in non-time sensitive OLAP reporting blocking time-sensitive OLTP activity, potentially preventing you from attaining your specified service level for the ERP application. Unfortunately, as most of you will also know, this architecture is all too common. As the DBA, you want to use Resource Governor to ensure that your ERP application attains its specified service level, while at the same time, allowing reports to be run, but at a lower priority so they don’t interfere with OLTP activity. To accomplish this goal, you have decided that you want to limit both the amount of SQL Server CPU and memory resources used by the Reporting application to a maximum of 20% of what the server has available. Based on your research, you have determined that, with this restriction in place, your ERP application can consistently attain its service levels.

The figure presents a somewhat-oversimplified picture of this scenario. In this figure we can see two applications connecting to SQL Server. The first is our ERP application, called “ERP”, and the second is our reporting application, called “Reporting”:

In order for the Resource Governor to be able to allocate CPU and memory resources as you require, it must be able to differentiate between different workloads. It does this using a user-defined classification function, created using Transact-SQL. When a connection is made to SQL Server, Resource Governor will use the classification function to evaluate each connection to determine which application it came from: either the ERP or the Reporting application. This is why it is important to be able to identify each application as it connects to SQL Server, because the classification function uses the APP_NAME()1 function to tell the Resource Governor which application is connecting to the database.

Besides using the APP_NAME() function to differentiate one workload from another, Resource Governor can use other system functions, such as HOST_NAME(),SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), and IS_MEMBER().

Once an incoming connection has been classified, the connection is then routed to a particular workload group. Put simply, a workload group is a container that holds connection requests that match a set of SQL Server connections, as defined by the classification function. For example, the ERP application connections goes to a workload group named “ERP”, and the Reporting application connections go to a workload group named “Reporting.”

At this point, all we have done is to separate out the connections from each application. As yet, we still have not allocated any resources to these connections, and this is the next step.

Associated with every workload group is a resource pool. A resource pool represents the physical resources of a server. It is used to allocate the minimum and maximum amount of CPU and/or memory that is to be allocated to a specific type of SQL Server connection. For example, if you want to limit the connections from the Reporting application to no more than 20% of the available CPU and memory resources of the server, then you will configure a resource pool with this information.

In our example, there are two resource pools, one associated with the ERP workload group and the other with the Reporting workload group. The ERP resource pool permits connections in this group to use up to 100% of the available CPU and memory resources on the server. The Reporting resource pool limits connections in the Reporting workload group to no more than 20% of the server’s CPU and memory resources.

Once a connection has been classified and put into the correct workload group, then the connection is allocated the CPU and memory resources that have been assigned to it, and then the query is passed on to the query optimizer for execution. In our example, Reporting queries would be allocated resources in such a way that they would not use in excess of 20% of the CPU and memory resources when passed to the query optimizer for execution.

Given that we specified that the ERP application has the ability to use up to 100% of the available CPU and memory resources, you’re probably wondering what would happen if a request from the Reporting Application came through while the ERP application is using 100% of the resources. Under this condition, the Reporting connection would simply have to wait until resources become available for it. In this way, the ERP application get all the resources it needs to function properly, while allowing the Reporting application to continue to run, but at a lower priority.

Configuring Resource Governor

Unlike many other features in SQL Server, you cannot use SSMS to entirely configure and manage Resource Governor. You can either use a combination of Transact-SQL and SSMS, or you can use Transact-SQL only. To keep things as simple as possible, all of the following examples will be demonstrated using Transact-SQL only. Check out Books Online to see how SSMS can be used for some of the following steps.

Resource Governor only “kicks in” if there are resource contentions. For example, if the ERP workload happens to be small, and there are unused resources available, then the Reporting workload can use more than 20% of its allocated resources. But if the ERP workload is large, causing the ERP and Reporting workloads to fight for server resource, only then would the Resource Governor kick in and prevent the Reporting workload from using more than 20% of the available resources. For the rest of this chapter, we will be assuming that there is continuous resource contention and that the Reporting workload will be constrained by the Resource Governor.

Now, let’s go through an example of how to configure the Resource Governor, based on the Reporting example we’ve used throughout the chapter.

Creating Resource Pools

The first step is to create a resource pool, each for the ERP and the Reporting applications.

As you can see above, the CREATE RESOURCE POOL statement has a number of parameters.

They include:

• MIN_CPU_PERCENT: Allows you to specify the guaranteed average CPU bandwidth for all requests to the resource pool when there is CPU contention.
• MAX_CPU_PERCENT: Allows you to specify the maximum average CPU bandwidth that all requests for the resource pool when there is CPU contention.
• MIN_MEMORY_PERCENT: Allows you to specify the minimum amount of memory that is reserved for the resource pool that cannot be shared with other resource pools.
• MAX_MEMORY_PERCENT: Allows you to specify the total server memory that can be used by requests to the resource pool.

In the code above, I have created a resource pool named “poolERP” that will allow the ERP application to use up to 100% of the available server CPU and memory resources. In addition, I have specified that the “poolERP” be allocated a minimum of 80% of the total server CPU and memory resources available. This guarantees that “poolERP” will always have access to a minimum of 80% of the resources.

For “poolReporting”, I have specified that the maximum amount of resources that go to the Reporting application is 20%. Assuming there are resource contentions, the Reporting application’s resources will be constrained to this amount.

Creating Workload Groups

Next, I need to create the workload groups that will act as containers for the user connections that are defined in the classification user-defined function. During this same step, I also associate to each workload group one of the resource pools I just created.

I created a workload group called “wrkgroupERP” using the CREATE WORKLOAD GROUP statement, and I have associated it to the resource pool “poolERP”. In addition, I created a workload group called wrkgroupReporting that is associated to the resource pool “poolReporting”.

Creating the Classification Function

Next, I create the classification user-defined function that specifies which connections will be put inside each of the workload groups created in the previous step. Only one user-defined classification function can be created for each SQL Server instance, so when creating your own user-defined classification function, it is important to consider all the various connections that can be made to a SQL Server instance, and deal with them appropriately by assigning them to an appropriate workload group (and its associated resource pool). If a connection is not assigned to a specific workload group (and pool) by the classification function, then the connection goes to a default workload group (and pool). See Books Online to learn how to properly configure a default workload group and pool.

The user-defined classification function is created using the CREATE FUNCTION statement and is called “rgclassifier1()”. Inside this function, I specify the criteria on which SQL Server user connections are evaluated. For example, I have specified that if the APP_NAME() of a user connection equals “Reporting,” which is the application name of the Reporting application, then it should be added to the “wrkgroupReports” workload group. If the APP_NAME() of a user connection does not equal “Reporting,” then it should be added to the “wrkgroupERP” workload group.

Enabling Resource Governor

We are almost done, but we have a couple of maintenance tasks to perform before our Resource Governor will work. First, we need to run the following code, which assigns the “rgclassifier1″ function to the Resource Governor.

Second, we need to run the following code, which enables the Resource Governor.

At this point, the Resource Governor is turned on, and any time a report is run from the Reporting application, it will be limited to a maximum of 20% CPU and memory resources, while the ERP application will be able to use up to 100% of the available CPU and memory resources.

Summary

As you can imagine, there is a lot more to the Resource Governor than what I have discussed in this chapter. I have left out a lot of details, including additional configuration options, how to make changes after the fact, how to monitor if the Resource Governor is running properly, and troubleshooting information. If you are interested in learning more about the Resource Governor, I suggest you dive into Books Online and begin experimenting with it on a test server.

While Resource Governor offers many potential benefits, it also offers some potential pitfalls. For example, a misconfigured Resource Governor can not only hurt a server’s overall performance, it can potentially lock your server up, requiring you to use the Dedicated Administrator Connection (DAC) to attach to the locked up SQL Server in order to troubleshoot and fix the problem. Because of this, I highly recommend that you only use this option if you are an experienced DBA, and that you first test your configuration on a test server before rolling it out into production.