Monitoring Resource Governor
SQL Server exposes dynamic management views (DMVs) that you can use to return
statistics relating to resource pools and workload groups. You can also monitor Resource
Governor’s usage using Windows’ Performance Monitor tool, however, and this gives
you the advantage of a graphical representation. The following sections discuss both of
these approaches to monitoring Resource Governor.
Monitoring with Performance Monitor
DBAs can monitor how resource pools and their associated workload groups are being
utilized by using Performance Monitor, which is built into Windows. You can access
Performance Monitor from Control Panel ➤ Administrative Tools or by searching for
Perfmon in the Start menu.
Note  To follow the demonstrations in this section, you should be running a
Windows Server operating system.
Two categories are available to Performance Monitor that relate to Resource
Governor. The first is MSSQL$[INSTANCE NAME]:Resource Pool Stats. This contains
counters that relate to the consumption of resources, which have been made available
to resource groups. An instance of each counter is available for each resource group that
has been configured on the instance.
The second category is MSSQL$[INSTANCE NAME]:Workload Group Stats, which
contains counters that relate to the utilization of each workload group that has been
configured on the instance. Figure 24-2 illustrates how we can add the InternalReports,
ExternalReports, SalesUsers, and Managers instances of the CPU Usage % counter
from within the Workload Group Stats category. After highlighting the instances, we will
use the Add button, to move them to the Added Counters section. We can invoke the
Add Counters dialog box by selecting Monitoring Tools ➤ Performance Monitor from
the left pane and then using the Plus (+) symbol on the toolbar in the right hand pane.

Now that we have added this counter, we also need to add the ReportingApp and
SalesApplication app instances of the Active Memory Grant Amount (KB) counter
from within the Resource Pool Stats category, as illustrated in Figure 24-3.
Figure 24-2.  Adding Workload Group Stats

To test our Resource Governor configuration, we can use the script in Listing 24-­6.
This script is designed to run in two separate query windows. The first part of the
script should run in a query window that is connected to your instance using the
SalesUser login, and the second part of the script should run in a query window that is
connected to your instance by using the SalesManager login. The two scripts should run
simultaneously and cause Performance Monitor to generate a graph similar to the one in
Figure 24-4. Although the scripts do not cause the classifier function to be called, they act
as an interactive way of testing our logic.
Note  The following scripts are likely to return a lot of data.
Figure 24-3.  Resource Pool Stats

Listing 24-6.  Generating Load Against the SalesUsers and Managers Workload
Groups
--Script Part 1 - To be run in a query windows that is connected using the
SalesManager Login
EXECUTE AS LOGIN = 'SalesManager'
DECLARE @i INT = 0 ;
WHILE (@i < 10000)
BEGIN
SELECT DBName = (
        SELECT Name AS [data()]
        FROM sys.databases
        FOR XML PATH('')
) ;
SET @i = @i + 1 ;
END
--Script Part 2 - To be run in a query windows that is connected using the
SalesUser Login
EXECUTE AS LOGIN = 'SalesUser'
DECLARE @i INT = 0 ;
WHILE (@i < 10000)
BEGIN
SELECT DBName = (
        SELECT Name AS [data()]
        FROM sys.databases
        FOR XML PATH('')
) ;
SET @i = @i + 1 ;
END

You can see that the CPU usage for the SalesUsers and Managers workload groups is
almost identical, which means that the Resource Governor implementation is working
as expected.
Monitoring with DMVs
SQL Server provides the sys.dm_resource_governor_resource_pools and sys.dm_
resource_governor_workload_groups DMVs that DBAs can use to examine Resource
Governor statistics. The sys.dm_resource_governor_resource_pools DMV returns the
columns detailed in Table 24-7.
Figure 24-4.  Viewing CPU utilization
Table 24-7.  Columns Returned by sys.dm_resource_governor_resource_pools
Column
Description
pool_id
The unique ID of the resource pool
name
The name of the resource pool
statistics_start_time
The date/time of the last time the resource pool’s statistics were reset
(continued)

