sys.dm_exec_query_stats (Transact-SQL)sys.dm_exec_query_stats (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

返回 SQL ServerSQL Server中缓存查询计划的聚合性能统计信息。Returns aggregate performance statistics for cached query plans in SQL ServerSQL Server. 缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. 在从缓存删除计划时,也将从该视图中删除对应行。When a plan is removed from the cache, the corresponding rows are eliminated from this view.

备注

  • 每次执行时, sys.databases dm_exec_query_stats 的结果可能会有所不同,因为数据只反映完成的查询,而不是仍在进行中的查询。The results of sys.dm_exec_query_stats may vary with each execution as the data only reflects finished queries, and not ones still in-flight.
  • 若要从或调用此 Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) 并行数据仓库Parallel Data Warehouse ,请使用名称 dm_pdw_nodes_exec_query_statsTo call this from Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse) or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_query_stats.
列名称Column name 数据类型Data type 说明Description
sql_handlesql_handle varbinary(64)varbinary(64) 是唯一标识查询所属的批处理或存储过程的标记。Is a token that uniquely identifies the batch or stored procedure that the query is part of.

通过调用 sys.dm_exec_sql_text 动态管理函数,sql_handle 可以和 statement_start_offsetstatement_end_offset 一起用于检索查询的 SQL 文本。sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
statement_start_offsetstatement_start_offset intint 指示行所说明的查询在其批查询或持久化对象文本中的开始位置(以字节为单位,从 0 开始)。Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offsetstatement_end_offset intint 指示行所说明的查询在其批查询或持久化对象文本中的结束位置(以字节为单位,从 0 开始)。Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. 对于之前的版本 SQL Server 2014 (12.x)SQL Server 2014 (12.x) ,值-1 指示批处理的结束。For versions before SQL Server 2014 (12.x)SQL Server 2014 (12.x), a value of -1 indicates the end of the batch. 不再包含尾随的注释。Trailing comments are no longer included.
plan_generation_numplan_generation_num bigintbigint 可用于在重新编译后区分不同计划实例的序列号。A sequence number that can be used to distinguish between instances of plans after a recompile.
plan_handleplan_handle varbinary(64)varbinary(64) 是一个标记,用于唯一标识已执行并且其计划驻留在计划缓存中或当前正在执行的批处理的查询执行计划。Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. 可以将此值传递给 sys.dm_exec_query_plan 动态管理函数来获取查询计划。This value can be passed to the sys.dm_exec_query_plan dynamic management function to obtain the query plan.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0x000。Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
creation_timecreation_time datetimedatetime 编译计划的时间。Time at which the plan was compiled.
last_execution_timelast_execution_time datetimedatetime 上次开始执行计划的时间。Last time at which the plan started executing.
execution_countexecution_count bigintbigint 计划自上次编译以来所执行的次数。Number of times that the plan has been executed since it was last compiled.
total_worker_timetotal_worker_time bigintbigint 此计划自编译以来执行所用的 CPU 时间总量(以微秒为单位报告,但仅精确到毫秒)。Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

对于本机编译的存储过程,如果许多执行所用的时间都不到 1 毫秒,则 total_worker_time 可能不精确。For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.
last_worker_timelast_worker_time bigintbigint 上次执行计划所用的 CPU 时间(以微秒为单位报告,但仅精确到毫秒)。CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed the last time the plan was executed. 11
min_worker_timemin_worker_time bigintbigint 此计划在单次执行期间所用的最小 CPU 时间(以微秒为单位报告,但仅精确到毫秒)。Minimum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 11
max_worker_timemax_worker_time bigintbigint 此计划在单次执行期间所用的最大 CPU 时间(以微秒为单位报告,但仅精确到毫秒)。Maximum CPU time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed during a single execution. 11
total_physical_readstotal_physical_reads bigintbigint 此计划自编译后在执行期间所执行的物理读取总次数。Total number of physical reads performed by executions of this plan since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_physical_readslast_physical_reads bigintbigint 上次执行计划时所执行的物理读取次数。Number of physical reads performed the last time the plan was executed.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
min_physical_readsmin_physical_reads bigintbigint 此计划在单个执行期间所执行的最少物理读取次数。Minimum number of physical reads that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_physical_readsmax_physical_reads bigintbigint 此计划在单个执行期间所执行的最多物理读取次数。Maximum number of physical reads that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_logical_writestotal_logical_writes bigintbigint 此计划自编译后在执行期间所执行的逻辑写入总次数。Total number of logical writes performed by executions of this plan since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_logical_writeslast_logical_writes bigintbigint 最近完成的计划执行期间更新的缓冲池页数。Number of buffer pool pages dirtied during the most recently completed execution of the plan.

