Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Archive for the ‘Resource Governor’ Category

Resource Governor – Part 01 – Overview

with 2 comments

In this series of posts, I will review the Resource Governor – newly added to SQL Server 2008.

Of all the new features added to SQL Server 2008, one of the most intriguing would have to be the Resource Governor (RG). RG is only available in the Enterprise Edition of SQL Server, but this and many other features may be enough to justify the upgrade to enterprise – especially if you are interested in taking advantage of the full Business Intelligence feature set offered by 2008. To see a comparison list of features available in versions of SQL Server have a look here (PDF warning).

In this first post we will review the key concepts of Resource Governor. What it is and isn’t, as well as the components that make up the Resource Governor.

A quick definition of Resource Governor from 2008 BOL is
“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”

The Resource Governor allows DBA’s to implement rules on connection requests that come to the server. It gives the ability to control how resources are allocated to connection requests and can set floors and ceilings on server resources for Workload Groups. So, simply put, you can create priority for certain groups and restrict other groups from doing silly things 😉 .

Prior to 2008 there was little in the way of control over SQL Server resources. We had the ability to specify how much memory the entire server could use, how many CPU’s where available to SQL Server, I/O specific settings, and time thresholds for queries etc, but greater control for specific users and groups did not exist.

For example, if you knew that the Marketing department required a large share of CPU and Memory on SQL Server in order to run their reports and marketing campaigns – while HR required much less of the resources to update and insert records as well as run scheduled reports – you set timeouts for these queries and offered locking hints and priorities, but that was about all you could do without affecting entire server settings.

Now, with the Resource Governor, you can define these requirements within and automatically allocate floors and ceilings of resources when connections are made to SQL Server based on groups. So Marketing can have a greater share of resources in order to complete their tasks, while HR resource allocation is slightly less – or of less priority.

In order to use and experiment with RG, there are a few concepts that need to be understood as well as some initial setup to get RG up and running properly. In my next post we will review the concepts and components of SQL Server 2008 Resource Governor, and in subsequent posts these concepts will be further explained with examples of how to implement rules for resource allocation.

Advertisements

Written by Simon Worth

July 2, 2008 at 7:26 pm