Table 24-7.  (continued)
Column
Description
total_cpu_usage_ms
The total CPU time used by the resource pool since the statistics last
reset
cache_memory_kb
The total cache memory currently being used by the resource pool
compile_memory_kb
The total memory the resource pool is currently using for compilation
and optimization
used_memgrant_kb
The total memory the resource pool is using for memory grants
total_memgrant_count
A count of memory grants in the resource pool since the statistics
were reset
total_memgrant_
timeout_count
A count of memory grant timeouts in the resource pool since the
statistics were last reset
active_memgrant_count
A count of current memory grants within the resource pool
active_memgrant_kb
The total amount of memory currently being used for memory grants
in the resource pool
memgrant_waiter_count
A count of queries currently pending, waiting for memory grants
within the resource pool
max_memory_kb
The maximum amount of memory the resource pool can reserve
used_memory_kb
The amount of memory the resource pool currently has reserved
target_memory_kb
The amount of memory that the resource pool is currently trying to
maintain
out_of_memory_count
A count of failed memory allocations for the resource pool
min_cpu_percent
The guaranteed average minimum CPU % for the resource pool
max_cpu_percent
The average maximum CPU % for the resource pool
min_memory_percent
The guaranteed minimum amount of memory that is available to the
resource pool during periods of memory contention
max_memory_percent
The maximum percentage of server memory that can be allocated to
the resource pool
cap_cpu_percent
The hard limit on the maximum CPU % available to the resource pool

The sys.dm_resource_governor_workload_groups DMV returns the columns
detailed in Table 24-8.
Table 24-8.  Columns Returned by sys.dm_resource_governor_workload_groups
Column
Description
group_id
The unique ID of the workload group.
name
The name of the workload group.
pool_id
The unique ID of the resource pool with which the workload group is
associated.
statistics_start_
time
The date/time of the last time the workload group’s statistics were reset.
total_request_count A count of the number of requests in the workload group since the
statistics were last reset.
total_queued_
request_count
The number of requests within the workload group that have been
queued as a result of the GROUP_MAX_REQUESTS threshold being
reached since the statistics were last reset.
active_request_count A count of requests that are currently active within the workload group.
queued_request_count The number of requests within the workload group that are currently
queued as a result of the GROUP_MAX_REQUESTS threshold being reached.
total_cpu_limit_
violation_count
A count of requests in the workload group that have exceeded the CPU
limit since the statistics were last reset.
total_cpu_usage_ms
The total CPU time used by requests within the workload group since the
statistics were last reset.
max_request_cpu_
time_ms
The maximum CPU time used by any request within the workload group
since the last time the statistics were reset.
blocked_task_count
A count of tasks within the workload group that are currently blocked.
total_lock_wait_
count
A count of all lock waits that have occurred for requests within the
workload group since the last time the statistics were reset.
total_lock_wait_
time_ms
A sum of time that locks have been held by requests within the workload
group since statistics were last reset.
(continued)

You can join the sys.dm_resource_governor_resource_pools and sys.dm_
resource_governor_workload_groups DMVs, using the pool_id column in each view.
The script in Listing 24-7 demonstrates how you can achieve this so you can return a
report of CPU usage across the workload groups as compared to the overall CPU usage of
the resource pool.
Table 24-8.  (continued)
Column
Description
total_query_
optimization_count
A count of all query optimizations that have occurred within the workload
group since the statistics were reset.
total_suboptimal_
plan_generation_
count
A count of all suboptimal plans that have been generated within the
workload group, since the last time the statistics were reset. These
suboptimal plans indicate that the workload group was experiencing
memory pressure.
total_reduced_
memgrant_count
A count of all memory grants that have reached the maximum size limit
within the workload group since the last time the statistics were reset.
max_request_grant_
memory_kb
The size of the largest single memory grant that has occurred within the
workload group since the last time the statistics were reset.
active_parallel_
thread_count
A count of how many parallel threads are currently in use within the
workload group.
importance
The current value specified for the workload group’s importance setting.
request_max_memory_
grant_percent
The current value specified for the workload group’s maximum memory
grant percentage.
request_max_cpu_
time_sec
The current value specified for the workload group’s CPU limit.
request_memory_
grant_timeout_sec
The current value specified for the workload group’s memory grant timeout.
group_max_requests
The current value specified for the workload group’s maximum
concurrent requests.
max_dop
The current value specified for the workload group’s MAXDOP.