读取某个页面后,该页面仅在第一次修改时才会更新。After a page is read, the page becomes dirty only the first time it is modified. 页面变得脏时,此数字会递增。When a page becomes dirty, this number is incremented. 对已更新的页面进行的后续修改不会影响此数字。Subsequent modifications of an already dirty page do not affect this number.

查询内存优化表时,此数字始终为0。This number will always be 0 when querying a memory-optimized table.
min_logical_writesmin_logical_writes bigintbigint 此计划在单个执行期间所执行的最少逻辑写入次数。Minimum number of logical writes that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_logical_writesmax_logical_writes bigintbigint 此计划在单个执行期间所执行的最多逻辑写入次数。Maximum number of logical writes that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_logical_readstotal_logical_reads bigintbigint 此计划自编译后在执行期间所执行的逻辑读取总次数。Total number of logical reads performed by executions of this plan since it was compiled.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
last_logical_readslast_logical_reads bigintbigint 上次执行计划时所执行的逻辑读取次数。Number of logical reads performed the last time the plan was executed.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
min_logical_readsmin_logical_reads bigintbigint 此计划在单个执行期间所执行的最少逻辑读取次数。Minimum number of logical reads that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
max_logical_readsmax_logical_reads bigintbigint 此计划在单个执行期间所执行的最多逻辑读取次数。Maximum number of logical reads that this plan has ever performed during a single execution.

