SQL Server provides three dynamic management views you can use to view and monitor your Resource Governor configuration:
sys.dm_resource_governor_workload_groups— Returns workload group statistics along with the current in-memory configuration of the workload groups.
sys.dm_resource_governor_resource_pools— Returns information about current state of your resource pools and resource pool statistics.
sys.dm_resource_governor_configuration— Returns the in-memory configuration state of the Resource Governor. Output is the same as the sys.resource_governor_configuration system catalog view.
For example, the following query against the sys.dm_resource_governor_resource_pools DMV returns the configuration settings for each of the pools along with the actual memory allocated:
select name,
min_cpu_percent as MinCPU,
max_cpu_percent as MaxCPU,
min_memory_percent as 'MinMEM%' ,
max_memory_percent as 'MaxMEM%',
max_memory_kb as 'MaxMemKB',
used_memory_kb as 'UsedMemKB',
target_memory_kb as 'TgtMemKB'
from sys.dm_resource_governor_resource_pools
GO
name MinCPU MaxCPU MinMEM% MaxMEM% MaxMemKB UsedMemKB TgtMemKB
---------- ------ ------ ------- ------- -------- --------- --------
internal 0 100 0 100 1556232 8296 1556232
default 0 100 0 100 389064 8336 389064
ReportPool 0 20 0 30 389064 280 389064
OLTPPool 80 100 75 100 1556232 40 1556232
The following example displays statistics on the requests received within the defined workgroups:
select
cast(g.name as nvarchar(10)) as wg_name,
cast(p.name as nvarchar(10)) as pool_name,
total_request_count as totreqcnt,
active_request_count as actreqcnt,
g.total_cpu_usage_ms as tot_cpu_use,
total_cpu_limit_violation_count as tot_clvc,
g.request_max_cpu_time_sec as req_mcts,
g.total_reduced_memgrant_count as tot_rmc
from sys.dm_resource_governor_workload_groups g
inner join
sys.dm_resource_governor_resource_pools p
on p.pool_id = g.pool_id
go
wg_name pool_name totreqcnt actreqcnt tot_cpu_use tot_clvc req_mcts tot_rmc
--------- ---------- --------- --------- ----------- -------- -------- -------
internal internal 0 0 37314 0 0 0
default default 784 2 97938 0 0 0
ReportWG1 ReportPool 170 1 476016 0 0 0
OLTPWG1 OLTPPool 161 0 1834 0 0 0
Six other DMVs in SQL Server 2008 contain information related to Resource Governor:
sys.dm_exec_query_memory_grants—
Returns information about the queries that have acquired a memory grant
or that still require a memory grant to execute. Resource
Governor–related columns in this table are the group_id, pool_id, is_small, and ideal_memory_kb columns.
sys.dm_exec_query_resource_semaphores— Returns information about the current query_resource semaphore status, providing general query-execution memory status information. The pool_id column provides a link to Resource Governor information.
sys.dm_exec_session— Returns one row per session on SQL Server. The group_id column relates the information to Resource Governor workload groups.
sys.dm_exec_requests— Returns information about each request currently executing within SQL Server. The group_id column relates the information to Resource Governor workload groups.
sys.dm_exec_cached_plans— Returns a row for each query plan cached by SQL Server in the plan cache. The pool_id column relates the information to Resource Governor resource pools.
sys.dm_os_memory_brokers—
Returns information about internal allocations within SQL Server that
use the Memory Manager. This information includes the following columns
for the Resource Governor: pool_id, allocations_db_per_sec, predicted_allocations_kb, and overall_limit_kb.
The following query joins between sys.dm_exec_session and sys.dm_resource_governor_workload_groups to display which sessions are in which workload group:
SELECT
CAST(g.name as nvarchar(10)) as poolname,
s.session_id as 'session',
s.login_time,
CAST(s.host_name as nvarchar(15)) as host_name,
CAST(s.program_name AS nvarchar(20)) as program_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
where g.name in ('default', 'ReportWG1', 'OLTPWG1')
go
poolname session login_time host_name program_name
---------- ------- ----------------------- --------------- --------------------
default 51 2010-05-02 14:31:18.530 LATITUDED830-W7 Microsoft SQL Server
default 52 2010-05-02 14:31:21.990 LATITUDED830-W7 SQLAgent - Generic R
default 53 2010-05-02 14:31:23.533 LATITUDED830-W7 SQLAgent - TSQL JobS
default 55 2010-05-02 14:47:27.250 LATITUDED830-W7 Microsoft SQL Server
ReportWG1 60 2010-05-02 19:06:21.100 LATITUDED830-W7 Microsoft SQL Server
OLTPWG1 54 2010-05-02 21:03:03.020 LATITUDED830-W7 Microsoft SQL Server
You can also monitor CPU
and memory resources allocated by the Resource Governor through the
Windows Performance Monitor via a couple of new performance counters:
An instance of the SQLServer: Resource Pool Stats counter is available for each of the configured resource pools. Likewise, an instance of the SQLServer: Workload Stats counter is available for each of the configured workload groups (see Figure 1). These performance counters return the same information as that returned by the sys.dm_resource_governor_workload_groups and sys.dm_resource_governor_resource_pools DMVs but enable you to monitor these statistics over time.