Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Resource Governor – Part 02 – Components Explained

with 3 comments

As I stated in my last post, there are a few concepts in regards to Resource Governor that should be understood before proceeding into enabling Resource Governor, defining requirements, and creating pools and groups.

There are 3 main areas of interest for the Resource Governor and they are as follows:

  • Resource Pools – These are pool allocations of resources defined on the server that dictate the amount of CPU and Memory (MIN and MAX) that can be consumed by a member of that pool. The resource pool provides the connections with the minimum and maximum amount of resources (CPU and Memory) that can be consumed. For example, in the case of HR – which I briefly explained in the first post, we can set the Maximum CPU to 50% and the Maximum Memory to 30% (probably not a good idea as you are really restricting HR’s ability to complete requests in a timely fashion – but just for the sake of example here) and give Marketing 100% of both CPU and Memory. Now Marketing will be able to complete requests without having to worry about HR getting in the way too much (with regards to memory and CPU usage – this does not apply to locking or other contentious issues that can occur).
    There are 2 Resource Pools defined on SQL Server when installed, these resource pools are named ‘INTERNAL’ and ‘DEFAULT’. The INTERNAL pool is used by internal processes of SQL Server itself and this pool cannot be altered in any way. The INTERNAL pool trumps all other resource pools on the server – meaning that the INTERNAL pool is considered critical and will break the rules of allocation when deemed necessary to complete internal tasks. The DEFAULT resource pool is just as it sounds. It is the default pool used by connection requests if there are no other resource pools defined or the connection cannot be classified into any other pool. The DEFAULT pool cannot be created or dropped but it can be modified.
  • Workload Groups – These are containers for connection requests and are assigned to a Resource Pool. The connection requests are classified by a Classifier Function (see below) upon connecting and assigned to a Workload Group. Resource Pools contain workload groups and dictate the resources allocated to a connection request via the group the connection is classified to. Workload Groups can only exist in 1 Resource Pool – but can be moved from 1 pool to another if need be, however Resource Pools can contain many Workload groups.
    Workload Groups also have the ability to govern resource allocation to connections by providing provisions for Importance of the request, the maximum requests allowed for the group, CPU time (in seconds), memory grants, grant timeouts, and Degree of parallelism (more on these features and limitations of them in a later post).
    Much the same as Resource Pools, 2 Workload Groups are defined when SQL Server 2008 is installed – these are called INTERNAL and DEFAULT. The INTERNAL group is part of the INTERNAL Resource Pool and cannot be modified in any way – but can be monitored (more on monitoring in a future post). The DEFAULT workload group is part of the DEFAULT Resource Pool and is used when the connection cannot be classified into a group, the group being assigned to the connection by the Classifier Function does not exist, or there is a failure to classify the connection into an existing group.
  • Classifier Functions – a classifier function is a user defined scalar function that classifies incoming connection requests. It’s based on user written requirements and the results classify the connection to a workload group – by returning the Workload Group name to the Resource Governor. If criteria for a connection fail to classify the connection – the DEFAULT workload group is used by SQL Server (more on writing the classifier function(s) in an upcoming post). The classifier function cannot change the requirements for assigning connections to the INTERNAL workload group – and you also cannot manually assign connections to the INTERNAL workload group – it is for internal processes only. As stated above, the classifier function returns the name of the workload group that the connection should be assigned to – and this is how the Resource Governor knows what Workload Group to assign the connection to.

It is important to note that when defining Resource Pools and Workload Groups you must keep in mind the requirements of the business as well as any service level agreements that have been signed with the business. If a particular department has an agreement in place with the database team and system resources are not allocated properly to that department – you could end up in breach of contract and this may cause more problems than you would like to deal with.

When planning out resource allocation for the business it is important that you have a good understanding of resource requirements for all users and groups, average system usage, max system usage, idle times, and scheduled maintenance and job periods etc in order to deliver an optimal system to the business. You can easily modify settings within the Resource Governor, but it is a better solution to have proper limits and resource allocation in place to begin with rather than trying to tweak these settings over time. Resource Governor should be transparent to the many users (in most cases) of SQL Server and will save headaches to DBA’s and support staff going forward if the proper leg work is done before implementation.

In my next post we’ll take a closer look at what a Classifier function is, what it can do, and considerations when writing Classifier Functions.


Written by Simon Worth

July 4, 2008 at 10:23 am

3 Responses

Subscribe to comments with RSS.

  1. […] my second post on Resource Governor for SQL Server 2008, I briefly went over some key concepts to understand how […]

  2. […] a comment » In my second post on Resource Governor for SQL Server 2008, I briefly went over some key concepts to understand how […]

  3. […] my second post on Resource Governor for SQL Server 2008, I briefly went over some key concepts to understand how […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: