16.1. Resource Governor overview

A common (and unfortunate) situation that DBAs often encounter in production systems is a single runaway query that flatlines server performance to the detriment of all users. Unless the offending query can be identified and killed, users are effectively at the mercy of the query completing. To make matters worse, there's nothing stopping the same query from occurring over and over again, with the end result being an unpredictable experience for end users and a frustrating one for DBAs.

In previous versions of SQL Server, resources could be constrained at an instance level with configuration settings such as CPU affinity and maximum memory, but such settings are coarse grained and don't apply to individual users or applications. Thus, instances that host groups of users or applications with varying resource usage present a challenge for a DBA whose goal is to provide a stable, predictable environment for all users.

A classic example of this is a SQL Server instance used for both reporting and data entry. In such a case, performance for data entry users may be fine until someone decides to run a large report, at which time data entry performance grinds to a halt. Resource Governor is purpose built to address problems of this type.

16.1.1. Resource Governor benefits

Put simply, Resource Governor enables resource sharing and segregation between groups of users or applications based on their connection source. For example, we could assign our data entry group minimum CPU and memory resources, effectively shielding them from the impact of a large report query.

As you'll see shortly, Resource Governor is not only relatively easy to set up and use, but it's also very effective in limiting the performance impact of one group on others. That being said, there are a number of considerations before using it, as well as several limitations.

16.1.2. Resource Governor limitations

As a brand-new feature in SQL Server 2008, Resource Governor comes with a number of limitations that will presumably be removed in subsequent SQL Server versions as the feature matures:

• Resource constraints are limited to memory and CPU. Network and disk I/O resources cannot be constrained in SQL Server 2008.

• Reporting Services, Analysis Services, and Integration Services resource usage cannot be constrained. In this version, resource constraints apply to core database engine usage only.

• Resource Governor constraints are defined within (and apply within) a SQL Server instance; resource constraints do not apply across instances on a multi-instance server.^[]

  ^[] Resource segregation across instances can be achieved with CPU affinity and min/max memory settings.

Further, Resource Governor is most effective when resource usage is well defined and consistent across a number of distinct groups, for example, data entry and reports. In cases where particular data entry transactions dominate resource usage, Resource Governor may not be effective, unless there is a method of identifying and classifying the source of such transactions.

Before we dive into the details of Resource Governor, let's briefly define the major components.

16.1.3. Resource Governor components

Resource Governor tracks and allocates CPU and memory resources using three major components: resource pools, workload groups, and a classifier function:

• A resource pool is created to define the minimum and maximum usage of a server's CPU and memory resources. A resource pool can be used by one or more workload groups.

• Connections to a SQL Server instance are classified into a workload group by the classifier function. The workload group is mapped to a resource pool defining the minimum and maximum resource consumption available to the connection. The resource group also serves as a monitoring and aggregation point for resource consumption and as a method of applying a uniform resource usage policy, for example, a MAXDOP setting specific to each group.

• The classifier function, invoked upon connection to a SQL Server instance, defines which resource group the connection is assigned to. Connections can be classified based on the username, application name, host name, or role membership.

Workload groups and resource pools are either user defined or system based. There are two system groups and pools: internal and default. The internal group and pool are used for SQL Server processes, whereas the default group and pool are used for connections that are not otherwise classified into a user-defined group and pool.

Figure 16.1 illustrates the above components working together to define resource usage limits for an incoming connection to a SQL Server 2008 instance.

With this overview in mind, let's consider the components in more detail, beginning with the classifier function.