16.4. Resource pools

We come now to the final component of Resource Governor, the resource pool, created using the T-SQL command shown below. As you can see, the command is fairly simple, with arguments for min and max values for CPU and memory, the two resources under our control in SQL Server 2008.

CREATE RESOURCE POOL pool_name
[ WITH
    ( [ MIN_CPU_PERCENT = value ]
    [ [ , ] MAX_CPU_PERCENT = value ]
    [ [ , ] MIN_MEMORY_PERCENT = value ]
    [ [ , ] MAX_MEMORY_PERCENT = value ] )
]

There are two system pools, default and internal, and as with workload groups, the internal pool is for system usage only, and its resource limits cannot be modified. Given the importance of internal SQL Server system processes, its resource usage is not constrained, regardless of the resources reserved in other pools. In contrast, the default pool can be modified, with multiple user-defined workload groups in addition to the default workload group able to use it.

Before we look at what min and max actually mean in the context of resource pool usage (there's more to it than you may think), let's first define some important terms: effective maximum percentage and shared percentage. In doing so, let's look at an example of the two resource pools defined in table 16.1. The values in this table can represent either CPU or memory; the terms apply in both cases.

Table 16.1. Example pool configuration—internal pool excluded
 

Pool name	Min %	Max %
Default	0	100
Pool A	30	100
Pool B	60	75

Have a look at Pool A; its maximum value is specified as 100 percent; however, Pool B is configured with a minimum of 60 percent. It follows that Pool A could never receive more than 40 percent, hence the term effective maximum. In a similar vein, Pool B is configured with a maximum of 75 percent, but given Pool A's minimum value of 30 percent, it will never receive more than 70 percent.

The minimums of pools A and B added together total 90 percent; therefore, only 10 percent is left for pools to use over their minimum values. The 10 percent value is referred to as the total shared percentage and is used in calculating the effective maximum values. Essentially, effective maximums decrease as minimum values increase.

A poorly configured pool design with large minimum values may have the unwanted effect of starving resources from certain pools. The important point to take from this is that the best resource pool designs are usually the simplest, and like other configuration settings, they should be changed only for a good reason after a well-considered analysis. Later in the chapter we'll look at a plan of attack for deciding how to size these values. Table 16.2 includes the effective maximum values based on the pool design from table 16.1.

Table 16.2. Pool configuration with effective maximum values included
 

Pool name	Min %	Max %	Effective max %
Default	0	100	10
Pool A	30	100	40
Pool B	60	75	70

With these points in mind, let's look at further ramifications of minimum resource values on CPU and memory.

16.4.1. Effective minimum: memory considerations

You must take special care when configuring a resource pool with a minimum memory percentage. When SQL Server starts, the memory minimums for each pool are reserved up front, regardless of whether the memory is required, or even if there are no active workload groups using the pool. It follows that in a case where there are a number of unused pools with configured minimum memory values, there is potentially a large amount of memory that's unable to be accessed by pools that actually need it. In contrast, CPU limits are more fluid.

16.4.2. Effective minimum: CPU considerations

Consider figure 16.5, which shows the CPU usage of two resource pools in Performance Monitor.

What we're looking at here is a running query in the RP_Reporting pool (represented by the line that starts near 100 percent and drops down to around 15 percent). This pool is configured with a maximum CPU usage of 15 percent. In the left half of the screen, it's clearly using much more than that, in some cases 100 percent. About halfway across, we see the emergence of a query running in the RP_Sales pool (represented by the line that starts at 0 percent and increases to around 80 percent). At this point, the original query's CPU usage is throttled back dramatically, to around the 15 percent average value.

What's actually happening here is that Resource Governor is smart enough to figure out that there's no CPU contention, so it lets the first query use as much of the resource as it needs. As soon as a second query comes along, CPU contention occurs, at which point resource limits are applied.

Perhaps the most important point to learn from this is in regard to appropriate load testing. For example, testing the impact of a 30 percent CPU pool maximum is pointless unless there is something else running that throttles the pool's CPU usage down to 30 percent.

Finally, note that the resource values are to be interpreted as averages, not hard and fast limits; that is, monitoring will occasionally show limits being exceeded, while the average values should be maintained.

With this background, let's walk through a script to set up a Resource Governor scheme from start to finish.