Get started Bring yourself up to speed with our introductory content.

FAQ: Using Resource Governor in SQL Server 2008 R2 to allocate resources

Ensure your customer's SQL Server 2008 R2 environment has the proper resources by determining workload group requirements with Resource Governor and classifying server requests.

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.


 

Listen to part of this Project FAQ in a podcast.

 

FAQ: Using Resource Governor in SQL Server 2008 R2 to allocate resources

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 address?
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

 

What types of issues does the SQL Server 2008 R2 Resource Governor address?

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.

 

Are there any constraints that limit the Resource Governor's effectiveness?

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.

 

How does the SQL Server 2008 R2 Resource Governor use multiple resource pools?

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.

 

How can solution providers determine how many workload groups to create?

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.

 

How can solution providers tell if hardware resources are allocated correctly?

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.

 

What resources should solution providers initially provide to a workload group?

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.

 

What is involved in classifying SQL Server requests?

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.

 

Is the Resource Governor completely command-line based?

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.

 

More resources on SQL Server 2008 R2 Resource Governor

Resource Governor in SQL Server 2008 R2 manages customers' workloads

Microsoft SQL Server 2008 Resource Governor primer

SQL Server 2008 Resource Governor questions

The SQL Server 2008 Resource Governor

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 last published in October 2010

Dig Deeper on Database Management Products and Solutions

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

MicroscopeUK

SearchCloudProvider

SearchSecurity

SearchStorage

SearchNetworking

SearchCloudComputing

SearchDataManagement

SearchBusinessAnalytics

Close