Resource Governor Resource Pool
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.
Resource Pool Concepts
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
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
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.
Resource Pool Tasks
Task Description |
Topic |
|---|---|
Describes how to create a resource pool. |
|
Describes how to change resource pool settings. |
|
Describes how to delete a resource pool. |
See Also
Concepts
Resource Governor Workload Group
Resource Governor Classifier Function