Microsoft SQL Server 2008 R2 Unleashed- P157: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 | 1504 CHAPTER 40 Managing Workloads with the Resource Governor resource pool this setting determines whether requests within one workload group run at a higher or lower priority than other workload groups within the same resource pool. MEDIUM is the default setting. Currently the weighting factors for each setting is LOW 1 MEDIUM 3 and HIGH 9. This means that the scheduler will attempt to execute sessions in workgroups with importance of HIGH three times more often than workgroups with MEDIUM importance and nine times more often workgroups with LOW importance. NOTE Try to avoid having too many sessions in groups with high importance or assigning high importance to too many groups because the sessions will likely end up getting only equal time on the scheduler as your medium and low priority sessions. Maximum Requests Specifies the maximum number of simultaneous requests allowed to execute in the workload group. The default setting 0 allows unlimited requests. CPU Time Specifies the maximum amount of CPU time in seconds that a request within the workload group can use. The default setting is 0 which means unlimited. Memory Grant Specifies as a percentage the maximum amount of execution grant memory that a single request can take from the resource pool. This percentage is relative to the amount of memory allocated to the resource pool. The allowed range of values is from 0 through 100. The default setting is 25. Execution grant memory is the amount of memory used for query execution not for data buffers or cached plans which can be shared by many sessions regardless of resource pool or workload group. Note that setting this value to 0 prevents queries with SORT and HASH JOIN operations in user-defined workload groups from running. It is also not recommended that this value be set greater than 70 because the server may be unable to set aside enough free memory if other concurrent queries are running. Grant Time-out Specifies the maximum time in seconds that a query .