Listing 24-7.  Reporting on CPU Usage
SELECT
        rp.name ResourcePoolName
        ,wg.name WorkgroupName
        ,rp.total_cpu_usage_ms ResourcePoolCPUUsage
        ,wg.total_cpu_usage_ms WorkloadGroupCPUUsage
        ,CAST(ROUND(CASE
                WHEN rp.total_cpu_usage_ms = 0
                        THEN 100
                ELSE (wg.total_cpu_usage_ms * 1.)
/ (rp.total_cpu_usage_ms * 1.) * 100 Percentage
                END, 3) AS FLOAT) WorkloadGroupPercentageOfResourcePool
FROM sys.dm_resource_governor_resource_pools rp
INNER JOIN sys.dm_resource_governor_workload_groups wg
        ON rp.pool_id = wg.pool_id
ORDER BY rp.pool_id ;
You can reset the cumulative statistics exposed by the sys.resource_governor_
resource_pools and sys.dm_resource_governor_workload_groups DMVs using the
command in Listing 24-8.
Listing 24-8.  Resetting Resource Governor Statistics
ALTER RESOURCE GOVERNOR RESET STATISTICS ;
SQL Server exposes a third DMV named sys.dm_resource_governor_resource_
pool_affinity, which returns the columns detailed in Table 24-9.
Table 24-9.  Columns Returned by dm_resource_ governor_resource_pool_affinity
Column
Description
pool_id
The unique ID of the resource pool.
processor_group The ID of the logical processor group.
scheduler_mask
The binary mask, which represents the schedulers that are affinitized with the
resource pool. For further details on interpreting this binary mask, please refer
to Chapter 5.

You can join the sys.dm_resource_governor_resource_pool_affinity DMV to the
sys.resource_governor_resource_pools DMV using the pool_id column in each view.
Listing 24-9 demonstrates this; it first alters the default resource pool so that it only uses
processor 0 before it displays the scheduler binary mask for each resource pool that has
processor affinity configured.
Listing 24-9.  Scheduling a Binary Mask for Each Resource Pool
ALTER RESOURCE POOL [Default] WITH(AFFINITY SCHEDULER = (0)) ;
ALTER RESOURCE GOVERNOR RECONFIGURE ;
SELECT
        rp.name ResourcePoolName
        ,pa.scheduler_mask
FROM sys.dm_resource_governor_resource_pool_affinity pa
INNER JOIN sys.dm_resource_governor_resource_pools rp
        ON pa.pool_id = rp.pool_id ;
There is a DMV called sys.dm_resource_governor_resource_pool_volumes which
returns details of the IO statistics for each resource pool. This DMV’s columns are
described in Table 24-10.
Table 24-10.  Columns Returned by dm_resource_ governor_resource_pool_volumes
Column
Description
pool_id
The unique ID of the resource pool
volume_name
The name of the disk volume
min_iops_per_volume
The current configuration for the minimum number of IOPS per volume
for the resource pool
max_iops_per_volume
The current configuration for the maximum number of IOPS per volume
for the resource pool
read_ios_queued_
total
The total read IOs queued for the resource pool against this volume
since the last time the statistics were reset
(continued)

