Chapter 16. Resource Governor
In this chapter, we’ll cover
- Resource Governor overview
- Classifier functions
- Workload groups
- Resource pools
- Monitoring resource usage
Throughout this book, we’ve looked at a number of performance-management tools used for observation and/or simulation purposes. What’s missing from those tools is a mechanism to control resource usage based on the connection source, now possible using Resource Governor, introduced in the Enterprise Edition of SQL Server 2008.
In this chapter, we’ll begin with an overview of Resource Governor before concentrating on its major components: the classifier function, workload groups, and resource pools. We’ll conclude the chapter with an example implementation using T-SQL script and cover some of the monitoring techniques used.
A common (and unfortunate) situation that DBAs often encounter in production systems is a single runaway query that flatlines server performance to the detriment of all users. Unless the offending query can be identified and killed, users are effectively at the mercy of the query completing. To make matters worse, there’s nothing stopping the same query from occurring over and over again, with the end result being an unpredictable experience for end users and a frustrating one for DBAs.