Maximum and Effective Maximum Resource Percentage in
Resource Governor (SQL Server 2008)
By : Kasim Wirama, MCDBA
Resource governor is cool feature of SQL Server 2008 to provide predictable database performance by allocating resource consumption for database application. It is pretty useful when more than one application compete for shared resource such as memory and CPU. One classic example for perfect scenario of resource race is in a same SQL Server box, there are high activity rate of OLTP application and occasional reporting activity. Reporting activity requires high amount of CPU and memory when it is run so that OLTP application degrades in performance. To overcome this issue, there should be mechanism to give higher priority on memory and CPU resource for OLTP application but when OLTP application is under not busy state, more memory and CPU resource could be allocated for reporting activity. That’s exactly resource governor is about.
In resource governor, you can define MIN_CPU_PERCENT, MAX_CPU_PERCENT, MIN_MEMORY_PERCENT, and MAX_MEMORY_PERCENT. These parameters are for resource pool settings. Resource pool will be applied to pool name. By default SQL Server 2008 has 2 resource pool, i.e: internal and default resource pool. Internal resource pool is intended for internal operation for SQL Server engine. You can create your own resource pool. Back to resource pool parameters, notice MIN and MAX settings. MIN setting here will make sure minimal resource allocation for a resource pool. Remember that total of MIN setting for same resource (memory/CPU) for each resource pool should be 100%. MAX setting will define maximum value possible for a resource pool. The most interesting is about MAX. In real situation, it might unreach to MAX value although it is possible to happen. It is called effective maximum value. Effective maximum is calculated with the following formula
100 – MIN (max value or (total minimum of other resource pools excluding current resource pool and internal resource pool))
But this result should not exceed to maximum value of corresponding resource pool. For example I create 2 additional resource pool, i.e. OLTP and Reporting.
|
Resource pool |
MIN CPU percent |
MAX CPU percent |
|
Internal |
0 |
100 |
|
Default |
30 |
100 |
|
OLTP |
30 |
50 |
|
Reporting |
40 |
75 |
Effective maximum percent for internal is 100%. Effective maximum for Default is 100 – 70 = 30, effective maximum for OLTP is 100 – 50 = 50 and effective maximum for Reporting is 100 – 70 = 30.