当查询内存优化的表时,此项将始终为 0。Will always be 0 querying a memory-optimized table.
total_clr_timetotal_clr_time bigintbigint 时间,以微秒为单位报告 (但仅精确到毫秒) ,在 MicrosoftMicrosoft .NET Framework.NET Framework 公共语言运行) (时内使用此计划自编译后的执行此计划。Time, reported in microseconds (but only accurate to milliseconds), consumed inside MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. CLR 对象可以是存储过程、函数、触发器、类型和聚合。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
last_clr_timelast_clr_time bigintbigint 在上一次执行此计划期间,在 .NET Framework.NET Framework 公共语言运行时 (CLR) 对象内执行所用的时间(以微秒为单位报告,但仅精确到毫秒)。Time, reported in microseconds (but only accurate to milliseconds) consumed by execution inside .NET Framework.NET Framework CLR objects during the last execution of this plan. CLR 对象可以是存储过程、函数、触发器、类型和聚合。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
min_clr_timemin_clr_time bigintbigint 此计划在单次执行期间在 .NET Framework.NET Framework CLR 对象内所用的最小时间(以微秒为单位报告,但仅精确到毫秒)。Minimum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside .NET Framework.NET Framework CLR objects during a single execution. CLR 对象可以是存储过程、函数、触发器、类型和聚合。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
max_clr_timemax_clr_time bigintbigint 此计划在单次执行期间在 .NET Framework.NET Framework CLR 内所用的最大时间(以微秒为单位报告,但仅精确到毫秒)。Maximum time, reported in microseconds (but only accurate to milliseconds), that this plan has ever consumed inside the .NET Framework.NET Framework CLR during a single execution. CLR 对象可以是存储过程、函数、触发器、类型和聚合。The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
total_elapsed_timetotal_elapsed_time bigintbigint 上次完成执行此计划所用的总时间(以微秒为单位报告,但仅精确到毫秒)。Total elapsed time, reported in microseconds (but only accurate to milliseconds), for completed executions of this plan.
last_elapsed_timelast_elapsed_time bigintbigint 最近一次完成执行此计划所用的时间(以微秒为单位报告,但仅精确到毫秒)。Elapsed time, reported in microseconds (but only accurate to milliseconds), for the most recently completed execution of this plan.
min_elapsed_timemin_elapsed_time bigintbigint 任何一次完成执行此计划所用的最小时间(以微秒为单位报告,但仅精确到毫秒)。Minimum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
max_elapsed_timemax_elapsed_time bigintbigint 任何一次完成执行此计划所用的最大时间(以微秒为单位报告,但仅精确到毫秒)。Maximum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.
query_hashquery_hash **二进制 (8) **Binary(8) 对查询计算的二进制哈希值,用于标识具有类似逻辑的查询。Binary hash value calculated on the query and used to identify queries with similar logic. 可以使用查询哈希确定仅仅是文字值不同的查询的聚合资源使用情况。You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
query_plan_hashquery_plan_hash **二进制 (8) **binary(8) 对查询执行计划计算的二进制哈希值,用于标识类似的查询执行计划。Binary hash value calculated on the query execution plan and used to identify similar query execution plans. 可以使用查询计划哈希查找具有类似执行计划的查询的累积成本。You can use query plan hash to find the cumulative cost of queries with similar execution plans.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0x000。Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
total_rowstotal_rows bigintbigint 查询返回的总行数。Total number of rows returned by the query. 不能为 null。Cannot be null.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
last_rowslast_rows bigintbigint 上一次执行查询返回的行数。Number of rows returned by the last execution of the query. 不能为 null。Cannot be null.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
min_rowsmin_rows bigintbigint 查询在一次执行过程中所返回的最小行数。Minimum number of rows ever returned by the query during one execution. 不能为 null。Cannot be null.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
max_rowsmax_rows bigintbigint 一次执行期间查询返回的最大行数。Maximum number of rows ever returned by the query during one execution. 不能为 null。Cannot be null.

当本机编译的存储过程查询内存优化的表时,此项将始终为 0。Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.
statement_sql_handlestatement_sql_handle varbinary(64)varbinary(64) 适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

仅当打开查询存储并为该特定查询收集统计信息时,才填充非 NULL 值。Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
statement_context_idstatement_context_id 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.

仅当打开查询存储并为该特定查询收集统计信息时,才填充非 NULL 值。Populated with non-NULL values only if Query Store is turned on and collecting the stats for that particular query.
total_doptotal_dop bigintbigint 此计划自编译后使用的并行度的总和。The total sum of degree of parallelism this plan used since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_doplast_dop bigintbigint 上一次执行此计划时的并行度。The degree of parallelism when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_dopmin_dop bigintbigint 此计划在一次执行期间所用的最小并行度。The minimum degree of parallelism this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_dopmax_dop bigintbigint 此计划在一次执行期间所用的最大并行度。The maximum degree of parallelism this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_grant_kbtotal_grant_kb bigintbigint 此计划自编译后收到的总保留内存授予量(KB)。The total amount of reserved memory grant in KB this plan received since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_grant_kblast_grant_kb bigintbigint 上次执行此计划时的保留内存授予量(KB)。The amount of reserved memory grant in KB when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_grant_kbmin_grant_kb bigintbigint 在一次执行过程中,此计划以前收到的保留内存授予的最小数量(KB)。The minimum amount of reserved memory grant in KB this plan ever received during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_grant_kbmax_grant_kb bigintbigint 在一次执行过程中,此计划以前收到的保留内存授予的最大数量(KB)。The maximum amount of reserved memory grant in KB this plan ever received during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_used_grant_kbtotal_used_grant_kb bigintbigint 此计划自编译以来使用的保留内存授予总数(KB)。The total amount of reserved memory grant in KB this plan used since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_used_grant_kblast_used_grant_kb bigintbigint 上一次执行此计划时使用的内存授予量(KB)。The amount of used memory grant in KB when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_used_grant_kbmin_used_grant_kb bigintbigint 在一次执行期间,此计划使用的最小内存授予量(KB)。The minimum amount of used memory grant in KB this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_used_grant_kbmax_used_grant_kb bigintbigint 在一次执行期间,此计划使用的最大内存授予量(KB)。The maximum amount of used memory grant in KB this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_ideal_grant_kbtotal_ideal_grant_kb bigintbigint 此计划自编译后估计的理想内存授予总量(KB)。The total amount of ideal memory grant in KB this plan estimated since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_ideal_grant_kblast_ideal_grant_kb bigintbigint 上次执行此计划时的理想内存授予量(KB)。The amount of ideal memory grant in KB when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_ideal_grant_kbmin_ideal_grant_kb bigintbigint 此计划在一次执行期间估计的最小理想内存授予量(KB)。The minimum amount of ideal memory grant in KB this plan ever estimated during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_ideal_grant_kbmax_ideal_grant_kb bigintbigint 此计划在一次执行期间估计的最大理想内存授予量(KB)。The maximum amount of ideal memory grant in KB this plan ever estimated during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_reserved_threadstotal_reserved_threads bigintbigint 此计划自编译以来曾使用过的保留并行线程的总数。The total sum of reserved parallel threads this plan ever used since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_reserved_threadslast_reserved_threads bigintbigint 上次执行此计划时保留的并行线程的数目。The number of reserved parallel threads when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_reserved_threadsmin_reserved_threads bigintbigint 此计划在一次执行期间所用的保留并行线程的最小数目。The minimum number of reserved parallel threads this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_reserved_threadsmax_reserved_threads bigintbigint 此计划在一次执行过程中使用的保留并行线程的最大数目。The maximum number of reserved parallel threads this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_used_threadstotal_used_threads bigintbigint 此计划自编译以来曾使用过的已用并行线程的总数。The total sum of used parallel threads this plan ever used since it was compiled. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
last_used_threadslast_used_threads bigintbigint 上次执行此计划时使用的并行线程数。The number of used parallel threads when this plan executed last time. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
min_used_threadsmin_used_threads bigintbigint 此计划在一次执行期间使用的最小并行线程数。The minimum number of used parallel threads this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
max_used_threadsmax_used_threads bigintbigint 此计划在一次执行过程中使用的最大并行线程数。The maximum number of used parallel threads this plan ever used during one execution. 查询内存优化表时,它将始终为0。It will always be 0 for querying a memory-optimized table.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.
total_columnstore_segment_readstotal_columnstore_segment_reads bigintbigint 查询读取的列存储段的总数。The total sum of columnstore segments read by the query. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_columnstore_segment_readslast_columnstore_segment_reads bigintbigint 上次执行查询所读取的列存储段的数目。The number of columnstore segments read by the last execution of the query. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_columnstore_segment_readsmin_columnstore_segment_reads bigintbigint 查询在一次执行期间读取的列存储段的最小数目。The minimum number of columnstore segments ever read by the query during one execution. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_columnstore_segment_readsmax_columnstore_segment_reads bigintbigint 查询在一次执行过程中读取的最大列存储段数。The maximum number of columnstore segments ever read by the query during one execution. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
total_columnstore_segment_skipstotal_columnstore_segment_skips bigintbigint 查询跳过的列存储段的总数。The total sum of columnstore segments skipped by the query. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_columnstore_segment_skipslast_columnstore_segment_skips bigintbigint 上次执行查询时跳过的列存储段数。The number of columnstore segments skipped by the last execution of the query. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_columnstore_segment_skipsmin_columnstore_segment_skips bigintbigint 查询在一次执行过程中跳过的列存储段的最小数目。The minimum number of columnstore segments ever skipped by the query during one execution. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_columnstore_segment_skipsmax_columnstore_segment_skips bigintbigint 查询在一次执行过程中跳过的列存储段的最大数目。The maximum number of columnstore segments ever skipped by the query during one execution. 不能为 null。Cannot be null.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
total_spillstotal_spills bigintbigint 自编译以来执行此查询所溢出的总页数。The total number of pages spilled by execution of this query since it was compiled.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
last_spillslast_spills bigintbigint 上次执行查询时溢出的页数。The number of pages spilled the last time the query was executed.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
min_spillsmin_spills bigintbigint 此查询在一次执行期间溢出的最小页数。The minimum number of pages that this query has ever spilled during a single execution.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
max_spillsmax_spills bigintbigint 此查询在一次执行期间溢出的最大页数。The maximum number of pages that this query has ever spilled during a single execution.

适用于:从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始Applies to: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3
pdw_node_idpdw_node_id intint 此分发所在的节点的标识符。The identifier for the node that this distribution is on.

适用于: Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse)并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse Analytics(SQL 数据仓库)Azure Synapse Analytics (SQL Data Warehouse), 并行数据仓库Parallel Data Warehouse
total_page_server_readstotal_page_server_reads bigintbigint 此计划自编译以来执行的远程页面服务器读取的总次数。Total number of remote page server reads performed by executions of this plan since it was compiled.

