Using the Resource Governor
The Resource
Governor is a new feature that was introduced with SQL Server 2008. It
is available only in the Enterprise, Developer, and Evaluation editions
of SQL Server. Although the Resource Governor does have a name similar
to the Query Governor and can limit execution of expensive queries like
the Query Governor, it is a more robust resource management mechanism
than the Query Governor. The goal of the Resource Governor is to be more
specific in what code it limits on the server and what code is allowed
to run unrestricted. We can identify the code to the server through
criteria such as which user is running the query, what program the user
is using, and what server or database roles the user is a member of.
Based on the criteria, we can limit the code as it executes on the
server by controlling things such as how many current requests there can
be and how much of the available CPU and memory resources they can
consume. To accomplish this goal, we will create Resource Pools,
Workload Groups, and a Classifier Function.
The terms Classifier Function, Workload Group, and Resource Pool,
may sound a little daunting at first, but they are easy to understand
once you have gone through the process. We’ll talk first about what each
of these terms means and then walk through an example configuration.
We’ll start with Resource Pool, but first, here is a quick
generalization of each term to help get you thinking:
Classifier Function A user-defined function written by us. It is run every time a session starts as part of the login process, and it uses logic based
on who the user is, what program the user is running, and so on, to
assign the session to a Workload Group, or any other logic we feel is
necessary to classify the session’s workload requirements.
Workload Group
Defines a “policy” for the session and its code. The policy first
assigns the “Resource Pool” (see the next bullet item) from which the
session will be allocated CPU and memory resources. It then defines a
suballocation of the memory from the associated Resource Pool for the
particular Workload Group, and sets maximum CPU time thresholds, memory
resource time-outs, and maximum degree of parallelism policies.
Resource Pool Controls the availability of CPU and Memory resources to the Workload Groups assigned to use that particular Resource Group.
Resource Pools allow us to
allocate a portion of the server’s total CPU “bandwidth” and Memory to
the statements submitted through a client connection. SQL server has two
primary resources that are directly consumable by client code: CPU and
Memory. Some applications, and therefore the statements submitted by
those applications to the server, are “mission critical.” Other
applications may be important, but not critical, while still more
applications may be unimportant or trivial. The problem is that
sometimes it’s the programs we care less about that can consume an
unacceptable level of resources on the server. If that happens, they can
prevent the more critical applications from having the resources they
need.
Resource Pools allow us
to define the minimum and maximum percentages of both CPU and memory
utilization. To reflect the resources required by these three different
kinds of workloads suggested above (mission critical, important, and
trivial), we could create three Resource Pools. The first or, Mission
Critical Resource Pool, would have a large percentage of CPU and Memory
resources available. We could then create the Important Resource Pool
and assign moderate percentages of CPU and Memory resources to it.
Third, we could create a Trivial Resource Pool that would have a very
limited percentage of CPU and Memory resources. It is important to note
that the Resource Pool is a way to configure the available resources,
not what code uses those resources. Client connections first get
assigned to a Workload Group by the Classifier Function. The Workload
Group the client session gets assigned to determines which Resource Pool
the client’s session will consume resources from. When multiple
Resource Pools are defined, their Minimum CPU and Memory resource
allocations cannot total more than 100%. The Maximum percentages are the
lesser of the requested maximum and the requested maximum minus the
minimum resources of the other pools.
Workload
Groups allow us to enforce a policy of execution on the sessions that
are assigned to it. That policy includes which Resource Pool the session
will allocate resources from. Multiple Workload Groups can be assigned
to the same Resource Pools and therefore “compete” for resources in that
pool. For that reason, a Workload Group can be assigned an “importance”
to help the Resource Governor to resolve arguments concerning which
Workload Group should get a resource first. Workload Group policies
determine how much of its associated Resource Pool’s memory its sessions
can use, how long a session can wait for memory if it is not currently
available, how many concurrent requests can be open for all sessions in
the Workload Group, and some others. The Workload Group can set a
maximum amount of CPU time (in seconds) that a session can use, but SQL
Server does not terminate code if it exceeds that time; it only
generates an event that we can capture using SQL Trace and report on.
To continue our example, we
could create three Workload Groups, and we could call them Mission
Critical Group, Important Group, and Trivial Group. In the configuration
of those Workload Groups, we could assign them to allocate resources
from the Mission Critical Resource Pool, Important Resource Pool, and
Trivial Resource Pool, respectively. We determine what Workload Group a
client session gets assigned to by creating a CLASSIFIER function.
The CLASSIFIER
function is a user-defined function that can use whatever logic we need
to determine the name of the Workload Group we would like to assign the
session to. As a client logs into his or her workstation and starts an
application that connects to SQL Server, a session is started on the
server on behalf of the user. As that session starts, during the login
process, SQL Server will call the CLASSIFIER function we create. The CLASSIFIER
function will use our logic to determine and then return the name of
the Workload Group the server should assign the session to. The logic
can be based on system functions such as SUSER_SNAME(), APP_NAME(), HOST_NAME(), IS_SRVROLEMEMBER, IS_MEMBER(), CONNECTIONPROPERTY, GETDATE(),
as well as any other code we need to determine what kind of workload
the session requires, and then direct it to the appropriate Workload
Group. (If you don’t know what some or all of the functions just named
are, look them up in the SQL Server documentation. While you are at it,
look for other useful system functions you could use to help classify
your client’s sessions.)
Hopefully, it is starting to make sense. Before we get started, there are a couple more things to mention:
Resource Governor is built into SQL Server but is not enabled by default. We use the ALTER RESOURCE GOVERNOR RECONFIGURE statement to enable it.
Resource
Governor has two Resource Pools and Workload Groups in its initial
configuration named “internal” and default.” The default Workload Group
is assigned to the default Resource Pool, and the “internal” Workload
Group is assigned to the internal Resource Pool.
The internal Resource Pool and Workload Group are reserved for internal SQL server processes and cannot be managed by us.
The
default Workload Group is the group to which sessions are assigned
whenever there is no other Workload Group appropriate for the session,
or the Workload Group name the CLASSIFIER function returns cannot be found.
SQL
Server provides a number of views that we can use to investigate and
monitor the Resource Governor configuration. They include but are not
limited to: sys.resource_governor_configuration,
sys.resource_governor_resource_pools,
sys.resource_governor_workload_groups,
sys.dm_resource_governor_workload_groups,
sys.dm_resource_governor_resource_pools,
sys.dm_resource_governor_configuration
Connections
made via the Dedicated Administrator Connection (DAC) are not subject
to the Classifier and do not run with limited resources. This can be
used when the Resource Governor has been configured incorrectly or needs
to be diagnosed.
To wrap it up, let’s look at an example. In this example we will:
Create a Resource Pool that guarantees a minimum amount of CPU and Memory resources for people using the Accounting App.
Create
a Workload Group that sets a policy for the Accounting application and
assigns it to the Accounting Resource Pool made in step 1.
Create a CLASSIFIER function that uses the APP_NAME() function to determine if the session being created is for our accounting application, Great Plains.
Assign our CLASSIFIER function to be used by the Resource Governor.
Enable the Resource Governor (so it will begin to use the new configuration).
After connecting with Great Plains, query the server to find out what group it was assigned to.
USE master;
GO
-- Step 1: Create the "Accounting" Resource Pool with:
-- 25% CPU Guaranteed (Minimum)
-- 50% Memory Guaranteed
CREATE RESOURCE POOL [Accounting] WITH
(min_cpu_percent=25,
max_cpu_percent=100,
min_memory_percent=50,
max_memory_percent=100);
GO
-- Step 2: Create the "Accounting" Workload Group with:
-- High Importance
-- 30 Seconds of CPU Time threshold
-- 100 Percent of the Memory in the Accounting Resource Pool
-- 30 Seconds timeout if waiting for memory
-- Using the Accounting Resource Pool
CREATE WORKLOAD GROUP [Accounting] WITH(group_max_requests=0,
importance=High,
request_max_cpu_time_sec=30,
request_max_memory_grant_percent=100,
request_memory_grant_timeout_sec=30)
USING [Accounting];
GO
-- Step 3: Create the Classifier Function return the Workload Group name:
-- Make sure to make the Classifier Function in the Master database
-- "Accounting" - If the APP_NAME() is like "Great Plains"
-- "default" - for everything else
CREATE FUNCTION dbo.ufnClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Group sysname
IF APP_NAME() LIKE '%Great Plains%'
SET @Group = 'Accounting'
ELSE
SET @Group = 'default'
RETURN @Group
END;
GO
-- Step 4: Assign our Classifier Function to be used by the Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[ufnClassifier]);
GO
-- Step 5: Enable the Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- Step 6: Query the system dynamic management views to see
-- which Workload Group and thus resource Pool
-- a live session from Great Plains was assigned to...
SELECT s.session_id, s.program_name,
w.name AS workload_group, p.name as pool_name
FROM sys.dm_exec_sessions AS s
JOIN sys.resource_governor_workload_groups as w
JOIN sys.resource_governor_resource_pools as p
on w.pool_id = p.pool_id
ON s.group_id = w.group_id
WHERE program_name='Great Plains';
session_id program_name workload_group pool_name
---------- ------------ -------------- ----------
61 Great Plains Accounting Accounting
Warning
The
Resource Governor is available only in the Enterprise, Evaluation, and
Developer editions. Test questions often like to trick you by playing
with the editions available in the criteria, so pay attention!
