Microsoft® SQL Server® 2012 Bible

1133

Chapter 50: Resource Governor

50

Minimum Rate for any resource pool. This example illustrates how resources can share when

no workload for any process is available.

TABLE 50 -1 Example Showing Resource Sharing Among Pools

Resource Pool Minimum Percentage Maximum Percentage

Effective Maximum

Percentage Shared Percentage

Internal 0 100 100 100

Default 0 100 25 25

Custom Pool 1 10 100 35 25

Custom Pool 2 35 90 50 15

Custom Pool 3 30 80 35 5

For example, referring to Table 50-1, Custom Pool 1 has a minimum percentage of 10 percent

and a maximum of 100 percent; subtract Custom Pools 2’s and Custom Pool 3’s minimums

(100 - 35 - 30 = 35).

What’s New with the Resource Governor in SQL

Server 2012

There are a few subtle changes in the Resource Governor in SQL Server 2012. These changes give

greater performance controls in multi-tenancy environments such as a private cloud:

■ (^) Support for up to 64 user-defi ned resource pools (64-bit editions only).
■ (^) Greater CPU usage control, namely the ability to now set affi nity to a particular scheduler,
group of schedulers, or a NUMA node.
■ (^) CPU Capping for predictable performance. You can now specify a hard cap on CPU band-
width that all requests in the resource pool will receive, regardless of whether or not there
are other workloads running on the instance. In the previous release, MAX_CPU_PERCENT
was only enforced if there were other workloads competing for CPU resources. Specifi cally
providing a hard cap allows for predictable resource allocations.
■ (^) Memory allocation controls now extend to all areas except for the buffer pool and column
store cache. In previous versions this control only extended to query grant memory.
You can create and manage resource pools with T-SQL using CREATE, DROP, and ALTER
statements. After you issue the T-SQL commands to the add, alter, and drop resource pools,
you must get the Resource Governor to reconfi gure with ALTER RESOURCE GOVERNOR
RECONFIGURE. The following statement creates a resource pool named CustomPool1 that
simply sets the maximum CPU value for this resource pool at 30 percent.
c50.indd 1133c50.indd 1133 7/31/2012 10:22:30 AM7/31/2012 10:22:30 AM
http://www.it-ebooks.info