CHAPTER 24
Resource Governor
Resource Governor provides a method for throttling applications at the SQL Server
layer by imposing limits on CPU, memory, and physical IO on different classifications
of connection. This chapter discusses the concepts the Resource Governor uses before
demonstrating how to implement it. We then look at how to monitor the effect that
Resource Governor has on resource utilization.
Resource Governor Concepts
Resource Governor uses resource pools to define a subset of server resources, workload
groups as logical containers for similar session requests, and a classifier function to
determine to which workload group a specific request should be assigned. The following
sections discuss each of these concepts.
Resource Pool
A resource pool defines a subset of server resources that sessions can utilize. When
Resource Governor is enabled, three pools are automatically created: the internal pool,
the default pool, and the default external pool. The internal pool represents the server
resources the instance uses. This pool cannot be modified. The default pool is designed
as a catch-all pool and is used to assign resources to any session that is not assigned to
a user-defined resource pool. You cannot remove this pool; however, you can modify
its settings. The default external pool is used to govern resources used by the rterm.exe,
BxlServer.exe, and python.exe processes, which are used by Machine Learning Services.
The default external resource pool can be modified, but not removed, and new external
resource pools can be added.

Resource pools allow you to configure the minimum and maximum amount of
resources (CPU, memory, and physical IO) that will be available to sessions that are
assigned to that pool. As you add additional pools, maximum values of existing pools are
transparently adjusted so they do not conflict with the minimum resource percentages
assigned to all pools. For example, imagine that you have configured the resource pools,
which are represented in Table 24-1, to throttle CPU usage.
In this example, the actual Max CPU % settings will be as you configured
them. However, imagine that you now add an additional resource pool, called
AccountsApplication, which is configured with a Min CPU % of 50 and a Max CPU %
of 80. The sum of the minimum CPU percentages is now greater than the sum of the
maximum CPU percentages. This means that the effective maximum CPU percentage
for each resource pool is reduced accordingly. The formula for this calculation is
Smallest( Default(Max), Default(Max) – SUM(Other Min CPU)), which is reflected
in Table 24-­2.
Table 24-1.  Resource Pools’ Simple Effective Maximum Percentages
Resource Pool∗
Min
CPU %
Max
CPU %
Effective
Max CPU %
Calculation
Default
Smallest(75,(100–0-25)) = 75
SalesApplication
Smallest(75,(100–0-0)) = 75
Default External
Smallest(75,(100–0-25)) = 75
∗The internal resource pool is not mentioned here since it is not configurable either directly or
implicitly. Instead, it can consume whatever resources it requires and has a minimum CPU of 0;
therefore, it does not impact the effective maximum CPU calculation for other pools.

Workload Group
A resource pool can contain one or more workload groups. A workload group
represents a logical container for similar sessions that have been classified as similar by
executing a classifier function, which is covered in the next section. For example, in the
SalesApplication resource pool mentioned earlier, we can create two workload groups.
We can use one of these workload groups as a container for normal user sessions while
using the second as a container for reporting sessions.
This approach allows us to monitor the groups of sessions separately. It also allows
us to define separate policies for each set of sessions. For example, we may choose
to specify that sessions used for reporting have a lower MAXDOP (Maximum Degree of
Parallelization) setting than the sessions used for standard users, or that sessions used
for reporting should only be able to specify a limited number of concurrent requests.
These settings are in addition to the settings we can configure at the resource pool level.
Classifier Function
A classifier function is a scalar function, created in the Master database. It is used to
determine which workload group each session should be assigned to. Every new session
is classified using a single classifier function, with the exception of DACs (dedicated
Table 24-2.  Resource Pools’ Effective Maximum Percentages After Implicit
Reductions
Resource Pool∗
Min
CPU %
Max
CPU %
Effective
Max CPU %
Calculation
Default
Smallest(100,(100-­
sum(25,50,0))) = 25
SalesApplication
Smallest(75,(100-50-0)) = 50
AccountsApplication 50
Smallest(80,(100-25-0)) = 75
Default External
Smallest(100,(100-­
sum(25,50,0))) = 25
∗The internal resource pool is not mentioned here since it is not configurable either directly or
implicitly. Instead, it can consume whatever resources it requires and has a minimum CPU of 0;
therefore, it does not impact the effective maximum CPU calculation for other pools.

administrator connections), which are not subject to Resource Governor. The classifier
function can group sessions based on virtually any attribute that it is possible to code
within interpreted SQL. For example, you may choose to classify requests based upon
username, role membership, application name, host name, login property, connection
property, or even time.
Implementing Resource Governor
To configure Resource Governor on an instance, you must create and configure one or
more resource pools, each with one or more workload groups. In addition, you must also
create a classifier function. Finally, you need to enable Resource Governor, which results
in all subsequent sessions being classified. These topics are discussed in the following
sections.
Creating Resource Pools
It is possible to create a maximum of 64 resource pools per instance. Let’s create a
resource pool through SQL Server Management Studio, drill through Management ➤
Resource Governor in Object Explorer, and then select New Resource Pool from the
Resource Pools context menu. This causes the Resource Governor Properties dialog box
to be invoked.
In the Resource Pools section of this dialog box, create a new row in the grid and
populate it with the information we need to create our new resource pool. In our case,
you should add the details for a resource pool named SalesApplication, which has a
Minimum CPU % of 25, a Maximum CPU % of 75, a Minimum Memory % of 25, and a
Maximum Memory % of 40.
Tip  Highlighting a resource pool causes the workload groups associated with
that resource pool to display in the Workload Groups For Resource Pool section of
the screen. Here, you can add, amend, or remove resource pools at the same time.
However, you can also access this dialog box by drilling through Management ➤
Resource Governor ➤ [Resource Pool name] and then selecting New Workload
Group from the Workload Groups context menu.