适用于: Azure SQL Database 超大规模Applies to: Azure SQL Database Hyperscale
last_page_server_readslast_page_server_reads bigintbigint 上次执行计划时所执行的远程页面服务器读取次数。Number of remote page server reads performed the last time the plan was executed.

适用于: Azure SQL Database 超大规模Applies To: Azure SQL Database Hyperscale
min_page_server_readsmin_page_server_reads bigintbigint 此计划在单次执行期间所执行的最少远程页面服务器读取次数。Minimum number of remote page server reads that this plan has ever performed during a single execution.

适用于: Azure SQL Database 超大规模Applies To: Azure SQL Database Hyperscale
max_page_server_readsmax_page_server_reads bigintbigint 此计划在单次执行期间所执行的远程页面服务器读取次数上限。Maximum number of remote page server reads that this plan has ever performed during a single execution.

适用于: Azure SQL Database 超大规模Applies To: Azure SQL Database Hyperscale

备注

1 对于启用统计信息收集时的本机编译的存储过程,以毫秒为单位收集工作线程时间。1 For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. 如果查询执行的时间不到1毫秒,则该值将为0。If the query executes in less than one millisecond, the value will be 0.

权限Permissions

在上 SQL ServerSQL Server ,需要 VIEW SERVER STATE 权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
SQL 数据库SQL Database 高级层上,需要具有 VIEW DATABASE STATE 数据库中的权限。On SQL 数据库SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL 数据库SQL Database 标准层和基本层上,需要 服务器管理员Azure Active Directory 管理员 帐户。On SQL 数据库SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

