SQL Server Resource Governor - Understanding Pool Usage

In an earlier posting, I discussed some of the basics of configuring the SQL Server 2008 Resource Governor. Since that posting, I've been spending a fair amount of time digging into the internals of the Resource Governor and understanding exactly how it works. It's been an interesting trip considering the limited amount of information available on the subject (let's hope that by RTM they improve this!), but I think I have finally uncovered enough information to make a useful post.

Resource Governor Architecture

As discussed in my earlier post, the Resource Governor is made up of Resource Groups and Resource Pools. When a user (or process) connects to SQL Server 2008, it is "classified" (either through a built-in classifier or a user-defined classifier function) and then assigned to a resource group based on that classification. One or more resource groups are then assigned to specific resource pools. The architecture looks like this:

There are 2 built-in resource groups, the Internal Group and the Default Group. The Internal group is used to execute certain system functions (such as the Lazywriter and Checkpoint Processes) and the Default group is used when the session does not have a defined classification. (For example, if you follow the steps in my earlier post and create a classifier function to assign the SQL Management Studio to a limited resource pool, all sessions that originate from an application other than management studio would be assigned to the Default group)

Resource Governor Misconceptions

One of the things that I believe will be a contentious issue when SQL Server 2008 hits the street is the way in which the Resource Governor affects connections. For example, if I configure a resource pool to limit the maximum CPU time of any process in the pool, I expect that the governor will honor that and stop anything that exceeds the value. In reality, what happens is an event is fired and the application developer is expected to capture the event and react accordingly. For example, say that you create a resource pool and you set the REQUEST_MAX_CPU_TIME_SEC = 5. What you would expect to happen is when a query reaches 5 seconds of CPU time it becomes terminated. In reality what happens is once the query exceeds the CPU time by 5 seconds (that is 5 seconds above the configured value, so in my example here it would be a minimum of 10 seconds of CPU time) a CPU Threshold Exceeded event is fired. You can look for these events using the SQL trace events that are part of the Performance Monitor tool.

Monitoring Resource Pools

You can use the Performance Monitor (the example that I am showing here is from Windows Server 2008 so it may be different than your environment) to monitor Resource Pool Statistics. Start Perfmon and add the counters from the SQL Server: Resource Pool Stats collection for the pool that you want to monitor as shown here:

Once you start the trace, and begin to see activity within the classified connection, you will see a screen that looks similar to the following:

You can use this data to determine how well the pool is working and get a good baseline for how the applications that use that pool are consuming resources.