In this scenario, the maximum memory limit is a hard limit. This means that no more
than 40% of the memory available to this instance is ever allocated to this resource pool.
Also, even if no sessions are using this resource pool, 25% of the memory available to the
instance is still allocated to this resource pool and is unavailable to other resource pools.
In contrast, the maximum CPU limit is soft, or opportunistic. This means that if
more CPU is available, the resource pool utilizes it. The cap only kicks in when there is
contention on the processor.
Tip  It is possible to configure a hard cap on CPU usage. This is helpful in PaaS
(Platform as a Service) or DaaS (Database as a Service) environments where clients
are charged based on CPU usage, and you need to ensure consistent billing for their
applications. A client can easily dispute a bill if they have agreed to pay for 40%
of a core, but the soft cap allows them to reach 50%, resulting in a higher charge
automatically being applied. Implementing this is discussed later in this section.
You can also create resource pools via T-SQL. When you do so, you have access
to more functionality than you do through the GUI, which allows you to configure
minimum and maximum IOPS (input/output per second), set hard caps on CPU usage,
and affinitize a resource pool with specific CPUs or NUMA nodes. Creating an affinity
between a resource pool and a subset of CPUs means that the resource pool will only use
the CPUs, to which it is aligned. You can use the CREATE RESOURCE POOL DDL statement
to create a resource pool in T-SQL. The settings you can configure on a resource pool are
detailed in Table 24-3.
Table 24-3.  CREATE RESOURCE POOL Arguments
Argument
Description
pool_name
The name that you assign to the resource pool.
MIN_CPU_
PERCENT
Specifies the guaranteed average minimum CPU resource available to the
resource pool as a percentage of the CPU bandwidth available to the instance.
MAX_CPU_
PERCENT
Specifies the average maximum CPU resource available to the resource pool as
a percentage of the CPU bandwidth available to the instance. This is a soft limit
that applies when there is contention for the CPU resource.
(continued)

When we are working with minimum- and maximum-IOPS-per-volume thresholds,
we need to take a few things into account. First, if we do not set a maximum IOPS limit,
SQL Server does not govern the IOPS for the resource pool at all. This means that if
you configure minimum IOPS limits for other resource pools, they are not respected.
Therefore, if you want Resource Governor to govern IO, always set a maximum IOPS
threshold for every resource pool.
It is also worth noting that the majority of IO that you can control through Resource
Governor is read operations. This is because write operations, such as Lazy Writer and
Log Flush operations, occur as system operations and fall inside the scope of the internal
Argument
Description
CAP_CPU_
PERCENT
Specifies a hard limit on the amount of CPU resource available to the resource
pool as a percentage of the CPU bandwidth available to the instance.
MIN_MEMORY_
PERCENT
Specifies the minimum amount of memory that is reserved for the resource pool
as a percentage of the memory available to the instance.
MAX_MEMORY_
PERCENT
Specifies the maximum amount of memory that the resource pool can use as a
percentage of the memory available to the instance.
MIN_IOPS_PER_
VOLUME
Specifies the number of IOPS per volume that is reserved for the resource pool.
Unlike CPU and memory thresholds, IOPS are expressed as an absolute value, as
opposed to a percentage.
MAX_IOPS_PER_
VOLUME
Specifies the maximum number of IOPS per volume that the resource pool can
use. Like the minimum IOPS threshold, this is expressed as an absolute number,
as opposed to a percentage.
AFFINITY
SCHEDULER∗
Specifies that the resource pool should be bound to specific SQLOS (SQL
operating system) schedulers, which in turn map to specific virtual cores within
the server. Cannot be used with AFFINITY NUMANODE.
Specify AUTO to allow SQL Server to manage the schedulers that are used by the
resource pool.
Specify the range of scheduler IDs. For example (0, 1, 32 TO 64).
AFFINITY
NUMANODE∗
Specifies that the resource pool should be bound to a specific range of NUMA
nodes. For example (1 TO 4). Cannot be used with AFFINITY SCHEDULER.
∗For further details of CPU and NUMA affinity, refer to Chapter 5.
Table 24-3.  (continued)

