SQL Server 2008 - Resource Governor - Part I

  • Article

Hi Friends, I am back with one more blog post on an interesting feature in SQL Server 2008. Today I was discussing about run away situations with my colleagues and I found that only few people read about this excellent feature available in SQL Server 2008 so just thought why not to blog about it.

If you are using SQL Server everyday then you would be aware of the situations when you get into run-away situation during the peak load on the server. For example: there is a heavy transaction volume on the server and someone from the remote end fired an ad-hoc query to churn several tables, running to a few million rows and everything comes to a screeching halt. You would have always wondered if there was a way to manage this to avoid run-away situation. Even if there was any solution that sort you would have wondered can it be done on the fly with a minimal impact.

 

What is run-away ?

To explain it in a very simplified manner, every server hardware has a finite number of CPUs and amount of RAM installed on it. When you try to fire a query that is resource intensive and the hardware resources are not available to serve the request ..you get into a run-away situation due to which the service requested is delayed and other requests being handled by the server in parallel are also delayed.

 

So what is the solution ?

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

 

Resource Governor

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor.

It is feature that is designed to manage the resource limits in real time with minimal impact on workloads that are executing.

 

Issues addressed by Resource Governor

Resource Governor addresses 3 primary issues that are prevalent in the database environments.

  1. Run-Away : A situation where one process eats up all the resources and another process starves.
  2. Workload Priority : A situation where we need to prioritise one workload type over another viz. a current payment transaction over another query that is crunching millions of rows to take out some summarized information which is expected to run for a few minutes.
  3. Unpredictable workload execution : A situation similar to this one where two data warehouse applications are a mix of OLTP and data warehouse applications. These applications are not isolated from each other and the resulting resource contention causes unpredictable workload execution.

All of the above scenarios require the ability to differentiate workloads in some way. Resource Governor provides:

  • The ability to classify incoming connections and route their workloads to a specific group (Identifying a workload group based on an incoming connection).
  • The ability to monitor resource usage for each workload in a group.
  • The ability to pool resources and set pool-specific limits on CPU usage and memory allocation. This prevents or minimizes the probability of run-away queries.
  • The ability to associate grouped workloads with a specific pool of resources.
  • The ability to identify and set priorities for workloads. (Setting the priority or Low, Medium, High)

 

Scope of Resource Governor

There is a clear scope in which Resource Governor works and it is important to understand the scope of it.

  • It is only manages the CPU Bandwidth and the RAM managed by the SQL Server 2008.
  • Resource Governor itself will not optimise any slow running queries.
  • No load balancing will be done between multiple SQL Server instances.
  • It does not divide the server into isolated pieces.
  • OLTP workloads. Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage %.

 

How Resource Governor Works

The following three concepts are fundamental to understanding and using Resource Governor:

  1. Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
  2. Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
  3. Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

 

ResGovGIF

 

Explanation (Step by Step):

  1. There is an incoming connection for a session (Session 1 of n).
  2. The session is classified (Classification).
  3. The session workload is routed to a workload group.(Workload Group 1)
  4. The workload group uses the resource pool it is associated with, for example, Pool 1.
  5. The resource pool provides and limits the resources required by the application, for example, Application 1

 

 

For more information on Resource Governor you may visit SQL Server 2008 Books Online.

 

I will write more about the Resource Governor very soon so keep a watch.