Resource Governor in SQL Server 2012

Major improvement in Resource Governor is the increase of Maximum number of resource pools to 64 in 64 bit version of SQL Server. In the previous version it was only 20 pools. However, for the 32 but version still the previous limit exists.

Now you can configure MAX_CPU_PERCENT for a resource pool. With new parameter CAP_CPU_PERCENT, users can cap CPU usage by a pool, even when there is no contention on the box.

ALTER RESOURCE POOL resource_pool_name

WITH (CAP_CPU_PERCENT = 40);

Above code will set the resource pool resource_pool_name for maximum cpu percentage of 40 regardless of whether there are any workloads are running or not.

Users now have the option of setting the affinity to a scheduler, group of schedulers, or a NUMA node. If user wanted to set the affinity of a resource pool to schedulers 5 through 7 only, user can use following code.

ALTER RESOURCE POOL resource_pool_name

WITH (AFFINITY SCHEDULER = (5 TO 7));