resource pool. Because you cannot alter the internal resource pool, you cannot govern
the majority of write operations. This means that using Resource Governor to limit
IO operations is most appropriate when you have a reporting application or another
application with a high ratio of reads to writes.
Finally, you should be aware that Resource Governor can only control the number
of IOPS; it cannot control the size of the IOPS. This means that you cannot use Resource
Governor to control the amount of bandwidth into a SAN an application is using.
To create an external resource pool, the CREATE EXTERNAL RESOURCE POOL DDL
statement should be used. The settings that can be configured on an external resource
pool are detailed in Table 24-4.
If you want to create a resource pool called ReportingApp that sets a minimum CPU
percentage of 50, a maximum CPU percentage of 80, a minimum IOPS reservation of
20, and a maximum IOPS reservation of 100, you can use the script in Listing 24-1.
The final statement of the script uses ALTER RESOURCE GOVERNOR to apply the new
configuration. You should also run this statement after you create workload groups or
apply a classifier function.
Table 24-4.  CREATE EXTERNAL RESOURCE POOL Arguments
Argument
Description
pool_name
The name that you assign to the resource pool.
MAX_CPU_
PERCENT
Specifies the average maximum CPU resource available to the resource pool as a
percentage of the CPU bandwidth available to the instance. This is a soft limit that
applies when there is contention for the CPU resource.
AFFINITY
SCHEDULER
Specifies that the resource pool should be bound to specific SQLOS (SQL operating
system) schedulers, which in turn map to specific virtual cores within the server.
Cannot be used with AFFINITY NUMANODE.
Specify AUTO to allow SQL Server to manage the schedulers that are used by the
resource pool.
Specify the range of scheduler IDs. For example (0, 1, 32 TO 64).
MAX_MEMORY_
PERCENT
Specifies the maximum amount of memory that the resource pool can use as a
percentage of the memory available to the instance.
MAX_
PROCESSES
Specifies the maximum number of processes allowed within the pool at any given
time. The default is 0, which limits the number of processes by server resources only.

Listing 24-1.  Creating a Resource Pool
CREATE RESOURCE POOL ReportingApp
    WITH(
        MIN_CPU_PERCENT=50,
        MAX_CPU_PERCENT=80,
        MIN_IOPS_PER_VOLUME = 20,
        MAX_IOPS_PER_VOLUME = 100
        ) ;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE ;
GO
Creating Workload Groups
Each resource pool can contain multiple workload groups. To begin creating a workload
group for our SalesApplication resource pool, we drill through Management ➤
Resource Governor ➤ Resource Pools. We then drill through our SalesApplication
resource pool and select New Workload Group from the Workload Groups context
menu. This invokes the Resource Governor Properties dialog box, which is displayed in
Figure 24-1.
Figure 24-1.  The Resource Governor Properties dialog box

You can see that with the SalesApplication resource pool highlighted in the
Resource Pools section of the dialog box, we have created two rows within the Workload
Groups section of the screen. Each of these rows represents a workload group that is
associated with the SalesApplication resource pool.
We have configured the SalesUsers workload group to allow a maximum of 100
simultaneous requests and a MAXDOP of 4, meaning that requests classified under this
workload group are able to use a maximum of four schedulers.
We have configured the Managers workload group to allow a maximum of ten
simultaneous requests and use a maximum of one scheduler. We have also configured
this workload group to be able to use a maximum of 10% of the memory that the
resource pool can reserve, as opposed to the default of 25%.
If the Memory Grant % setting is set to 0, then any requests classified under that
workload group are blocked from running any operations that require a SORT or HASH
JOIN physical operator. If queries need more than the specified amount of RAM, then SQL
Server reduces the DOP for that query in an attempt to reduce the memory requirement.
If the DOP reaches 1 and there is still not enough memory, then Error 8657 is thrown.
To create a resource pool via T-SQL, use the CREATE WORKLOAD GROUP DDL statement.
This statement accepts the arguments detailed in Table 24-5.
Table 24-5.  CREATE WORKLOAD GROUP Arguments
Argument
Description
group_name
Specifies the name of the workload group.
IMPORTANCE
Can be configured to HIGH, MEDIUM, or LOW and allows you to prioritize
requests in one workload group above another.
REQUEST_MAX_
MEMORY_GRANT_
PERCENT
Specifies the maximum amount of memory that any one query can use
from the resource pool expressed as a percentage of the memory available
to the resource pool.
REQUEST_MAX_CPU_
TIME_SEC
Specifies the amount of CPU time, in seconds, that any one query can use.
It is important to note that if the threshold is exceeded, then an event is
generated that can be captured with Extended Events. The query is not
cancelled, however.
(continued)

