sys.dm_exec_query_profiles (Transact-SQL)sys.dm_exec_query_profiles (Transact-SQL)

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

正执行查询时监视实时查询进度。Monitors real time query progress while the query is in execution. 例如,使用此 DMV 确定运行缓慢的查询部分。For example, use this DMV to determine which part of the query is running slow. 可使用说明字段中标识的列,将此 DMV 与其他系统 DMV 相联接。Join this DMV with other system DMVs using the columns identified in the description field. 或者,使用时间戳列,将此 DMV 与其他性能计数器(如性能计数器 xperf)相联接。Or, join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns.

返回的表Table Returned

返回的计数器基于每个运算符和每个线程。The counters returned are per operator per thread. 结果是动态的,并且不匹配现有选项的结果,例如, SET STATISTICS XML ON 查询完成后仅创建输出。The results are dynamic and do not match the results of existing options such as SET STATISTICS XML ON which only create output when the query is finished.

列名称Column name 数据类型Data type 说明Description
session_idsession_id smallintsmallint 标识运行此查询的会话。Identifies the session in which this query runs. 引用 dm_exec_sessions.session_id。References dm_exec_sessions.session_id.
request_idrequest_id intint 确定目标请求。Identifies the target request. 引用 dm_exec_sessions.request_id。References dm_exec_sessions.request_id.
sql_handlesql_handle varbinary(64)varbinary(64) 是唯一标识查询所属的批处理或存储过程的标记。Is a token that uniquely identifies the batch or stored procedure that the query is part of. 引用 dm_exec_query_stats.sql_handle。References dm_exec_query_stats.sql_handle.
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. 引用 dm_exec_query_stats. plan_handle。References dm_exec_query_stats.plan_handle.
physical_operator_namephysical_operator_name nvarchar(256)nvarchar(256) 物理运算符名称。Physical operator name.
node_idnode_id intint 标识查询树中的运算符节点。Identifies an operator node in the query tree.
thread_idthread_id intint 区分属于同一个查询运算符节点的线程(针对并行查询)。Distinguishes the threads (for a parallel query) belonging to the same query operator node.
task_addresstask_address varbinary(8)varbinary(8) 确定此线程正在使用的 SQLOS 任务。Identifies the SQLOS task that this thread is using. 引用 dm_os_tasks.task_address。References dm_os_tasks.task_address.
row_countrow_count bigintbigint 运算符迄今返回的行数。Number of rows returned by the operator so far.
rewind_countrewind_count bigintbigint 迄今为止的重绕数。Number of rewinds so far.
rebind_countrebind_count bigintbigint 迄今为止的重新绑定数。Number of rebinds so far.
end_of_scan_countend_of_scan_count bigintbigint 迄今为止的扫描结束次数。Number of end of scans so far.
estimate_row_countestimate_row_count bigintbigint 估计的行数。Estimated number of rows. 可用于将 estimated_row_count 与实际 row_count 进行比较。It can be useful to compare to estimated_row_count to the actual row_count.
first_active_timefirst_active_time bigintbigint 首次调用运算符的时间(毫秒)。The time, in milliseconds, when the operator was first called.
last_active_timelast_active_time bigintbigint 上次调用运算符的时间(毫秒)。The time, in milliseconds, when the operator was last called.
open_timeopen_time bigintbigint 打开时的时间戳(毫秒)。Timestamp when open (in milliseconds).
first_row_timefirst_row_time bigintbigint 打开第一行时的时间戳(毫秒)。Timestamp when first row was opened (in milliseconds).
last_row_timelast_row_time bigintbigint 打开最后一行时的时间戳(毫秒)。Timestamp when last row was opened(in milliseconds).
close_timeclose_time bigintbigint 关闭时的时间戳(毫秒)。Timestamp when close (in milliseconds).
elapsed_time_mselapsed_time_ms bigintbigint 到目前为止,目标节点的操作所用的总运行时间 ((以毫秒为单位)) 。Total elapsed time (in milliseconds) used by the target node's operations so far.
cpu_time_mscpu_time_ms bigintbigint 到目前为止,目标节点的操作使用的总 CPU 时间 ((以毫秒为单位)) 。Total CPU time (in milliseconds) use by target node's operations so far.
database_iddatabase_id smallintsmallint 包含要对其进行读写的对象的数据库的 ID。ID of the database that contains the object on which the reads and writes are being performed.
object_idobject_id intint 要对其进行读写的对象的标识符。The identifier for the object on which the reads and writes are being performed. 引用 sys.objects.object_id。References sys.objects.object_id.
index_idindex_id intint 打开其行级的索引(如果有)。The index (if any) the rowset is opened against.
scan_countscan_count bigintbigint 迄今为止的表/索引扫描数。Number of table/index scans so far.
logical_read_countlogical_read_count bigintbigint 迄今为止的逻辑读取数。Number of logical reads so far.
physical_read_countphysical_read_count bigintbigint 迄今为止的物理读取数。Number of physical reads so far.
read_ahead_countread_ahead_count bigintbigint 迄今为止的预读数。Number of read-aheads so far.
write_page_countwrite_page_count bigintbigint 迄今为止由于溢出而导致的页写入数。Number of page-writes so far due to spilling.
lob_logical_read_countlob_logical_read_count bigintbigint 迄今为止的 LOB 逻辑读取数。Number of LOB logical reads so far.
lob_physical_read_countlob_physical_read_count bigintbigint 迄今为止的 LOB 物理读取数。Number of LOB physical reads so far.
lob_read_ahead_countlob_read_ahead_count bigintbigint 迄今为止的 LOB 预读数。Number of LOB read-aheads so far.
segment_read_countsegment_read_count intint 迄今为止的段预读数。Number of segment read-aheads so far.
segment_skip_countsegment_skip_count intint 迄今为止跳过的段数。Number of segments skipped so far.
actual_read_row_countactual_read_row_count bigintbigint 应用驻留谓词之前由运算符读取的行数。Number of rows read by an operator before the residual predicate was applied.
estimated_read_row_countestimated_read_row_count bigintbigint 适用于:SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始。Applies to: Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1.
在应用残留谓词之前估计要由运算符读取的行数。Number of rows estimated to be read by an operator before the residual predicate was applied.

