sys.dm_resource_governor_resource_pools (Transact-SQL)sys.dm_resource_governor_resource_pools (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

返回有关当前资源池状态、资源池的当前配置以及资源池统计信息的信息。Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.

备注

若要从Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data Warehouse调用此,请使用名称dm_pdw_nodes_resource_governor_resource_poolsTo call this from Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_resource_governor_resource_pools.

列名称Column name 数据类型Data type 说明Description
pool_idpool_id intint 资源池的 ID。The ID of the resource pool. 不可为 null。Is not nullable.
namename sysnamesysname 资源池的名称。The name of the resource pool. 不可为 null。Is not nullable.
statistics_start_timestatistics_start_time datetimedatetime 为该池重置统计信息的时间。The time when statistics was reset for this pool. 不可为 null。Is not nullable.
total_cpu_usage_mstotal_cpu_usage_ms bigintbigint 自重置资源调控器统计信息以来的累计 CPU 使用量(毫秒)。The cumulative CPU usage in milliseconds since the Resource Govenor statistics were reset. 不可为 null。Is not nullable.
cache_memory_kbcache_memory_kb bigintbigint 当前的缓存内存总用量(以 KB 为单位)。The current total cache memory usage in kilobytes. 不可为 null。Is not nullable.
compile_memory_kbcompile_memory_kb bigintbigint 当前总的内存盗用量(以 KB 为单位)。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. 不可为 null。Is not nullable.
used_memgrant_kbused_memgrant_kb bigintbigint 当前通过内存授予使用(盗用)的内存总量。The current total used (stolen) memory from memory grants. 不可为 null。Is not nullable.
total_memgrant_counttotal_memgrant_count bigintbigint 此资源池中的内存授予累计计数。The cumulative count of memory grants in this resource pool. 不可为 null。Is not nullable.
total_memgrant_timeout_counttotal_memgrant_timeout_count bigintbigint 此资源池中内存授予超时的累计计数。The cumulative count of memory grant time-outs in this resource pool. 不可为 null。Is not nullable.
active_memgrant_countactive_memgrant_count intint 当前内存授予计数。The current count of memory grants. 不可为 null。Is not nullable.
active_memgrant_kbactive_memgrant_kb bigintbigint 当前内存授予总量(以 KB 为单位)。The sum, in kilobytes (KB), of current memory grants. 不可为 null。Is not nullable.
memgrant_waiter_countmemgrant_waiter_count intint 内存授予过程中当前挂起的查询数。The count of queries currently pending on memory grants. 不可为 null。Is not nullable.
max_memory_kbmax_memory_kb bigintbigint 该资源池可拥有的最大内存量(以 KB 为单位)。The maximum amount of memory, in kilobytes, that the resource pool can have. 这取决于当前设置和服务器状态。This is based on the current settings and server state. 不可为 null。Is not nullable.
used_memory_kbused_memory_kb bigintbigint 此资源池已用的内存量(以 KB 为单位)。The amount of memory used, in kilobytes, for the resource pool. 不可为 null。Is not nullable.
target_memory_kbtarget_memory_kb bigintbigint 此资源池试图获取的目标内存量(以 KB 为单位)。The target amount of memory, in kilobytes, the resource pool is trying to attain. 这取决于当前设置和服务器状态。This is based on the current settings and server state. 不可为 null。Is not nullable.
out_of_memory_countout_of_memory_count bigintbigint 自重置资源调控器统计信息以来池中内存分配失败次数。The number of failed memory allocations in the pool since the Resource Govenor statistics were reset. 不可为 null。Is not nullable.
min_cpu_percentmin_cpu_percent intint 存在 CPU 争用时此资源池中所有请求有保障的平均 CPU 带宽的当前配置。The current configuration for the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. 不可为 null。Is not nullable.
max_cpu_percentmax_cpu_percent intint 存在 CPU 争用时允许此资源池中的所有请求使用的最大平均 CPU 带宽的当前配置。The current configuration for the maximum average CPU bandwidth allowed for all requests in the resource pool when there is CPU contention. 不可为 null。Is not nullable.
min_memory_percentmin_memory_percent intint 存在内存争用时此资源池中所有请求有保障的内存量的当前配置。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. 不可为 null。Is not nullable.
max_memory_percentmax_memory_percent intint 此资源池中的请求可使用的总服务器内存百分比的当前配置。The current configuration for the percentage of total server memory that can be used by requests in this resource pool. 不可为 null。Is not nullable.
cap_cpu_percentcap_cpu_percent intint 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

资源池中的所有请求都将收到的 CPU 带宽硬性上限。Hard cap on the CPU bandwidth that all requests in the resource pool will receive. 将 CPU 最大带宽级别限制为指定的级别。Limits the maximum CPU bandwidth level to the specified level. 允许的值范围为 1 到 100。The allowed range for value is from 1 through 100. 不可为 null。Is not nullable.
min_iops_per_volumemin_iops_per_volume intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

针对此池的每个磁盘卷设置的每秒最小 I/O 数 (IOPS)。The minimum IO per second (IOPS) per disk volume setting for this Pool. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
max_iops_per_volumemax_iops_per_volume intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

针对此池的每个磁盘卷设置的每秒最大 I/O 数 (IOPS)。The maximum IO per second (IOPS) per disk volume setting for this Pool. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置均为 0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0..
read_io_queued_totalread_io_queued_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器以来排队的读取 IO 总数。The total read IOs enqueued since the Resource Govenor was reset. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_io_issued_totalread_io_issued_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来发出的读取 IO 总数。The total read IOs issued since the Resource Govenor statistics were reset. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_io_completed_totalread_io_completed_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来完成的读取 IO 总数。The total read IOs completed since the Resource Govenor statistics were reset. 不可为 null。Is not nullable.
read_io_throttled_totalread_io_throttled_total intint 自重置资源调控器统计信息以来限制的读取 IO 总数。The total read IOs throttled since the Resource Govenor statistics were reset. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
read_bytes_totalread_bytes_total bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来读取的总字节数。The total number of bytes read since the Resource Govenor statistics were reset. 不可为 null。Is not nullable.
read_io_stall_total_msread_io_stall_total_ms bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

读 IO 到达和完成之间的总时间(毫秒)。Total time (in milliseconds) between read IO arrival and completion. 不可为 null。Is not nullable.
read_io_stall_queued_msread_io_stall_queued_ms bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

读 IO 到达和发出之间的总时间(毫秒)。Total time (in milliseconds) between read IO arrival and issue. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.

若要确定池的 IO 设置是否导致延迟,请从read_io_stall_total_ms中减去read_io_stall_queued_msTo 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_totalwrite_io_queued_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来排队的写入 IO 总数。The total write IOs enqueued since the Resource Govenor statistics were reset. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
write_io_issued_totalwrite_io_issued_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来发出的写入 IO 总数。The total write IOs issued since the Resource Govenor statistics were reset. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
write_io_completed_totalwrite_io_completed_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来完成的写入 IO 总数。The total write IOs completed since the Resource Govenor statistics were reset. 不可为 Null。Is not nullable
write_io_throttled_totalwrite_io_throttled_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来限制的写入 IO 总数。The total write IOs throttled since the Resource Govenor statistics were reset. 不可为 Null。Is not nullable
write_bytes_totalwrite_bytes_total bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

自重置资源调控器统计信息以来写入的总字节数。The total number of bytes written since the Resource Govenor statistics were reset. 不可为 null。Is not nullable.
write_io_stall_total_mswrite_io_stall_total_ms bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

写 IO 到达和完成之间的总时间(毫秒)。Total time (in milliseconds) between write IO arrival and completion. 不可为 null。Is not nullable.
write_io_stall_queued_mswrite_io_stall_queued_ms bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

写 IO 到达和发出之间的总时间(毫秒)。Total time (in milliseconds) between write IO arrival and issue. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.

这是 IO 资源调控所引入的延迟。This is the delay introduced by IO Resource Governance.
io_issue_violations_totalio_issue_violations_total intint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

总 IO 发出违反数。Total IO issue violations. 即 IO 发出率低于保留比率时的次数。That is, the number of times when the rate of IO issue was lower than the reserved rate. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
io_issue_delay_total_msio_issue_delay_total_ms bigintbigint 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

预定发出 IO 和实际发出 IO 之间的总时间(毫秒)。Total time (in milliseconds) between the scheduled issue and actual issue of IO. 可以为 Null。Is nullable. 如果没有为 IO 调控资源池,则为 null。Null if the resource pool is not governed for IO. 也就是说,资源池 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 设置是0。That is, the Resource Pool MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are 0.
pdw_node_idpdw_node_id intint 适用于: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW), 并行数据仓库Parallel Data Warehouse

此分发所在的节点的标识符。The identifier for the node that this distribution is on.

备注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. 若要查看存储的配置元数据,请使用 sys. resource_governor_resource_pools 目录视图。To see the stored configuration metadata, use the sys.resource_governor_resource_pools catalog view.

权限Permissions

需要 VIEW SERVER STATE 权限。Requires VIEW SERVER STATE permission.

另请参阅See Also

动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
sys. dm_resource_governor_workload_groups (Transact-sql) sys.dm_resource_governor_workload_groups (Transact-SQL)
sys. resource_governor_resource_pools (Transact-sql) sys.resource_governor_resource_pools (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)ALTER RESOURCE GOVERNOR (Transact-SQL)