Caution  Workload group names must be unique, even if they are associated with
different pools. This is so they can be returned by the classifier function.
If we create two workload groups we want associated with our ReportingApp
resource pool—one named InternalReports with a MAXDOP of 4 and a 25% maximum
memory grant and the other named ExternalReports with a MAXDOP of 8 and a
maximum memory grant percentage of 75%—we could use the script in Listing 24-2.
Listing 24-2.  Creating Workload Groups
CREATE WORKLOAD GROUP InternalReports
    WITH(
        GROUP_MAX_REQUESTS=100,
        IMPORTANCE=Medium,
        REQUEST_MAX_CPU_TIME_SEC=0,
Argument
Description
REQUEST_MEMORY_
GRANT_TIMEOUT_SEC
Specifies the maximum amount of time that a query can wait for a work
buffer memory to become available before it times out. The query only
times out under memory contention, however. Otherwise, the query
receives the minimum memory grant. This results in performance
degradation for the query. The maximum wait time is expressed in seconds.
MAX_DOP
The maximum number of processors that a single parallel query can use.
The MAXDOP for a query can be further restrained by using query hints,
by changing the MAXDOP setting for the instance, or when the relational
engine chooses a serial plan.
GROUP_MAX_
REQUESTS
Specifies the maximum number of concurrent requests that can be
executed within the workload group. If the number of concurrent requests
reaches this value, then further queries are placed in a waiting state until
the number of concurrent queries falls below the threshold.
USING
Specifies the resource pool with which the workload group is associated. If
not specified, then the group is associated with the default pool.
Table 24-5.  (continued)

REQUEST_MAX_MEMORY_GRANT_PERCENT=25,
        REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
        MAX_DOP=4
                ) USING ReportingApp ;
GO
CREATE WORKLOAD GROUP ExternalReports
    WITH(
        GROUP_MAX_REQUESTS=100,
        IMPORTANCE=Medium,
        REQUEST_MAX_CPU_TIME_SEC=0,
        REQUEST_MAX_MEMORY_GRANT_PERCENT=75,
        REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
        MAX_DOP=8
        ) USING ReportingApp ;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Creating a Classifier Function
A classifier function is a scalar UDF (user-defined function) that resides in the Master
database. It returns a value of type SYSNAME, which is a system-defined type equivalent
to NVARCHAR(128). The value returned by the function corresponds to the name of
the workload group into which each request should fall. The logic within the function
determines which workload group name is returned. You only ever have one classifier
function per instance, so you need to modify the function if you add additional
workload groups.
Now let’s create a classifier function using the Resource Governor environment that
we have built in this chapter. This function will classify each request made against our
instance using the following rules:
	 1.	 If the request is made under the context of the SalesUser login,
then the request should fall under the SalesUsers workload
group.

2.	 If the request is made by the SalesManager login, then requests
should be placed in the Managers workload group.
	 3.	 If the request is made by the ReportsUser login and the request
was made from a server named ReportsApp, then the request
should fall into the InternalReports workload group.
	 4.	 If the request is made by the ReportsUser login but did not
originate from the ReportsApp server, then it should fall into the
ExternalReports workload group.
	 5.	 All other requests should be placed into the default workload
group.
Before creating our classifier function, we prepare the instance. To do this, we
first create the Chapter24 database. We then create the SalesUser, ReportsUser, and
SalesManager logins, with Users mapped to the Chapter24 database. (Further detail on
security principles can be found in Chapter 10.) Listing 24-3 contains the code we need
to prepare the instance.
Note  The users are mapped to the Chapter24 database for the purpose of this
example, but you can make the queries against any database in the instance.
Listing 24-3.  Preparing the Instance
--Create the database
USE [master]
GO
CREATE DATABASE Chapter24 ;
--Create the Logins and Users
CREATE LOGIN SalesUser
    WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=Chapter24,
        CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ;
