Solutions provider takeaway: Allocating resources in Microsoft SQL Server 2008 R2 is a very different process than it was in SQL Server 2005. Using the Resource Governor, solutions providers have a practical method for managing CPU and memory in SQL Server 2008 R2.
Resource consumption is one of the fundamental problems that has long plagued solutions providers working with SQL Server. Any server, whether it's physical or virtual, has a finite pool of CPU and memory at its disposal. In the past, this simple fact was especially troublesome for SQL Server, which is often a very resource-intensive application.
SQL Server resource consumption isn't so much of an issue if the server is only hosting a single database. But it really becomes an issue if multiple databases are in use, because the various databases all compete for the same set of CPU and memory.
In SQL Server 2005, the commonly accepted solution to this problem is to create a separate SQL Server instance for each database, using processor affinity to allocate resources to each database instance. The problem with this technique is that once resources are allocated to a SQL Server instance, they become unavailable to other SQL instances. Consequently, if one database has an especially heavy workload, it is unable to borrow CPU resources from other SQL instances on the server that may not be doing anything at the moment.
Some solutions providers have also attempted to use server virtualization as a way to allocate resources to individual SQL Server databases. In this model, each virtual machine (VM) hosts a single SQL Server database. This method also works, but the VMs and their operating systems consume some server resources that SQL Server could have used. Furthermore, depending on which virtualization software you're using, dynamic allocation of resources on an as-needed basis may be difficult or impossible.
Microsoft has finally created a viable solution to the resource allocation problem in Microsoft SQL Server 2008 R2 by introducing a new component called the Resource Governor. The Resource Governor, which is accessible through the Microsoft SQL Server Management Studio, defines resource pools, each of which contain workload groups, as shown in Figure A.
Figure A: The Resource Governor makes it practical to manage Microsoft SQL Server 2008 R2 CPU and memory resources.
As you can see from the figure above, Microsoft SQL Server 2008 R2 defines two resource pools by default: a system resource pool and a default resource pool. As the name implies, a resource pool is a collection of server resources that are available to the workgroups within the pool.
Each resource pool is configured with a minimum and a maximum value for CPU resources and for memory resources. These values are expressed as percentages, with 100% being equal to the server's entire set of hardware resources.
The minimum value for a pool represents a percentage of CPU or memory resources that are specifically dedicated to the pool and are available to the pool at all times. In situations where multiple resource pools are configured, the sum total of the minimum values assigned to all pools cannot exceed 100%. Any resources that are not claimed by minimum values are freely available across all resource pools.
To prevent a Microsoft SQL Server 2008 R2 database from consuming all of the system resources that aren't reserved as minimum values, solutions providers can express a maximum value. A maximum value represents the highest percentage of CPU or memory resources that the pool can consume. But it is important for solutions providers to realize that, in spite of any maximum values that they might set, a pool can trigger brief CPU spikes of up to 100%. These spikes are nondisruptive and are considered normal behavior.
You might have noticed in the figure above that, by default, Microsoft SQL Server 2008 R2 contains two resource pools, and each resource pool contains a workload group. The Resource Governor classifies inbound requests and routes the requests to the appropriate workload groups, based on the nature of the requests. The implication is that you are no longer limited to allocating resources on a per-database level (although you can). You can actually allocate different pools of resources to different types of database requests. For example, solutions providers might allocate more CPU time to a database lookup function than to a database write function.
The one big downside to the Microsoft SQL Server 2008 R2 Resource Governor is that it is not an administrative tool that solutions providers can enable and configure with a few mouse clicks. Classifying inbound requests and then moving those requests to the appropriate workload group requires a significant amount of scripting. Thankfully, Microsoft provides a series of MSDN articles that show you how to create the necessary scripts.
About the expert:
Brien M. Posey, MCSE, is a five-time recipient of Microsoft's Most Valuable Professional award for his work with Exchange Server, Windows Server, Internet Information Services (IIS), File Systems and Storage. Posey has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal website at www.brienposey.com.