Having sufficient memory and scheduler resources available is
of paramount importance in having a system that runs well. Although SQL Server
and the SQLOS have many built-in algorithms to distribute these resources
equitably, you often understand your resource needs better than the SQL Server
Database Engine does.
Resource
Governor Overview
SQL
Server 2008 Enterprise Edition provides you with an interface for assigning
scheduler and memory resources to groups of processes based on your
determination of their needs. This interface is called the Resource Governor,
which has the following goals:
■ Allow monitoring of resource consumption per workload, where
a workload can be defi ned as a group of requests.
■ Enable workloads to be prioritized.
■ Provide a means to specify resource
boundaries between workloads to allow
predictable execution of those workloads where there might otherwise be
resource contention
■ Prevent or reduce the probability of
runaway queries.
The Resource
Governor’s functionality is based on the concepts of workloads and resource
pools, which are set up by the DBA. Using just a few basic DDL commands, you
can defi ne a set of workload groups,
create a classifier function to determine which user sessions are members of
which groups, and set up pools of resources to allow each workload group to
have minimum and maximum settings for the amount of memory and the percentage
of CPU resources that they can use.
Enabling
the Resource Governor
The Resource Governor is enabled using the DDL statement ALTER RESOURCE GOVERNOR. Using
this statement, you can specify a classifier function to be used to assign
sessions to a workload, enable or disable the Resource Governor, or reset the
statistics being kept on the Resource Governor.
Classifier
Function
Once
a classifier function has been defi ned and the Resource Governor enabled, the
function is applied to each new session to determine the name of the workload
group to which the session will be assigned. The session stays in the same
group until its termination, unless it is assigned explicitly to a different
group. There can only be a maximum of one classifi er function active at any
given time, and if no classifier function has been defi ned, all new sessions are assigned to a default group. The
classifier function is typically based on properties of a connection, and
determines the workload group based on system functions such as SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), and IS_MEMBER(), and on property functions like LOGINPROPERTY and CONNECTIONPROPERTY.
Workload Groups
A workload group is just a name defined by a DBA to allow multiple connections to
share the same resources. There are two predefined workload groups in every SQL
Server instance:
■ Internal group This group is used for the internal activities of SQL Server.
Users are not able to add sessions to the internal group or affect its resource
usage. However, the internal group can be monitored.
■ Default group All
sessions are classified into this group when no other classifier rules could be
applied. This includes situations where the classifier function resulted in a
nonexistent group or when there was a failure of the classifier function.
Many sessions can be assigned to the same
workload group, and each session can start multiple sequential tasks (or
batches).
Resource Pools
A resource
pool is a subset of the physical resources of the server. Each pool has two
parts. One part does not overlap with other pools, which enables you to set a
minimum value for the resource. The other part of the pool is shared with other
pools, and this allows you to defi ne the maximum possible resource consumption.
The pool resources are set by specifying one of the following for each
resource:
■ MIN or MAX for CPU
■ MIN
or MAX for memory percentage
MIN
represents the minimum guaranteed resource availability for CPU or memory and
MAX represents the maximum size of the pool for CPU or memory.
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.
Resource
Governor Controls
The actual limitations of resources are controlled by your
pool settings. In SQL Server 2008, you can control memory and CPU resources,
but not I/O. It’s possible that in a future version, more resource controls
will become available. There is an important difference between the way that
memory and CPU resources limits are applied.
You can think of the memory specifications for a pool as hard
limits, and no pool will ever use more than its maximum memory setting. In
addition, SQL Server always reserves the minimum memory for each pool, so that
if no sessions in workload groups are assigned to a pool, its minimum memory
reservation is unusable by other sessions.
However, CPU limits are soft limits, and unused scheduler
bandwidth can be used by other sessions. The maximum values are also not always
fixed upper limits. For example, if there are two pools, one with a maximum of
25 percent and the other with a maximum of 50
percent, as soon as the first pool has used its 25 percent of the
scheduler, sessions from groups in the other pool can use all the remaining CPU
resources. As soft limits, they can make CPU usage not quite as predictable as
memory usage. Each session is assigned to a scheduler, with no regard to the
workload group that the session is in. Assume a minimal situation with only two
sessions running on a dual CPU instance. Each will most likely be assigned to a
different scheduler, and the two sessions may be in two different workload
groups in two different resource pools.
Assume that the session on CPU1 is from
a workload group in the first pool that has a maximum CPU setting of 80 percent,
and that the second session, on CPU2, is from a group in the second pool with a
maximum CPU setting of 20 percent. Because these are only two sessions, they
each use 100 percent of their scheduler or 50 percent of the total CPU
resources on the instance. If CPU1 is then assigned another task from a
workload group from the 20 percent pool, the
situation changes. Tasks using the 20 percent pool have 20 percent of
CPU1 but still have 100 percent of CPU2, and tasks using the 80 percent pool
still have only 80 percent of CPU1. This means tasks running from the 20
percent pool have 60 percent of the total CPU resources, and the one task from
the 80 percent pool has only 40 percent of the total CPU resources. Of course,
as more and more tasks are assigned to the schedulers, this anomaly may work
itself out, but because of the way that scheduler resources are managed across
multiple CPUs, there is much less explicit control.
For testing and troubleshooting purposes, there may be times
you want to be able to turn off all Resource Governor functionality easily. You
can disable the Resource Governor with the command ALTER RESOURCE GOVERNOR DISABLE. You can then re-enable the Resource Governor with the
command ALTER RESOURCE GOVERNOR RECONFIGURE. If you want to make sure the Resource Governor stays
disabled, you can start your SQL Server instance with trace fl ag 8040 in this
situation. When this trace fl ag is used, Resource Governor stays in the OFF
state at all times and all attempts to reconfigure it fails. The same behavior
results if you start your SQL Server instance in single-user mode using the –m and –f flags. If the Resource Governor is disabled, you should
notice the following behaviors:
■ Only the internal
workload group and resource pool exist.
■ Resource Governor configuration metadata are
not loaded into memory.
■ Your classifier function is never executed
automatically.
■ The Resource Governor metadata is visible
and can be manipulated.