Table 24-10.  (continued)
Column
Description
read_ios_issued_
total
The total read IOs issued for the resource pool against this volume since
the last time the statistics were reset
read_ios_completed_
total
The total read IOs  completed for the resource pool against this volume
since the last time the statistics were reset
read_bytes_total
The total bytes read for the resource pool against this volume since the
last time the statistics were reset
read_io_stall_
total_ms
The cumulative time between read IO operations being issued and
completed for the resource pool against this volume since the last time
the statistics were reset
read_io_stall_
queued_ms
The cumulative time between read IO operations arriving and being
completed for the resource pool against this volume since the last time
the statistics were reset
write_ios_queued_
total
The total write IOs queued for the resource pool against this volume
since the last time the statistics were reset
write_ios_issued_
total
The total write IOs issued for the resource pool against this volume
since the last time the statistics were reset
write_ios_
completed_total
The total write IOs completed for the resource pool against this volume
since the last time the statistics were reset
write_bytes_total
The total bytes written for the resource pool against this volume since
the last time the statistics were reset
write_io_stall_
total_ms
The cumulative time between write IO operations being issued and
completed for the resource pool against this volume since the last time
the statistics were reset
write_io_stall_
queued_ms
The cumulative time between write IO operations arriving and being
completed for the resource pool against this volume since the last time
the statistics were reset
io_issue_
violations_total
The total number of times that more IO operations were performed against
the resource pool and volume than are allowed by the configuration
io_issue_delay_
total_ms
The total time between when IO operations were scheduled to be issued
and when they were actually issued

You can use the sys.dm_resource_governor_resource_pool_volumes DMV to
determine if your resource pool configuration is causing latency by adding the read_io_
stall_queued_ms and write_io_stall_queued_ms and then subtracting this value from
the total of read_io_stall_total_ms added to write_io_stall_total_ms, as shown in
Listing 24-10. This script first alters the default resource pool so that IOPS are governed
before subsequently reporting on IO stalls.
Tip  Remember that you are likely to see far fewer write operations than read
operations in user-defined resource pools. This is because the vast majority of
write operations are system operations and, therefore, they take place within the
internal resource pool.
Listing 24-10.  Discovering If Resource Pool Configuration Is Causing Disk
Latency
ALTER RESOURCE POOL [default] WITH(
                min_iops_per_volume=50,
                max_iops_per_volume=100) ;
ALTER RESOURCE GOVERNOR RECONFIGURE ;
SELECT
        rp.name ResourcePoolName
        ,pv.volume_name
        ,pv.read_io_stall_total_ms
        ,pv.write_io_stall_total_ms
        ,pv.read_io_stall_queued_ms
        ,pv.write_io_stall_queued_ms
        ,(pv.read_io_stall_total_ms + pv.write_io_stall_total_ms)
            - (pv.read_io_stall_queued_ms + pv.write_io_stall_queued_ms)
GovernorLatency
FROM sys.dm_resource_governor_resource_pool_volumes pv
RIGHT JOIN sys.dm_resource_governor_resource_pools rp
        ON pv.pool_id = rp.pool_id ;

Tip  If you do not see any IO stalls, create a database on a low-performance
drive and run some intensive queries against it before you rerun the query in
Listing 24-­10.
Summary
Resource Governor allows you to throttle applications at the SQL Server instance level.
You can use it to limit a request’s memory, CPU, and disk usage. You can also use it to
affinitize a category of requests with specific scheduler or NUMA ranges or to reduce the
MAXDOP for a category of requests.
A resource pool represents a set of server resources, and a workload group is a
logical container for similar requests that have been classified in the same way. Resource
Governor provides an internal resource pool and workload group for system requests
and a default resource pool and workload group as a catch-all for any requests that have
not been classified. Although the internal resource pool cannot be modified, user-­
defined resource pools have a one-to-many relationship with workload groups.
Requests made to SQL Server are classified using a user-defined function, which the
DBA must create. This function must be a scalar function that returns the sysname data
type. It must also be schema-bound and reside in the Master database. DBAs can use
system functions, such as USER_SNAME(), IS_MEMBER(), and HOST_NAME(), to assist them
with the classification.
SQL Server provides four dynamic management views (DMVs) that DBAs can use
to help monitor Resource Governor configuration and usage. DBAs can also monitor
Resource Governor usage using Performance Monitor, however, and this gives them
the advantage of a visual representation of the data. When taking this approach, you
will find that Performance Monitor exposes counter categories for resource pools and
workload groups for each instance that resides on the server. The counters within these
categories have one instance for each resource pool or workload group, respectively, that
is currently configured on the instance.