Resource Governor in SQL Server 2008 R2 manages customers' workloads

SQL Server 2008 R2 Resource Governor helps you allocate customers' resources and manage their workloads, but it has its limitations.

Solutions provider takeaway: If your customers are using legacy versions of SQL Server, they may be interested in upgrading to a new SQL Server 2008 R2 feature called the Resource Governor when R2 comes out later this year. The Resource Governor breaks incoming requests into distinct workloads and allows you to set thresholds on the resources that each individual workload can consume.

More on SQL Server 2008 R2:

New SQL Server 2008 R2 editions, features and licensing

SQL Server 2008 R2 could drop in May

In these economically challenging times, organizations are attempting to reduce costs by making better use of their database servers. In doing so, some of your customers may attempt to consolidate multiple SQL databases on a single server to reduce hardware and licensing costs. You may have other customers who attempt to consolidate their data into a single SQL Server database to avoid maintaining multiple instances of the same data.

Although these approaches do work relatively well, they are not without drawbacks. For instance, it is possible for a runaway query to exhaust the SQL Server of memory or CPU resources to the point that other sessions become unresponsive. If a SQL Server is hosting multiple SQL databases, a heavily used database can consume so many resources that it effectively diminishes the performance of all of the databases on the server.

In these situations, using the SQL Server 2008 R2 Resource Governor will help your customers organize workloads and determine the amount of resources that each workload can handle.

The anatomy of SQL Server 2008 R2 Resource Governor

One major misconception about the SQL Server 2008 R2 Resource Governor is that it is a mechanism for allocating server resources to SQL Server databases. Although the Resource Governor can accomplish per-database resource allocations, it is important to understand that the Resource Governor is not a database-level feature. Microsoft designed it to allocate server resources based on the particulars of an inbound request. Therefore, even if two requests were made of the same database, those requests might be allocated to different resources depending on the way that the request is classified.

It should, therefore, come as no surprise that the most critical of the Resource Governor's components is its classifier. The classifier is responsible for examining each inbound request and routing the request accordingly.

The request is routed to another resource called a workload group. Each workload group is attached to a corresponding resource pool that provides CPU and memory resources to the requests that are being serviced by the workload group. It is possible, however, for multiple workload groups to share a common pool of resources (although this may defeat the purpose of creating the various workload groups).

By default, SQL Server recognizes two workload groups: an internal group and a default group. The internal group and its resource pool are designed to provide resources to the SQL Server itself. The default group is a catch-all group. In a default deployment, the default group handles everything except for the requests that are related (and, in turn, routed) to the internal group.

To further categorize SQL Server workloads and allocate resources based on those categorizations, it is necessary to provide the classifier with a set of rules that it can use to parse inbound requests. Likewise, additional workload groups and resource pools must be created to handle those requests once classified. As a solutions provider, you may be called upon to create the classification rules and workload groups for your customers.

SQL Server 2008 R2 Resource Governor limitations

Although the SQL Server 2008 R2 Resource Governor can make SQL Server databases less prone to resource exhaustion, there are some significant limitations that you need to be aware of. First, the Resource Governor is designed to work within a SQL Server instance. If your customers have multiple SQL Server instances deployed on a single server, the Resource Governor is not going to be able to manage workloads across those instances.

Another Resource Governor limitation is that it only works with the SQL Server database engine. This limitation may initially seem trivial, but it means that other SQL-related services such as the Reporting Service, Analysis Service and Integration Service are not monitored by the Resource Governor. Since these services can sometimes consume a significant amount of resources, it is possible that the presence of such resources on governed servers may skew the resource monitoring.

The last major SQL Server 2008 R2 Resource Governor limitation is that it's only designed to monitor CPU and memory resources. As such, it is important to design your customers' SQL Server databases in a way that prevents the storage subsystem from becoming a bottleneck.

The inclusion of the Resource Governor in SQL Server 2008 represents new revenue opportunities for solutions providers in the form of upgrade licenses and server configuration services. The key to achieving such sales is to convince your customers that the Resource Governor will help them to use their server hardware resources more effectively.

About the expert:
Brien M. Posey, MCSE, is a five-time recipient of Microsoft's Most Valuable Professional award for his work with Exchange Server, Windows Server, Internet Information Services (IIS), File Systems and Storage. Posey has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal website at www.brienposey.com.


This was first published in February 2010

Dig deeper on IT Channel Resources

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

MicroscopeUK

SearchCloudProvider

SearchSecurity

SearchStorage

SearchNetworking

SearchCloudComputing

SearchConsumerization

SearchDataManagement

SearchBusinessAnalytics

Close