Bagikan melalui

  • Artikel

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.

Performance Counters

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

Description

SQLServer:Workload Group Stats

Reports statistics for each active workload group, such as the number of active requests and the number of blocked requests.

SQLServer:Resource Pool Stats

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.

Events

In addition to using SQL Trace events, Resource Governor introduces new events for monitoring. The following table describes the new events.

Event name

Description

CPU Threshold Exceeded

Indicates when Resource Governor detects that a query has exceeded the CPU threshold value in REQUEST_MAX_CPU_TIME_SEC.

PreConnect:Starting

Indicates when a LOGON trigger or Resource Governor classifier function starts execution.

PreConnect:Completed

Indicates when a LOGON trigger or Resource Governor classifier function finishes execution.

Dynamic Management Views

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.

Name

Description

sys.dm_resource_governor_workload_groups

Returns workload group statistics and the current in-memory configuration of the workload group.

sys.dm_resource_governor_resource_pools

Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.

sys.dm_resource_governor_configuration

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.