Microsoft SQL Server 2008 R2 Unleashed- P156:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1494 CHAPTER 40 Managing Workloads with the Resource Governor NOTE Resource Governor is available only in the Enterprise Datacenter and Developer Editions of SQL Server 2008 and SQL Server 2008 R2. Overview of Resource Governor Resource Governor works by controlling the allocation of resources according to workloads. When a connection request is submitted to the Database Engine the request is classified based on a classification function. The classification function is a scalar function that you define via T-SQL. The classification function evaluates information about the connection for example login ID application name hostname server role to determine how it should be classified. After the connection request is classified it is routed to a workload group defined for that classification or if the connection cannot be classified it is routed to the default workload group . Each workload group is associated with a resource pool. A resource pool represents the physical resources of SQL Server currently in SQL Server 2008 the only physical resources available for configuration are CPU and memory and specifies the maximum amount of CPU and or memory resources that are to be allocated to a specific type of workload. When a connection is classified and put into the correct workload group the connection is allocated the CPU and memory resources assigned to it and then the query is passed on to the query optimizer for execution. This process is illustrated in Figure . FIGURE Overview of the Resource Governor. Resource Governor Components 1495 Resource Governor is designed to address the following types of resource issues which are commonly found in a database environment Runaway queries These resource-intensive queries can take up most or all of the server resources. Unpredictable workload execution This situation occurs when you have concurrent applications on the same server that are not isolated from each other and the resulting resource contention causes .
