sys. query_store_wait_stats (Transact-sql) sys.query_store_wait_stats (Transact-SQL)

适用于:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL Server 2017 (14.x)SQL Server 2017 (14.x)yesSQL Server 2017 (14.x)SQL Server 2017 (14.x) and later 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

包含有关查询的等待信息的信息。Contains information about the wait information for the query.

列名称Column name 数据类型Data type 说明Description
wait_stats_idwait_stats_id bigintbigint 表示 plan_id、runtime_stats_interval_id、execution_type 和 wait_category 的等待统计信息的行的标识符。Identifier of the row representing wait statistics for the plan_id, runtime_stats_interval_id, execution_type and wait_category. 只有过去运行时统计信息间隔才是唯一的。It is unique only for the past runtime statistics intervals. 对于当前处于活动状态的时间间隔,可能有多个行表示 plan_id 引用的计划的等待统计信息,并且执行类型由 execution_type 表示,而等待类别由 wait_category 表示。For the currently active interval, there may be multiple rows representing wait statistics for the plan referenced by plan_id, with the execution type represented by execution_type and the wait category represented by wait_category. 通常,一行代表刷新到磁盘的等待统计信息,而其他 () 表示内存中状态。Typically, one row represents wait statistics that are flushed to disk, while other(s) represent in-memory state. 因此,若要获取每个间隔的实际状态,需要聚合指标,按 plan_id、runtime_stats_interval_id、execution_type 和 wait_category 进行分组。Hence, to get actual state for every interval you need to aggregate metrics, grouping by plan_id, runtime_stats_interval_id, execution_type and wait_category.
plan_idplan_id bigintbigint 外键。Foreign key. 联接到 sys.databases)的 query_store_plan (Joins to sys.query_store_plan (Transact-SQL).
runtime_stats_interval_idruntime_stats_interval_id bigintbigint 外键。Foreign key. 联接到 sys.databases)的 query_store_runtime_stats_interval (Joins to sys.query_store_runtime_stats_interval (Transact-SQL).
wait_categorywait_category tinyinttinyint 使用下表对等待类型进行分类,然后在这些等待类别中聚合等待时间。Wait types are categorized using the table below, and then wait time is aggregated across these wait categories. 不同的等待类别需要不同的跟进分析来解决该问题,但在同一类别中等待的类型会导致类似的故障排除体验,并且除了等待外,还提供受影响的查询。Different wait categories require a different follow-up analysis to resolve the issue, but wait types from the same category lead to similar troubleshooting experiences, and providing the affected query in addition to the waits is the missing piece to complete the majority of such investigations successfully.
wait_category_descwait_category_desc nvarchar(128)nvarchar(128) 有关 "等待类别" 字段的文本说明,请查看下表。For textual description of the wait category field, review the table below.
execution_typeexecution_type tinyinttinyint 确定查询执行的类型:Determines type of query execution:

0-常规执行 (成功完成) 0 - Regular execution (successfully finished)

3-客户端启动的已中止执行3 - Client initiated aborted execution

4-异常中止执行4 - Exception aborted execution
execution_type_descexecution_type_desc nvarchar(128)nvarchar(128) 执行类型字段的文本说明:Textual description of the execution type field:

0-常规0 - Regular

3-已中止3 - Aborted

4-异常4 - Exception
total_query_wait_time_mstotal_query_wait_time_ms bigintbigint CPU wait查询计划在聚合间隔和等待类别内的总时间(以毫秒为单位) (报告) 。Total CPU wait time for the query plan within the aggregation interval and wait category (reported in milliseconds).
avg_query_wait_time_msavg_query_wait_time_ms floatfloat 聚合间隔和等待类别中每次执行的查询计划的平均等待持续时间,以毫秒) (报告。Average wait duration for the query plan per execution within the aggregation interval and wait category (reported in milliseconds).
last_query_wait_time_mslast_query_wait_time_ms bigintbigint 聚合间隔和等待类别中查询计划的上次等待持续时间,以毫秒) (报告。Last wait duration for the query plan within the aggregation interval and wait category (reported in milliseconds).
min_query_wait_time_msmin_query_wait_time_ms bigintbigint CPU wait查询计划 (报告的聚合间隔和等待类别中的最小时间(以毫秒为单位)) 。Minimum CPU wait time for the query plan within the aggregation interval and wait category (reported in milliseconds).
max_query_wait_time_msmax_query_wait_time_ms bigintbigint CPU wait查询计划 (报告的聚合间隔和等待类别中的最长时间(以毫秒为单位)) 。Maximum CPU wait time for the query plan within the aggregation interval and wait category (reported in milliseconds).
stdev_query_wait_time_msstdev_query_wait_time_ms floatfloat Query wait 聚合间隔和等待类别中查询计划的持续时间标准偏差,以毫秒) (报告。Query wait duration standard deviation for the query plan within the aggregation interval and wait category (reported in milliseconds).

等待类别映射表Wait categories mapping table

"%" 用作通配符"%" is used as a wildcard

整数值Integer value 等待类别Wait category 等待类型包括在类别中Wait types include in the category
00 UnknownUnknown UnknownUnknown
11 CPUCPU SOS_SCHEDULER_YIELDSOS_SCHEDULER_YIELD
22 工作线程Worker Thread THREADPOOLTHREADPOOL
33 LockLock LCK_M_%LCK_M_%
44 Latch LATCH_%LATCH_%
55 缓冲区闩锁Buffer Latch PAGELATCH_%PAGELATCH_%
66 缓冲区 IOBuffer IO PAGEIOLATCH_%PAGEIOLATCH_%
77 汇编*Compilation* RESOURCE_SEMAPHORE_QUERY_COMPILERESOURCE_SEMAPHORE_QUERY_COMPILE
88 SQL CLRSQL CLR CLR%,SQLCLR%CLR%, SQLCLR%
99 镜像Mirroring DBMIRROR%DBMIRROR%
1010 事务Transaction 事务%,DTC%,TRAN_MARKLATCH_%,MSQL_XACT_%,TRANSACTION_MUTEXXACT%, DTC%, TRAN_MARKLATCH_%, MSQL_XACT_%, TRANSACTION_MUTEX
1111 空闲Idle SLEEP_%、LAZYWRITER_SLEEP、SQLTRACE_BUFFER_FLUSH、SQLTRACE_INCREMENTAL_FLUSH_SLEEP、SQLTRACE_WAIT_ENTRIES、FT_IFTS_SCHEDULER_IDLE_WAIT、XE_DISPATCHER_WAIT、REQUEST_FOR_DEADLOCK_SEARCH、LOGMGR_QUEUE、ONDEMAND_TASK_QUEUE、CHECKPOINT_QUEUE、XE_TIMER_EVENTSLEEP_%, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES, FT_IFTS_SCHEDULER_IDLE_WAIT, XE_DISPATCHER_WAIT, REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, ONDEMAND_TASK_QUEUE, CHECKPOINT_QUEUE, XE_TIMER_EVENT
1212 预防Preemptive PREEMPTIVE_%PREEMPTIVE_%
1313 Service BrokerService Broker BROKER_% ** (但不 BROKER_RECEIVE_WAITFOR) **BROKER_% (but not BROKER_RECEIVE_WAITFOR)
1414 事务日志 IOTran Log IO 数据库准备、LOGBUFFER、LOGMGR_RESERVE_APPEND、LOGMGR_FLUSH、LOGMGR_PMM_LOG、CHKPT.、WRITELOGLOGMGR, LOGBUFFER, LOGMGR_RESERVE_APPEND, LOGMGR_FLUSH, LOGMGR_PMM_LOG, CHKPT, WRITELOG
1515 网络 IONetwork IO ASYNC_NETWORK_IO、NET_WAITFOR_PACKET、PROXY_NETWORK_IO EXTERNAL_SCRIPT_NETWORK_IOFASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF
1616 ParallelismParallelism CXPACKET、EXCHANGE、HT%、BMP%、BP%CXPACKET, EXCHANGE, HT%, BMP%, BP%
1717 内存Memory RESOURCE_SEMAPHORE、CMEMTHREAD、CMEMPARTITIONED、EE_PMOLOCK、MEMORY_ALLOCATION_EXT、RESERVED_MEMORY_ALLOCATION_EXT、MEMORY_GRANT_UPDATERESOURCE_SEMAPHORE, CMEMTHREAD, CMEMPARTITIONED, EE_PMOLOCK, MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT, MEMORY_GRANT_UPDATE
1818 用户等待User Wait WAITFOR、WAIT_FOR_RESULTS、BROKER_RECEIVE_WAITFORWAITFOR, WAIT_FOR_RESULTS, BROKER_RECEIVE_WAITFOR
1919 跟踪Tracing TRACEWRITE、SQLTRACE_LOCK、SQLTRACE_FILE_BUFFER、SQLTRACE_FILE_WRITE_IO_COMPLETION、SQLTRACE_FILE_READ_IO_COMPLETION、SQLTRACE_PENDING_BUFFER_WRITERS、SQLTRACE_SHUTDOWN、QUERY_TRACEOUT、TRACE_EVTNOTIFFTRACEWRITE, SQLTRACE_LOCK, SQLTRACE_FILE_BUFFER, SQLTRACE_FILE_WRITE_IO_COMPLETION, SQLTRACE_FILE_READ_IO_COMPLETION, SQLTRACE_PENDING_BUFFER_WRITERS, SQLTRACE_SHUTDOWN, QUERY_TRACEOUT, TRACE_EVTNOTIFF
2020 全文搜索Full Text Search FT_RESTART_CRAWL、全文收集、MSSEARCH、FT_METADATA_MUTEX、FT_IFTSHC_MUTEX、FT_IFTSISM_MUTEX、FT_IFTS_RWLOCK、FT_COMPROWSET_RWLOCK、FT_MASTER_MERGE、FT_PROPERTYLIST_CACHE、FT_MASTER_MERGE_COORDINATOR、PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNCFT_RESTART_CRAWL, FULLTEXT GATHERER, MSSEARCH, FT_METADATA_MUTEX, FT_IFTSHC_MUTEX, FT_IFTSISM_MUTEX, FT_IFTS_RWLOCK, FT_COMPROWSET_RWLOCK, FT_MASTER_MERGE, FT_PROPERTYLIST_CACHE, FT_MASTER_MERGE_COORDINATOR, PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
2121 其他磁盘 IOOther Disk IO ASYNC_IO_COMPLETION、IO_COMPLETION、BACKUPIO、WRITE_COMPLETION、IO_QUEUE_LIMIT、IO_RETRYASYNC_IO_COMPLETION, IO_COMPLETION, BACKUPIO, WRITE_COMPLETION, IO_QUEUE_LIMIT, IO_RETRY
22 22 复制Replication SE_REPL_%,REPL_%,HADR_% ** (但不 HADR_THROTTLE_LOG_RATE_GOVERNOR **) PWAIT_HADR_、REPLICA_WRITES%、FCB_REPLICA_WRITE、FCB_REPLICA_READ、PWAIT_HADRSIM、SE_REPL_%, REPL_%, HADR_% (but not HADR_THROTTLE_LOG_RATE_GOVERNOR), PWAIT_HADR_%, REPLICA_WRITES, FCB_REPLICA_WRITE, FCB_REPLICA_READ, PWAIT_HADRSIM
23 23 日志速率调控器Log Rate Governor LOG_RATE_GOVERNOR、POOL_LOG_RATE_GOVERNOR、HADR_THROTTLE_LOG_RATE_GOVERNOR INSTANCE_LOG_RATE_GOVERNORLOG_RATE_GOVERNOR, POOL_LOG_RATE_GOVERNOR, HADR_THROTTLE_LOG_RATE_GOVERNOR, INSTANCE_LOG_RATE_GOVERNOR

当前不支持编译等待类别。Compilation wait category is currently not supported.

权限Permissions

需要 VIEW DATABASE STATE 权限。Requires the VIEW DATABASE STATE permission.

另请参阅See Also