GO

CREATE LOGIN ReportsUser
    WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=Chapter24,
        CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ;
GO
CREATE LOGIN SalesManager
    WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=Chapter24,
        CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ;
GO
USE Chapter24
GO
CREATE USER SalesUser FOR LOGIN SalesUser ;
GO
CREATE USER ReportsUser FOR LOGIN ReportsUser ;
GO
CREATE USER SalesManager FOR LOGIN SalesManager ;
GO
In order to implement the business rules pertaining to which workload group each
request should be placed into, we use the system functions detailed in Table 24-6.
When we create a classifier function, it must follow specific rules. First, the function
must be schema-bound. This means that any underlying objects that are referenced by
the function cannot be altered without the function first being dropped. The function
must also return the SYSNAME data type and have no parameters.
It is worth noting that the requirement for the function to be schema-bound is
significant, and it poses limitations on the flexibility of Resource Governor. For example,
it would be very useful if you were able to delegate workloads based upon database role
Table 24-6.  System Functions for Implementing Business Rules
Function
Description
Business Rule(s)
SUSER_SNAME() Returns the name of a login
1, 2, 3, 4
HOST_NAME()
Returns the name of the host from which the request was issued 3, 4

membership; however, this is not possible, because schema-bound functions cannot
access objects in other databases, either directly or indirectly. Because the classifier
function must reside in the Master database, you cannot access information regarding
database roles in other databases.
As with all things, there are workarounds for this issue. For example, you can create
a table in the Master database that maintains role membership from user databases.
You can even keep this table updated automatically by using a combination of views
and triggers in the user database. The view would be based on the sys.sysusers catalog
view, and the trigger would be based on the view that you created. This would be a
complex design, however, which would pose operational challenges to maintain.
The script within Listing 24-4 creates the classifier function, which implements
our business rules before associating the function with Resource Governor. As always,
Resource Governor is then reconfigured so that our changes take effect.
Listing 24-4.  Creating the Classifier Function
USE Master
GO
CREATE FUNCTION dbo.Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
        --Declare variables
        DECLARE @WorkloadGroup        SYSNAME ;
        SET @WorkloadGroup = 'Not Assigned' ;
        --Implement business rule 1
        IF (SUSER_NAME() = 'SalesUser')
        BEGIN
                SET @WorkloadGroup = 'SalesUsers' ;
        END
        --Implement business rule 2
        ELSE IF (SUSER_NAME() = 'SalesManager')

BEGIN
                SET @WorkloadGroup = 'Managers' ;
        END
                --Implement business rules 3 & 4
        ELSE IF (SUSER_SNAME() = 'ReportsUser')
        BEGIN
                IF (HOST_NAME() = 'ReportsApp')
                BEGIN
                        SET @WorkloadGroup = 'InternalReports'
                END
                ELSE
                BEGIN
                        SET @WorkloadGroup = 'ExternalReports'
                END
        END
        --Implement business rule 5 (Put all other requests into the
default workload group)
        ELSE IF @WorkloadGroup = 'Not Assigned'
        BEGIN
                SET @WorkloadGroup = 'default'
        END
        --Return the apropriate Workload Group name
        RETURN @WorkloadGroup
END
GO
--Associate the Classifier Function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Classifier) ;
ALTER RESOURCE GOVERNOR RECONFIGURE ;

Testing the Classifier Function
After we create the classifier function, we want to test that it works. We can test business
rules 1 and 2 by using the EXECUTE AS statement to change our system context and then
call the classifier function. This is demonstrated in Listing 24-5. The script temporarily
allows all logins to access the classifier function directly, which allows the queries to
work. It implements this by granting the Public role the EXECUTE permission before
revoking this permission at the end of the script.
Listing 24-5.  Testing Business Rules 1 and 2
USE MASTER
GO
GRANT EXECUTE ON dbo.Classifier TO public ;
GO
EXECUTE AS LOGIN = 'SalesUser' ;
SELECT dbo.Classifier() AS 'Workload Group' ;
REVERT
EXECUTE AS LOGIN = 'SalesManager' ;
SELECT dbo.Classifier() as 'Workload Group' ;
REVERT
REVOKE EXECUTE ON dbo.Classifier TO public ;
GO
The result of running these two queries shows that business rules 1 and 2 are
working as expected.
To test business rule 4, we can use the same process we used to validate business
rules 1 and 2. The only difference is that we change the execution context to
ReportsUser. In order to validate rule 3, we use the same process, but this time, we
invoke the query from a server named ReportsApp.
Tip  If you do not have access to a server named ReportsApp, then update the
function definition to use a server name that you do have access to.