sys.dm_resource_governor_workload_groups (Transact-SQL)sys.dm_resource_governor_workload_groups (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 workload group statistics and the current in-memory configuration of the workload group. 此视图可以与 sys.dm_resource_governor_resource_pools 联接以获取资源池名称。This view can be joined with sys.dm_resource_governor_resource_pools to get the resource pool name.

备注

若要从 Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data Warehouse中调用此名称,请使用名称sys.databases. dm_pdw_nodes_resource_governor_workload_groupsTo 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_workload_groups.

列名Column name 数据类型Data type 描述Description
group_idgroup_id intint 工作负荷组的 ID。ID of the workload group. 不可为 null。Is not nullable.
NAMEname sysnamesysname 工作负荷组的名称。Name of the workload group. 不可为 null。Is not nullable.
pool_idpool_id intint 资源池的 ID。ID of the resource pool. 不可为 null。Is not nullable.
external_pool_idexternal_pool_id intint 适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

外部资源池的 ID。ID of the external resource pool. 不可为 null。Is not nullable.
statistics_start_timestatistics_start_time datetimedatetime 为工作负荷组重置统计信息集合的时间。Time that statistics collection was reset for the workload group. 不可为 null。Is not nullable.
total_request_counttotal_request_count bigintbigint 工作负荷组中已完成请求的累计计数。Cumulative count of completed requests in the workload group. 不可为 null。Is not nullable.
total_queued_request_counttotal_queued_request_count bigintbigint 达到 GROUP_MAX_REQUESTS 限制之后排队请求的累计计数。Cumulative count of requests queued after the GROUP_MAX_REQUESTS limit was reached. 不可为 null。Is not nullable.
active_request_countactive_request_count intint 当前请求计数。Current request count. 不可为 null。Is not nullable.
queued_request_countqueued_request_count intint 当前排队请求计数。Current queued request count. 不可为 null。Is not nullable.
total_cpu_limit_violation_counttotal_cpu_limit_violation_count bigintbigint 超出 CPU 限制的请求累计计数。Cumulative count of requests exceeding the CPU limit. 不可为 null。Is not nullable.
total_cpu_usage_mstotal_cpu_usage_ms bigintbigint 此工作负荷组的累计 CPU 使用情况,以毫秒为单位。Cumulative CPU usage, in milliseconds, by this workload group. 不可为 null。Is not nullable.
max_request_cpu_time_msmax_request_cpu_time_ms bigintbigint 单个请求的最大 CPU 使用情况,以毫秒为单位。Maximum CPU usage, in milliseconds, for a single request. 不可为 null。Is not nullable.

注意: 这是一个测量值,不同于 request_max_cpu_time_sec,这是一个可配置的设置。Note: This is a measured value, unlike request_max_cpu_time_sec, which is a configurable setting. 有关详细信息,请参阅 CPU Threshold Exceeded 事件类For more information, see CPU Threshold Exceeded Event Class.
blocked_task_countblocked_task_count intint 已阻塞任务的当前计数。Current count of blocked tasks. 不可为 null。Is not nullable.
total_lock_wait_counttotal_lock_wait_count bigintbigint 发生的锁等待累计计数。Cumulative count of lock waits that occurred. 不可为 null。Is not nullable.
total_lock_wait_time_mstotal_lock_wait_time_ms bigintbigint 持有锁的时间的累计之和,以毫秒为单位。Cumulative sum of elapsed time, in milliseconds, a lock is held. 不可为 null。Is not nullable.
total_query_optimization_counttotal_query_optimization_count bigintbigint 此工作负荷组中的查询优化累计计数。Cumulative count of query optimizations in this workload group. 不可为 null。Is not nullable.
total_suboptimal_plan_generation_counttotal_suboptimal_plan_generation_count bigintbigint 由于内存不足,在此工作负荷组中出现的不理想计划生成的累计计数。Cumulative count of suboptimal plan generations that occurred in this workload group due to memory pressure. 不可为 null。Is not nullable.
total_reduced_memgrant_counttotal_reduced_memgrant_count bigintbigint 达到了最大查询大小限制的内存授予累计计数。Cumulative count of memory grants that reached the maximum query size limit. 不可为 null。Is not nullable.
max_request_grant_memory_kbmax_request_grant_memory_kb bigintbigint 统计信息重置之后单个请求的最大内存授予大小,以千字节为单位。Maximum memory grant size, in kilobytes, of a single request since the statistics were reset. 不可为 null。Is not nullable.
active_parallel_thread_countactive_parallel_thread_count bigintbigint 并行线程使用情况的当前计数。Current count of parallel thread usage. 不可为 null。Is not nullable.
importanceimportance sysnamesysname 此工作负荷组中请求的相对重要性的当前配置值。Current configuration value for the relative importance of a request in this workload group. 重要性为下列值之一,默认值为 "低"、"中" 或 "高"。Importance is one of the following, with Medium being the default: Low, Medium, or High.

不可为 null。Is not nullable.
request_max_memory_grant_percentrequest_max_memory_grant_percent intint 单个请求的最大内存授予的当前设置,以百分比表示。Current setting for the maximum memory grant, as a percentage, for a single request. 不可为 null。Is not nullable.
request_max_cpu_time_secrequest_max_cpu_time_sec intint 单个请求的最大 CPU 使用限制的当前设置,以秒为单位。Current setting for maximum CPU use limit, in seconds, for a single request. 不可为 null。Is not nullable.
request_memory_grant_timeout_secrequest_memory_grant_timeout_sec intint 单个请求的内存授予超时的当前设置,以秒为单位。Current setting for memory grant time-out, in seconds, for a single request. 不可为 null。Is not nullable.
group_max_requestsgroup_max_requests intint 并发请求最大数的当前设置。Current setting for the maximum number of concurrent requests. 不可为 null。Is not nullable.
max_dopmax_dop intint 工作负荷组的最大并行度。Maximum degree of parallelism for the workload group. 默认值为 0,表示使用全局设置。The default value, 0, uses global settings. 不可为 null。Is not nullable.
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.

RemarksRemarks

此动态管理视图显示了内存中配置。This dynamic management view shows the in-memory configuration. 若要查看存储的配置元数据,请使用 sys.resource_governor_workload_groups 目录视图。To see the stored configuration metadata, use the sys.resource_governor_workload_groups catalog view.

成功执行 ALTER RESOURCE GOVERNOR RESET STATISTICS 后,会重置以下计数器: statistics_start_time、total_request_count、total_queued_request_count、total_cpu_limit_violation_count、total_cpu_usage_ms、max_request_cpu_time_ms、total_lock_wait_count、total_lock_wait_time_ms、total_query_optimization_count、total_suboptimal_plan_generation_count、total_reduced_memgrant_count 和 max_request_grant_memory_kb。When ALTER RESOURCE GOVERNOR RESET STATISTICS is successfully executed, the following counters are reset: statistics_start_time, total_request_count, total_queued_request_count, total_cpu_limit_violation_count, total_cpu_usage_ms, max_request_cpu_time_ms, total_lock_wait_count, total_lock_wait_time_ms, total_query_optimization_count, total_suboptimal_plan_generation_count, total_reduced_memgrant_count, and max_request_grant_memory_kb. statistics_start_time 设置为当前系统日期和时间,其他计数器设置为零(0)。statistics_start_time is set to the current system date and time, the other counters are set to zero (0).

PermissionsPermissions

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

另请参阅See Also

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