SQL Server 2008 Resource Governor

Background
As the number of queries hitting a database grows, contention for memory and CPU resources increases, giving rise to problems such as runaway queries that monopolize resources and deny them to other applications, and unpredictable workload execution, which occurs because jobs run more quickly at times when competition for resources is minimal and more slowly at other times, when competition is greater.

Predictable workload execution
Resource Governor, a new SQL Server 2008 Enterprise Edition feature, addresses these problems by enabling administrators to explicitly control the allocation of resources to competing workloads by applying administrator-defined CPU and memory limits to specified workloads, such as regular maintenance jobs or periodic resource intensive queries. This helps to make query execution more predictable and can prevent the occurrence of runaway queries.

You define the available resources by creating resource pools, and you use classifier functions and workload groups to identify workloads and apply limits.

Resource pools
Resource pools limit the available resources in terms of maximum and minimum CPU and memory percentages, which you define. You can create multiple pools, each with different resource settings to reflect the different workloads on the system. Resource Governor will use the percentage values that you supply to calculate a shared percentage value and an effective maximum value for CPU and memory for each of the pools. When calculating these figures, Resource Governor takes account of the competing requirements of each pool so that resource usage is balanced. In addition to user defined resource pools, there are two default resource pools, called ‘Internal’ and ‘Default’, which are used by the Internal and Default workload groups respectively.

Workload groups
A workload group is effectively a container into which incoming sessions are placed. Administrators can define multiple workload groups to represent the different types of workload that the system experiences, and SQL Server also has the two built-in workload groups, ‘Internal’ and ‘Default’, that we mentioned above. Connecting sessions are allocated to the Default group when they are not explicitly allocated to any other group, and the internal group is for use by SQL Server itself, for processes such as Lazy Writer.

Each workload group is associated with a resource pool, which dictates the memory and CPU resources that are available to the workload group, as described earlier. Workload groups have additional settings that can be used to fine tune resource usage, including the maximum number of CPUs that the group can use, and LOW, MEDIUM, and HIGH importance settings to prioritize workload groups relative to other workload groups. By allocating sessions to a specific workload group, you limit the resources that an application has access to, which prevents problems such as runaway queries from ever arising.

Classifier functions
Classifier functions examine incoming sessions as they connect to the server and allocate them to user defined or system workload groups. When you define a classifier function, you can use functions such as HOSTNAME(), SUSER_SNAME(), and APP_NAME() to identify sessions and stream them into the appropriate workload group. For example, you can create a function that uses APP_NAME() to check the names of applications that initiate sessions, and to place them into workload groups that were created to apply limits specifically for those applications. For maximum efficiency, classifier functions should ideally group together sessions that perform similar tasks so that each workload group is as uniform as possible.

Summary
Resource Governor is a powerful tool that is likely to become an essential part of the way we manage SQL Server databases. However, it is not a panacea for poorly performing queries, and you still need to consider all of the ‘traditional’ ways of improving query performance, such as good index and query design. There are also a couple of limitations to consider before you race off to implement it on your servers: Firstly, with the current release, Resource Governor can only be used to manage workloads within an instance of SQL Server; so if you’re running a multi-instance set-up, workloads from one instance can still impact upon with the execution of workloads from other instances, just as they always did. Secondly, Resource Governor can only be used to manage the database engine itself, and not to manage Integration Services, Reporting Services, or Analysis Services.