Resource Governor 2008 – First look

I have been testing SQL Server 2008 and I must admit, I've been impressed with the new features. I've written about some of the new functionality in past blog entries. One feature that I had not tested before was the Resource Governor. This allows us to control which applications use which resources on the Windows Server and in what proportion. Let's see how we can use it.

With Resource Governor we can limit the resources an application uses, while allowing other more important applications more of the resources such as CPU and Memory usage. This can help minimize the effects of runaway queries or memory intensive code in a low priority application. Higher priority applications will be able to get the resources they need when they need it. The neat thing about the Resource Governor is that it will only kick in if there is contention to deal with. If only a single application is running there is no need to limit resources. As soon as multiple applications are running and competing for resources, it will do its job.

To set up the Resource Governor, we need to enable it and define Resource Pools , Workload Groups and a Classifier Function. A Resource Pool defines the physical resources available on the server. You can specify minimum and maximum CPU and Memory usage. A Workload Group allows you to assign certain criteria to an application, for instance,  a priority or a limit to simultaneous requests. Workload Groups are assigned to a single Resource Pool. We then need to create a Classifier Function to assign applications to Workload Groups. There can be only one Classifier Function in use at a time. If an application is not identified by the Classifier Function, it will use the default Resource Pool.

As part of the setup, I was able to create a Resource Pool that would limit CPU and Memory to 10% of that available. Within that Resource Pool, I was then able to create a Workload Group with a priority of "Low" and a maximum Memory Grant of 25% which would limit any memory request from the pool to a quarter (of the 10%). Then I was able to create a Classifier Function to identify which applications should use the new Workload Group assigned to the new Resource Pool. I used some simple criteria based on application name.

Then the actual test. Using Performance Monitor in Windows Server 2008, I was able to track CPU usage by the new Resource Pool. I launched an application that matched the criteria of the Classifier Function but saw that it could use as much CPU as it needed because it was the only application using SQL Server at that time. I then launched another application that did not match the criteria and saw that the first application was then limited to 10% CPU and the second was not limited.

In this way, I used Resource Governor to keep a low priority application in check when contention occurred, allowing more important applications to excel.  

Yes, this is one Governor that gets my vote!

Later, Brian

Recent posts:

Intellisense – how good is it?

Let’s be thankful for SP3 – when it comes…

If it works, don’t fix it – the new Schema object