Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Resource Governor Resource Pool

SQL Server 2012

In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.

A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.

A pool has two parts. One part does not overlap with other pools, which enables minimum resource reservation. The other part is shared with other pools, which supports maximum possible resource consumption. In this release of Resource Governor the pool resources are set by specifying one of the following for each resource:

  • MIN, MAX or CAP for CPU

  • MIN or MAX for memory

MIN and MAX represent the minimum guaranteed resource availability of the pool and the maximum size of the pool, respectively, for each of these resources.

The CAP value for CPU represents a hard maximum. Available CPU capacity above this value will not be used.

The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX and CAP values can be set anywhere in the range between MIN and 100 percent inclusive.

If a pool has a nonzero MIN defined, the effective MAX value of other pools is readjusted. The minimum of the configured MAX value of a pool and the sum of the MIN values of other pools is subtracted from 100 percent.

The following table illustrates the preceding concepts. The table shows the settings for the internal pool, the default pool, and two user-defined pools. The following formulas are used for calculating the effective MAX% and the shared %.

  • Min(X,Y) means the smaller value of X and Y.

  • Sum(X) means the sum of value X across all pools.

  • Total shared % = 100 - sum(MIN %).

  • Effective MAX % = min(X,Y).

  • Shared % = Effective MAX % - MIN %.

Pool name

MIN % setting

MAX % setting

Calculated effective MAX %

Calculated shared %

Comment

internal

0

100

100

0

Effective MAX% and shared% are not applicable to the internal pool.

default

0

100

30

30

The effective MAX value is calculated as: min(100,100-(20+50)) = 30. The calculated shared % is effective MAX - MIN = 30.

Pool 1

20

100

50

30

The effective MAX value is calculated as: min(100,100-50) = 50. The calculated Shared % is Effective MAX - MIN = 30.

Pool 2

50

70

70

20

The effective MAX value is calculated as: min(70,100-20) = 70. The calculated Shared % is Effective MAX - MIN = 20.

Using the preceding table as an example we can further illustrate the adjustments that take place when another pool is created. This pool is Pool 3 and has a MIN % setting of 5.

Pool name

MIN % setting

MAX % setting

Calculated effective MAX %

Calculated shared %

Comment

internal

0

100

100

0

Effective MAX % and shared % are not applicable to the internal pool.

default

0

100

25

25

The effective MAX value is calculated as: min(100,100-(20+50+5)) = 25. The calculated shared % is Effective MAX - MIN = 25.

Pool 1

20

100

45

25

The effective MAX value is calculated as: min(100,100-55) = 45. The calculated Shared % is Effective MAX - MIN = 25.

Pool 2

50

70

70

20

The effective MAX value is calculated as: min(70,100-25) = 70. The calculated Shared % is effective MAX - MIN = 20.

Pool 3

5

100

30

25

The effective MAX value is calculated as: min(100,100-70) = 30. The calculated Shared % is effective MAX - MIN = 25.

The shared part of the pool is used to indicate where available resources can go if resources are available. However, when resources are consumed they go to the specified pool and are not shared. This may improve resource utilization in cases where there are no requests in a given pool and the resources configured to the pool can be freed up for other pools.

Some extreme cases of pool configuration are:

  • All pools define minimums that in total represent 100 percent of the server resources. In this case the effective maximums are equal to minimums. This is equivalent to dividing the server resources into non-overlapping pieces regardless of resources are consumed inside any given pool.

  • All pools have zero minimums. All the pools compete for available resources and their final sizes are based on resource consumption in each pool. Other factors such as policies play a role in shaping the final pool size.

Resource Governor predefines two resource pools, the internal pool and the default pool.

Internal Pool

The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group, and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools.

Note Note

The internal pool and internal group resource usage is not subtracted from the overall resource usage. Percentages are calculated from the overall resources available.

Default Pool

The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group.

Note Note

The default group is alterable but it cannot be moved out of the default pool.

User-Defined Resource Pools

Resource Governor provides DDL statements for creating, changing and dropping resource pools.

Task Description

Topic

Describes how to create a resource pool.

Create a Resource Pool

Describes how to change resource pool settings.

Change Resource Pool Settings

Describes how to delete a resource pool.

Delete a Resource Pool

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.