Resource Governor Monitoring
Obtaining execution statistics for each workload group and resource pool is a critical part of the Resource Governor monitoring and management strategy. Execution statistics enable you to identify workload characteristics that can aid in configuration and detect potential problems early enough to take corrective action and ensure ongoing reliable and predictable operation.
In Resource Governor the majority of statistics collection is based on performance counters for the following reasons:
Performance counters can be monitored without explicit Transact-SQL-style polling.
The total number of expected active workload groups and resource pools is small (in the order of tens) so there is no significant memory pressure from counters.
The existing sys.dm_os_performance_counters dynamic management view can be used to pull counter values. Because it shows raw values, a DBA can use them to calculate average values.
Existing tools such as Perfmon can be used without requiring modifications.
Resource Governor uses the following performance objects for collecting workload group and resource pool statistics:
Performance counter name
Reports statistics for each active workload group, such as the number of active requests and the number of blocked requests.
Reports statistics for each active resource pool, such as the number of memory grants that are occurring in the resource pool per second and the amount of memory that is used by the resource pool.
In addition to using SQL Trace events, Resource Governor introduces new events for monitoring. The following table describes the new events.
Indicates when Resource Governor detects that a query has exceeded the CPU threshold value in REQUEST_MAX_CPU_TIME_SEC.
Indicates when a LOGON trigger or Resource Governor classifier function starts execution.
Indicates when a LOGON trigger or Resource Governor classifier function finishes execution.
Resource Governor introduces new dynamic management views that you can use to return current statistics and configuration data for workload groups and resource pools. The following table describes the new dynamic management views.
Returns workload group statistics and the current in-memory configuration of the workload group.
Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.
Returns a row that contains the current in-memory configuration state for Resource Governor.
Stored configuration data for Resource Governor is available in catalog views such as sys.resource_governor_resource_pools.