• Article

Resource Governor Concepts

The following three concepts are fundamental to understanding and using Resource Governor:

  • Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.

  • Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.

  • Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

Note

Resource Governor does not impose any controls on a dedicated administrator connection (DAC). There is no need to classify DAC queries, which run in the internal workload group and resource pool.

In the context of Resource Governor, you can treat the preceding concepts as components. The following illustration shows these components and their relationship with each other as they exist in the database engine environment. From a processing perspective, the simplified flow is as follows:

  • There is an incoming connection for a session (Session 1 of n).

  • The session is classified (Classification).

  • The session workload is routed to a workload group, for example, Group 4.

  • The workload group uses the resource pool it is associated with, for example, Pool 2.

  • The resource pool provides and limits the resources required by the application, for example, Application 3.

Resource Governor Functional Components

Resource Pools

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 or MAX 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 sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value 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 as the minimum of the configured MAX value of a pool and the sum of the MIN values of other pools 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. For more information, see Resource Governor DDL and System Views.

Workload Groups

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request. A workload group allows the aggregate monitoring of resource consumption and the application of a uniform policy to all the requests in the group. A group defines the policies for its members.

Note

User-defined workload groups can be moved from one resource pool to another.

Resource Governor predefines two workload groups: the internal group and the default group. A user cannot change anything classified as an internal group, but can monitor it. Requests are classified into the default group when the following conditions exist:

  • There are no criteria to classify a request.

  • There is an attempt to classify the request into a non-existent group.

  • There is a general classification failure.

Resource Governor also provides DDL statements for creating, changing, and dropping workload groups. For more information, see Resource Governor DDL and System Views.

Classification

Resource Governor supports the classification of incoming sessions. Classification is based on a set of user-written criteria contained in a function. The results of the function logic enable Resource Governor to classify sessions into existing workload groups.

Note

The internal workload group is populated with requests that are for internal use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group.

You can write a scalar function that contains the logic that is used to assign incoming sessions to a workload group. Before you can use this function, you must complete the following actions:

  • Create and register the function using the ALTER RESOURCE GOVERNOR statement. For more information, see ALTER RESOURCE GOVERNOR (Transact-SQL).

  • Update the Resource Governor configuration using the ALTER RESOURCE GOVERNOR statement with the RECONFIGURE parameter.

After you create the function and apply the configuration changes, the Resource Governor classifier will use the workload group name returned by the function to send a new request to the appropriate workload group.

Important

The client session may time out if the classification function does not complete within the specified time-out for the login. Login time-out is a client property and as such, the server is unaware of a time-out. A long-running classifier function can leave the server with orphaned connections for long periods. It is important that you create classifier functions that finish executing before a connection time-out.

The user-defined function has the following characteristics and behaviors:

  • The user-defined function is evaluated for every new session, even when connection pooling is enabled.

  • The user-defined function gives workload group context for the session. After group membership is determined, the session is bound to the workload group for the lifetime of the session.

  • If the user-defined function returns NULL, default, or the name of non-existent group the session is given the default workload group context. The session is also given the default context if the function fails for any reason.

  • The function should be defined with server scope (master database).

  • The classifier user-defined function designation only takes effect after ALTER RESOURCE GOVERNOR RECONFIGURE is executed.

  • Only one user-defined function can be designated as a classifier at a time.

  • The classifier user-defined function cannot be dropped or altered unless its classifier status is removed.

  • In the absence of a classifier user-defined function, all sessions are classified into the default group.

  • The workload group returned by the classifier function is outside the scope of the schema-binding restriction. For example, you cannot drop a table, but you can drop a workload group.

Important

We recommend enabling the Dedicated Administrator Connection (DAC) on the server. The DAC is not subject to Resource Governor classification and can be used to monitor and troubleshoot a classifier function. For more information, see Using a Dedicated Administrator Connection. If a DAC is not available for troubleshooting, the other option is to restart the system in single user mode. Although single user mode is not subject to classification, it does not give you the ability to diagnose Resource Governor classification while it is running.

Classification Process

In the context of Resource Governor, the login process for a session consists of the following steps:

  1. Login authentication

  2. LOGON trigger execution

  3. Classification

When classification starts, Resource Governor executes the classifier function and uses the value returned by the function to send requests to the appropriate workload group. For more information, see Considerations for Writing a Classifier Function.

Note

Information about the execution of the classifier function and LOGON triggers is exposed in sys.dm_exec_sessions and sys.dm_exec_requests.