October 10, 2011 | SQL Server

SQL Server v.Next (Denali) : Changes to Resource Governor

After reading the headline, you're probably quite excited – but sadly, no, Denali does not introduce the ability to govern I/O. But there are some cool additions here that you will be able to take advantage of. For now I'll just talk about these enhancements in short form but will hopefully elaborate on them later. Note that these changes are not present in CTP3 but should be available in the next publicly available build.

More Resource Pools

Under 64-bit only, you will now be able to have 64 resource pools (the current limit is 20). This number includes the default and internal pools, so really you can only create 18 resource pools today. The increased limit can help folks on larger hardware consolidate more instances into a single instance, and can help service providers offer more customers a "slice" of an instance's resources.

Scheduler Affinity

You will now have the ability to set affinity to a scheduler, group of schedulers, or a NUMA node. So for example, if you wanted to set the affinity of a certain resource pool to schedulers 9 through 12 only, you could say:

 ALTER RESOURCE POOL pool_name WITH (AFFINITY SCHEDULER = (9 TO 12));

As a warning of sorts, this can make your calculations for, as an example, MIN_CPU_PERCENT (which can't add to more than 100%) a lot more complex. There is also a new DMV so that you can inspect the affinity settings (only resource pools with explicit affinity settings will show in the result):

 sys.dm_resource_governor_resource_pool_affinity
 
Columns: 
    pool_id, processor_group, scheduler_mask

Real CPU capping

You will now be able to cap CPU usage by a pool, even when there is no contention on the box. Currently MAX_CPU_PERCENT is only enforced if there are other workloads competing for CPU resources. This will allow providers to offer more predictable chargeback if their customer is often the only one on the box, in which case the MAX_CPU_PERCENT limit is ignored. You will be able to set the cap this way:

 ALTER RESOURCE POOL pool_name WITH (CAP_CPU_PERCENT = 30);

This would prevent this pool from exceeding 30% CPU regardless of what other workloads are (or are not) running on the box.

Memory Control

Resource Governor will be able to control all memory allocations *except* buffer pool and column store cache. Today it only handles query grant memory, but now this coverage will be extended (mostly due to changes in the memory manager).