This chapter excerpt from Microsoft SQL Server 2008 Management and Administration, by Ross Mistry, is printed with permission from Sams Publishing, Copyright 2009.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
Click here to purchase the entire book.
Managing workloads and consumption with Resource Governor
Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation.
It is important to realize from the start that Resource Governor will only throttle processes if SQL Server is experiencing CPU or memory pressure, and it will only throttle incoming requests. For example, a process that consumes high CPU will not be throttled, but the next process may be throttled. Resource Governor is unable to throttle high I/O consumers (processes that read and write excessively from disk) in the SQL Server 2008 release, but this is planned for a future release. Resource Governor is exciting for DBAs as it will allow them to control:
- Runaway processes - Processes that could otherwise degrade overall SQL Server performance; for example, a badly written cursor would no longer be able to consume all CPU, or a query that does not have appropriate indexes could not cause a CPU spike, or a long-running query that returns a large rowset could not consume large amounts of memory in the process.
- Login priorities - The DBA can selectively give high priorities to groups of logins, and lower priorities to other logins; for example, reporting users could have a lower priority than logins associated with order entry applications.
- Unpredictable responses—By throttling high-resource consumers, SQL Server will be able to deliver predictable response times, as the high-resource consumers will not degrade SQL Server performance the way they would in an ungoverned SQL Server infrastructure.
|Note: Resource Governor only applies to the SQL Server Database Engine; it does not apply to Reporting Services, Analysis Services, or Integration Servers. Also, it is not instance aware; in other words, another instance running on the same SQL Server can still degrade performance on all instances.|
Resource Governor concepts
Before we start using Resource Governor, it is essential to have a good grasp of its concepts. Resource Governor has three components. In this section we will look at these components which will help us to understand them and implement Resource Governor in your enterprise.
- Workload - A workload is a process that can be identified by Resource Governor and throttled by it. It can consist of one or more processes. Resource Governor cannot throttle active processes; it can only throttle processes when they log in.
- Classifier function—A classifier function is a TSQL function that is invoked at login and will determine which workload group the process will execute in. Resource Governor can only use a single classifier function at one time, but you can dynamically change the classifier function or change the classifier function that Resource Governor uses. Workloads will be classified by the classifier function after the login is authenticated and logon triggers are executed.
Resource pool—Resource Governor parcels out CPU and RAM into groups called pools. Think of a resource pool as a SQL Server instance. A SQL Server shares resources among other instances running on a single physical machine. Similarly, Resource Governor shares resources among resource pools.
By default two resource pools are created for Resource Governor, a default and an internal resource pool. All internal database engine processes will run in the internal resource pool. You cannot modify the properties of the internal resource pool, nor can you create workload groups to run inside it. The default resource pool is the pool that all workloads will run in, unless you define another resource pool, and the classifier function classifies workloads to run in workload groups that exist in other resource pools. You can create your own user resource pools and modify their properties on the fly. Changes to user resource pools will apply to newly logged-on workloads and not to current workloads.
A resource pool will have a minimum set of resources assigned to it; these resources are nonoverlapping. Other pools will not be able to use these resources, and the minimum settings for all pools combined cannot exceed 100 percent. A resource pool will also have a maximum set of resources, which are shared with other pools. Although a maximum resource setting for a particular pool can be 100 percent, the effective maximum will likely be less. Consider the range of pool values in Table 16.1.
Table 16.1 - Minimum and Maximum Pool Values
|Name||Minimum||Maximum||Effective Max||Shared Max|
|Resource Pool A||20||50||50-30-0=20||20 (50-20)|
|Resource Pool B||30||70||70-20-0=50||20 (70-50)|
- Resource Pool A will have a minimum value of 20 for CPU for example, and an effective max of 20 (the maximum for Pool A subtract the minimum for Pool B). We subtract the minimum of Pool B as it and the minimum for the default pool as these are dedicated to these pools and are not overlapping with the other pools. This means that it will share 20 percent of its resources with the other pools if it is not using them. Workload groups are placed in resource pools, and can be dynamically moved from one resource pool to another. Note that if there is an error in your classifier function, or if the classifier function is unable to classify a workload, it will run in the default resource pool.
- Workload Group - A workload group is a container in which one or more workloads will run. A workload group has properties associated with it, like Max CPU, degree of parallelism, and so on, and you can monitor the resource consumption of workload groups inside a resource pool. This is valuable as it will allow you to move a workload from one workload group to another, or a newly created workload group. As with resource pools, by default there are two workload groups, the internal workload group, and the default workload group.
You cannot modify the internal workload group, but you can monitor it. The default group will handle all unclassified workloads.
Resource Governor scenarios
As Resource Governor is a new feature in SQL Server 2008, most DBAs will want to understand where it will apply within their SQL Server infrastructure. Resource Governor will allow for predictable performance by allowing DBAs to "bucket" their workloads into resource pools, which will have preset limits on the amount of CPU and RAM they can consume. Consider a case where the DBA is using backup compression, which is a high-CPU consumer. While the backups are occurring, they will consume CPU that would otherwise be available to other workloads using the SQL Server. The net result is that all workloads using SQL Server will have degraded performance while the backup process is running. If the DBA were to implement a resource pool with a max CPU usage of 25 percent, the backup process would be throttled and the CPU hit while the backup is running would be considerably less than if you weren't using Resource Governor. The end result is that the backup process would take longer, but the other workloads running while the backup is in operation would continue to offer the same performance levels as before the backup started.
Also consider a case where a cursor or WHILE loop is missing a FETCH NEXT, or increment step. In this case the cursor or WHILE loop would execute the same portion of code and never exit the cursor or WHILE loop. Such a process, called a runaway process, would pre-empt other processes and consume all the CPU. Resource Governor would limit the impact of such runaway processes by limiting their priority and the resources these runaway processes could take.
Resource Governor can also be used to classify workloads and place them in workload groups, thereby isolating these workloads from other processes. For example, by placing a reporting workload in its own workload group, you can set limits on its workload group and minimize the impact of other workloads in other work load groups from adversely affecting the reporting workload. By doing this you can ensure predictable performance for the reporting workload. Another example, a high CPU process could manage so that other workloads would be able to get the CPU resource they require.
By isolating workloads into their own groups it is possible to monitor them and tune the resources that they consume. Resource Governor will allow you to closely monitor CPU and memory consumption of workloads in workload groups so you can correctly allocate resource to this workload to improve the overall performance of all workloads on your SQL Server.
While monitoring workloads with Resource Governor, you can selectively prioritize workloads as Low, Medium, or High, and then create workload groups for prioritized workloads. This will ensure that high priority processes complete faster than lower priority processes. Prior to Resource Governor it was impossible to monitor or perform such prioritizing of your workloads.
As you will learn, the best way to deploy Resource Governor is to place all workloads in the default resource pool, observe their resource requirements, and then create resource pools that are appropriate for their resource demands, and resource pools that will limit the impact of these workloads on the entire SQL Server. After these resource pools have been created, these workloads can be moved into these resource pools and SQL Server will dynamically throttle new workloads as they exceed their resource limits.
ABOUT THE AUTHOR
Ross Mistry is a principal consultant at Convergent Computing, bestselling author and SQL Server MVP. He focuses on implementing SQL Server, Active Directory and Exchange solutions for fortune 500 organizations residing in the Silicon Valley.