一般备注General Remarks

如果查询计划节点没有任何 i/o,则所有与 i/o 相关的计数器都将设置为 NULL。If the query plan node does not have any I/O, all the I/O-related counters are set to NULL.

此 DMV 报告的与 i/o 相关的计数器比通过 SET STATISTICS IO 以下两种方式报告的计数器更精细:The I/O-related counters reported by this DMV are more granular than the ones reported by SET STATISTICS IO in the following two ways:

  • SET STATISTICS IO 将所有 i/o 的计数器一起组合在一起。SET STATISTICS IO groups the counters for all I/O to a given table together. 对于此 DMV,你将为查询计划中针对表执行 i/o 的每个节点获取单独的计数器。With this DMV you will get separate counters for every node in the query plan that performs I/O to the table.

  • 如果存在并行扫描,则此 DMV 将报告处理扫描的每个并行线程的计数器。If there is a parallel scan, this DMV reports counters for each of the parallel threads working on the scan.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始, 标准查询执行统计信息分析基础结构轻型查询执行统计分析基础结构 并存。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, the standard query execution statistics profiling infrastructure exists side-by-side with a lightweight query execution statistics profiling infrastructure. SET STATISTICS XML ONSET STATISTICS PROFILE ON 始终使用 标准查询执行统计分析基础结构SET STATISTICS XML ON and SET STATISTICS PROFILE ON always use the standard query execution statistics profiling infrastructure. sys.dm_exec_query_profiles若要填充,必须启用查询分析基础结构之一。For sys.dm_exec_query_profiles to be populated, one of the query profiling infrastructures must be enabled. 有关详细信息,请参阅查询分析基础结构For more information, see Query Profiling Infrastructure.

备注

正在调查的查询必须在启用了查询分析基础结构 开始,在查询开始后启用它将不会在中生成结果 sys.dm_exec_query_profilesThe query under investigation has to start after the query profiling infrastructure has been enabled, enabling it after the query started will not produce results in sys.dm_exec_query_profiles. 有关如何启用查询分析基础结构的详细信息,请参阅 查询分析基础结构For more information on how to enable the query profiling infrastructures, see Query Profiling Infrastructure.

权限Permissions

SQL ServerSQL Server 和 AZURE SQL 托管实例上,需要 VIEW DATABASE STATE 数据库角色的权限和成员身份 db_ownerOn SQL ServerSQL Server and Azure SQL Managed Instance, requires VIEW DATABASE STATE permission and membership of the db_owner database role.
Azure SQL 数据库Azure SQL Database 高级层上,需要具有 VIEW DATABASE STATE 数据库中的权限。On Azure SQL 数据库Azure SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. Azure SQL 数据库Azure SQL Database SQL 数据库的基本、S0 和 S1 服务目标上,对于弹性池中的数据库, 服务器管理员 帐户或 Azure Active Directory 管理员 帐户是必需的。On Azure SQL 数据库Azure SQL Database On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 对于所有其他 SQL 数据库服务目标, VIEW DATABASE STATE 数据库中需要该权限。On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

示例Examples

步骤1:登录到计划运行查询的会话,你将使用进行分析 sys.dm_exec_query_profilesStep 1: Login to a session in which you plan to run the query you will analyze with sys.dm_exec_query_profiles. 配置用于分析的查询 SET STATISTICS PROFILE ONTo configure the query for profiling use SET STATISTICS PROFILE ON. 在同一会话中运行你的查询。Run your query in this same session.

--Configure query for profiling with sys.dm_exec_query_profiles  
SET STATISTICS PROFILE ON;  
GO  

--Or enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)  
DBCC TRACEON (7412, -1);  
GO 
  
--Next, run your query in this session, or in any other session if query profiling has been enabled globally 

步骤2:登录到与运行查询的会话不同的第二个会话。Step 2: Login to a second session that is different from the session in which your query is running.

以下语句总结当前在会话 54 中运行的查询的进度。The following statement summarizes the progress made by the query currently running in session 54. 为此,它基于每个节点的所有线程计算输出行的总数,然后将其与该节点的输出行的估算数目进行比较。To do this, it calculates the total number of output rows from all threads for each node, and compares it to the estimated number of output rows for that node.

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT node_id,physical_operator_name, SUM(row_count) row_count, 
  SUM(estimate_row_count) AS estimate_row_count, 
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)  
FROM sys.dm_exec_query_profiles   
WHERE session_id=54
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;  

另请参阅See Also

动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
与执行相关的动态管理视图和函数 (Transact-SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL)