sys.dm_resource_governor_resource_pools (Transact-SQL)

sys.dm_resource_governor_resource_pools (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_resource_governor_resource_pools.

Column nameData typeDescription
pool_idintThe ID of the resource pool. Is not nullable.
namesysnameThe name of the resource pool. Is not nullable.
statistics_start_timedatetimeThe time when statistics was reset for this pool. Is not nullable.
total_cpu_usage_msbigintThe cumulative CPU usage in milliseconds since the Resource Govenor statistics were reset. Is not nullable.
cache_memory_kbbigintThe current total cache memory usage in kilobytes. Is not nullable.
compile_memory_kbbigintThe 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_kbbigintThe current total used (stolen) memory from memory grants. Is not nullable.
total_memgrant_countbigintThe cumulative count of memory grants in this resource pool. Is not nullable.
total_memgrant_timeout_countbigintThe cumulative count of memory grant time-outs in this resource pool. Is not nullable.
active_memgrant_countintThe current count of memory grants. Is not nullable.
active_memgrant_kbbigintThe sum, in kilobytes (KB), of current memory grants. Is not nullable.
memgrant_waiter_countintThe count of queries currently pending on memory grants. Is not nullable.
max_memory_kbbigintThe 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_kbbigintThe amount of memory used, in kilobytes, for the resource pool. Is not nullable.
target_memory_kbbigintThe 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_countbigintThe number of failed memory allocations in the pool since the Resource Govenor statistics were reset. Is not nullable.
min_cpu_percentintThe 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_percentintThe 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_percentintThe 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_percentintThe current configuration for the percentage of total server memory that can be used by requests in this resource pool. Is not nullable.
cap_cpu_percentintApplies to: SQL Server 2012 through SQL Server 2016.

Hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to the specified level. The allowed range for value is from 1 through 100. Is not nullable.
min_iops_per_volumeintApplies to: SQL Server 2014 through SQL Server 2016.

The minimum IO per second (IOPS) per disk volume setting for this Pool. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
max_iops_per_volumeintApplies to: SQL Server 2014 through SQL Server 2016.

The maximum IO per second (IOPS) per disk volume setting for this Pool. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0..
read_io_queued_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total read IOs enqueued since the Resource Govenor was reset. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_io_issued_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total read IOs issued since the Resource Govenor statistics were reset. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_io_completed_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total read IOs completed since the Resource Govenor statistics were reset. Is not nullable.
read_io_throttled_totalintThe total read IOs throttled since the Resource Govenor statistics were reset. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_bytes_totalbigintApplies to: SQL Server 2014 through SQL Server 2016.

The total number of bytes read since the Resource Govenor statistics were reset. Is not nullable.
read_io_stall_total_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

Total time (in milliseconds) between read IO issue and completion. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_io_stall_queued_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

Total time (in milliseconds) between read IO arrival and completion. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.

To determine if the IO setting for the pool is causing latency, subtract read_io_stall_queued_ms from read_io_stall_total_ms.
write_io_queued_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total write IOs enqueued since the Resource Govenor statistics were reset. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
write_io_issued_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total write IOs issued since the Resource Govenor statistics were reset. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
write_io_completed_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total write IOs completed since the Resource Govenor statistics were reset. Is not nullable
write_io_throttled_totalintApplies to: SQL Server 2014 through SQL Server 2016.

The total write IOs throttled since the Resource Govenor statistics were reset. Is not nullable
write_bytes_totalbigintApplies to: SQL Server 2014 through SQL Server 2016.

The total number of bytes written since the Resource Govenor statistics were reset. Is not nullable.
write_io_stall_total_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

Total time (in milliseconds) between write IO issue and completion. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
write_io_stall_queued_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

Total time (in milliseconds) between write IO arrival and completion. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.

This is the delay introduced by IO Resource Governance.
io_issue_violations_totalintApplies to: SQL Server 2014 through SQL Server 2016.

Total IO issue violations. That is, the number of times when the rate of IO issue was lower than the reserved rate. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
io_issue_delay_total_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

Total time (in milliseconds) between the scheduled issue and actual issue of IO. Is nullable. Null if the resource pool is not governed for IO. That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

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.

Requires VIEW SERVER STATE permission.

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)

Community Additions

ADD
Show:
© 2016 Microsoft