Microsoft's SQL Server 2008 R2 Resource Governor is an asset to solution providers in many ways, including allocating resources and limiting CPU consumption. But providers also need to be aware of potential limitations that can restrict the Resource Governor's effectiveness in their customers' environments.
In this FAQ, SQL Server expert Brien Posey breaks down what the Resource Governor can help you accomplish and how solution providers can use the Performance Monitor to gather statistics for SQL Server 2008 R2 workload groups. See the best ways to execute tasks such as classifying SQL Server requests or handling workload groups.
Right-click on the podcast link to download the file as an MP3!
•What types of issues does the SQL Server 2008 R2 Resource Governor
•Are there any constraints that limit the Resource Governor's effectiveness?
•How does the SQL Server 2008 R2 Resource Governor use multiple resource pools?
•How can solution providers determine how many workload groups to create?
•How can solution providers tell if hardware resources are allocated correctly?
•What resources should solution providers initially provide to a workload group?
•What is involved in classifying SQL Server requests?
•Is the Resource Governor completely command-line based?
•More resources on SQL Server 2008 R2 Resource Governor
•About the expert
SQL Server 2008 R2 can be an extremely resource-intensive application for solution providers, and that can be especially problematic if multiple databases are hosted on a common SQL Server. The Resource Governor provides a means for limiting the server resources that are consumed as a result of incoming requests. The feature can help prevent excessive CPU consumption because of runaway queries and is also used to prioritize workloads and to stabilize otherwise unpredictable workload execution.
The biggest limitation associated with the SQL Server 2008 R2 Resource Governor is the fact that it is a SQL Server component and not an operating system (OS)-level component. Because of this, the Resource Governor can limit the resource consumption associated with SQL Server databases but is unable to limit resources that are consumed by other processes running on the server.
Although the Resource Governor is a SQL Server component, it has some additional limitations that directly impact its effectiveness in regulating SQL-related resource consumption. The first restriction is that the Resource Governor only works with the SQL Server Database Engine and, therefore, cannot limit resource consumption by other SQL services, such as Reporting Services or Analysis Services.
Another issue is that the Resource Governor is SQL Server instance-specific and cannot manage workloads across multiple SQL instances.
Finally, the Resource Governor is only capable of managing CPU and memory resources. SQL Server can be very disk-intensive, but the Resource Governor cannot throttle hard-disk usage.
A resource pool is a collection of hardware resources that are available to SQL Server. When the SQL Server 2008 R2 Resource Governor is initially deployed, two different resource pools are created – internal and default. The basic idea behind the internal pool is that SQL Server requires system resources in order to function. The internal pool, which is unalterable, is designed to ensure that SQL is not deprived of server resources.
In contrast, the default pool is essentially a predefined user pool that provides resources to the default group. The default group is used when an inbound request is unclassified or when the classification fails.
Although solution providers are free to use the default group and the default pool to manage resource consumption, they also have the option of defining their own pools. User-defined pools are generally the preferred mechanism for allocating resources to requests.
Before answering this question, it is important to understand that the Resource Governor does not throttle SQL Server databases, but limits the resources consumed by inbound requests instead. Unless you plan on using the default group and the default pool to service all requests, you will have to create some user-defined workload groups and link those groups to a resource pool.
The key to successfully allocating resources is to classify the different types of requests being sent to your SQL Server. For example, you might classify requests based on the database that the request pertains to. Once you have classified the various requests, you can create workload groups based on the hardware resources and the priorities of each request's classification.
Over time, the demands on a SQL Server are likely to change and the resources that you initially allocate to workload groups may end up being insufficient later on. As a result, it is important to periodically review a server's resource allocation. The best way to accomplish this is by using the Performance Monitor to gather the execution statistics for each workload group. Once you have gathered the necessary information, you can adjust your resource pools accordingly.
The most useful Performance Monitor counters to watch are SQLServer:Workload Group Stats and SQLServer:Resource Pool Stats.
Determining the optimal amount of resources to give a workload group can be tricky. The best way to accomplish this is to initially link a new workload group to the default resource pool, which gives the workload group plenty of resources.
Allow the server to run for several days, and use the statistics that you collect during that time to determine how to provision a dedicated resource pool for the workload group. For example, you can determine the CPU requirements by multiplying the number of requests per second by the average total CPU usage per request. You can also determine how much memory is required by looking at the maximum and average memory usage statistics.
The Resource Governor does not automatically classify SQL Server 2008 R2 requests. Unless classified, all requests are handled by the default workload group. If you want to use user-defined workload groups, you'll have to create classification functions and then register those functions with the Resource Governor.
Creating and registering classification functions involves writing some code, and the MSDN library provides Resource Governor workload management scenarios.
No, it isn't. Although some coding is required, if you want to create or register a classifier, you can interact with the Resource Governor through SQL Server Management Studio. SQL Server Management Studio allows you to enable or disable the Resource Governor, create or delete resource pools and workload groups and modify resource pool and workload group settings. You can also use Management Studio to allocate resources to a resource pool and to link a workload group to a resource pool.
Resource Governor in SQL Server 2008 R2 manages customers' workloads
Microsoft SQL Server 2008 Resource Governor primer
SQL Server 2008 Resource Governor questions
Handling workloads on SQL Server 2008 with Resource Governor
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, he has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Posey's personal website at www.brienposey.com.
This was first published in October 2010