sys.dm_resource_governor_resource

sys.dm_resource_governor_resource_p...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

SQL Server 2008 Books Online (June 2009)

sys.dm_resource_governor_resource_pools (Transact-SQL)

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

Column name	Data type	Description
pool_id	int	The ID of the resource pool. Is not nullable.
name	sysname	The name of the resource pool. Is not nullable.
statistics_start_time	datetime	The time when statistics was reset for this pool. Is not nullable.
total_cpu_usage_ms	bigint	The cumulative CPU usage in milliseconds. Is not nullable.
cache_memory_kb	bigint	The current total cache memory usage in kilobytes. Is not nullable.
compile_memory_kb	bigint	The current total stolen memory usage in kilobytes (KB). The majority of this usage would be for compile and optimization, but it can also include other memory users. Is not nullable.
used_memgrant_kb	bigint	The current total used (stolen) memory from memory grants. Is not nullable.
total_memgrant_count	bigint	The cumulative count of memory grants in this resource pool. Is not nullable.
total_memgrant_timeout_count	bigint	The cumulative count of memory grant time-outs in this resource pool. Is not nullable.
active_memgrant_count	bigint	The current count of memory grants. Is not nullable.
active_memgrant_kb	bigint	The sum, in kilobytes (KB), of current memory grants. Is not nullable.
memgrant_waiter_count	int	The count of queries currently pending on memory grants. Is not nullable.
max_memory_kb	bigint	The maximum amount of memory, in kilobytes, that the resource pool can have. This is based on the current settings and server state. Is not nullable.
used_memory_kb	bigint	The amount of memory used, in kilobytes, for the resource pool. Is not nullable.
target_memory_kb	bigint	The target amount of memory, in kilobytes, the resource pool is trying to attain. This is based on the current settings and server state. Is not nullable.
out_of_memory_count	bigint	The number of failed memory allocations in the pool. Is not nullable.
min_cpu_percent	int	The current configuration for the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. Is not nullable.
max_cpu_percent	int	The current configuration for the maximum average CPU bandwidth allowed for all requests in the resource pool when there is CPU contention. Is not nullable.
min_memory_percent	int	The current configuration for the guaranteed amount of memory for all requests in the resource pool when there is memory contention. This is not shared with other resource pools. Is not nullable.
max_memory_percent	int	The current configuration for the percentage of total server memory that can be used by requests in this resource pool. Is not nullable.

Remarks

Resource Governor workload groups and Resource Governor resource pools have a many-to-one mapping. As a result, many of the resource pool statistics are derived from the workload group statistics.

This dynamic management view shows the in-memory configuration.To see the stored configuration metadata, use the sys.resource_governor_resource_pools catalog view.

When ALTER RESOURCE GOVERNOR RESET STATISTICS is successfully executed, the following counters are reset: statistics_start_time, total_cpu_usage_ms, and out_of_memory_count. statistics_start_time is set to the current system date and time, the other counters are set to zero (0).

Permissions

Requires VIEW SERVER STATE permission.

Reference

Dynamic Management Views and Functions (Transact-SQL)
sys.dm_resource_governor_workload_groups (Transact-SQL)
sys.resource_governor_resource_pools (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance