Resource Governor
SQL Server provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.
For a description of the new Resource Governor features in SQL Server 2012, see Resource Governor in SQL Server 2012.
Benefits of Resource Governor
Resource Governor enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.
In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.
Resource Governor Constraints
This release of Resource Governor has the following constraints:
Resource management is limited to the SQL Server Database Engine. Resource Governor can not be used for Analysis Services, Integration Services, and Reporting Services.
There is no workload monitoring or workload management between SQL Server instances.
Limit specification applies to CPU bandwidth and memory managed by SQL Server.
OLTP workloads. Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage %.
Resource 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 2012 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 2012 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.
.gif "Resource Governor Functional Components")
Resource Governor Tasks
Task Description |
Topic |
|---|---|
Describes how to enable Resource Governor. |
|
Describes how to disable Resource Governor. |
|
Describes how to create, alter, and drop a resource pool. |
|
Describes how to create, alter, move, and drop a workload group. |
|
Describes how to create and test a classifier user-defined function. |
|
Describes how to configure Resource Governor using a template. |
|
Describes how to view Resource Governor properties. |