备注Remarks

查询完成后,将更新该视图中的统计信息。Statistics in the view are updated when a query is completed.

示例Examples

A.A. 查找 TOP N 查询Finding the TOP N queries

下列示例返回了按平均 CPU 时间排名的前五个查询的信息。The following example returns information about the top five queries ranked by average CPU time. 此示例将根据查询的查询哈希对查询进行聚合,以便按照查询的累积资源消耗来分组在逻辑上等效的查询。This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;  

B.B. 对查询返回行计数聚合Returning row count aggregates for a query

以下示例返回查询的行计数聚合信息(总行数、最小行数、最大行数和上一次行数)。The following example returns row count aggregate information (total rows, minimum rows, maximum rows and last rows) for queries.

SELECT qs.execution_count,  
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,   
                 (CASE WHEN qs.statement_end_offset = -1   
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2   
                       ELSE qs.statement_end_offset end -  
                            qs.statement_start_offset  
                 )/2  
             ) AS query_text,   
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,   
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows  
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt   
WHERE qt.text like '%SELECT%'   
ORDER BY qs.execution_count DESC;  

另请参阅See also

与执行相关的动态管理视图和函数 (Transact-sql) Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys. dm_exec_sql_text (Transact-sql) sys.dm_exec_sql_text (Transact-SQL)
sys. dm_exec_query_plan (Transact-sql) sys.dm_exec_query_plan (Transact-SQL)
sys. dm_exec_procedure_stats (Transact-sql) sys.dm_exec_procedure_stats (Transact-SQL)
sys. dm_exec_trigger_stats (Transact-sql) sys.dm_exec_trigger_stats (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)sys.dm_exec_cached_plans (Transact-SQL)