Understanding How the SQL Server Resource Governor Works

By: Dusan Petkovic


One of the biggest problems in relation to SQL Server performance tuning is trying to manage resources with the competing workloads on a shared database server. You can solve this problem using either server virtualization or several instances. In both cases, it is not possible for an instance to ascertain whether the other instances (or virtual machines) are using memory and the CPU. Resource Governor manages such a situation by enabling one instance to reserve a portion of a system resource for a particular process.

Generally, Resource Governor enables DBAs to define resource limits and priorities for different workloads. That way, consistent performance for processes can be achieved.

Resource Governor has two main components:

  •     Workload groups
  •     Resource pools

When a process connects to the Database Engine, it is classified and then assigned to a workload group based on that classification. (The classification is done using either a built-in classifier or a user-defined function.) One or more workload groups are then assigned to specific resource pools (see Figure 1).

0609_001

Figure 1.  Architecture of Resource Governor

 

NOTE

Only CPU and memory can be managed by Resource Governor. In other words, the tool doesn’t support I/O and network system resources.

As you can see in Figure 1, there are two different workload groups:

  •     Internal group
  •     Default group

The internal group is used to execute certain system functions, while the default group is used when the process doesn’t have a defined classification. (You cannot modify the classification for the internal group. However, monitoring of the workload of the internal group is possible.)

 NOTE

The internal and default groups are predefined workload groups. In addition to them, the tool allows the specification of 18 additional (user-defined) workload groups.

A resource pool represents the allocation of system resources of the Database Engine. Each resource pool has two different parts, which specify the minimum and maximum resource reservation. While minimum allocations of all pool resources cannot overlap, the sum of them cannot exceed 100 percent of all system resources. On the other hand, the maximum value of a resource pool can be set between its minimal value and 100 percent.

Analogous to workload groups, there are two predefined resource pools: the internal pool and the default pool. The internal pool contains the system resources, which are used by the internal processes of the system. The default pool contains both the default workload group and user-defined groups.

 

Leave a Reply