sys.dm_exec_query_profiles (Transact-SQL)


Updated: November 16, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Monitors real time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the description field. Or, join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns.

The counters returned are per operator per thread. 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 nameData typeDescription
session_idsmallintIdentifies the session in which this query runs. References dm_exec_sessions.session_id.
request_idintIdentifies the target request. References dm_exec_sessions.request_id.
sql_handlevarbinary(64)Identifies the target query. References dm_exec_query_stats.sql_handle.
plan_handlevarbinary(64)Identify target query (references dm_exec_query_stats.plan_handle.
physical_operator_namenvarchar(256)Physical operator name.
node_idintIdentifies an operator node in the query tree.
thread_idintDistinguishes the threads (for a parallel query) belonging to the same query operator node.
task_addressvarbinary(8)Identifies the SQLOS task that this thread is using. References dm_os_tasks.task_address.
row_countbigintNumber of rows returned by the operator so far.
rewind_countbigintNumber of rewinds so far.
rebind_countbigintNumber of rebinds so far.
end_of_scan_countbigintNumber of end of scans so far.
estimate_row_countbigintEstimated number of rows. It can be useful to compare to estimated_row_count to the actual row_count.
first_active_timebigintThe time, in milliseconds, when the operator was first called.
last_active_timebigintThe time, in milliseconds, when the operator was last called.
open_timebigintTimestamp when open (in milliseconds).
first_row_timebigintTimestamp when first row was opened (in milliseconds).
last_row_timebigintTimestamp when last row was opened(in milliseconds).
close_timebigintTimestamp when close (in milliseconds).
elapsed_time_msbigintTotal elapsed time (in milliseconds) used by the target node’s operations so far.
cpu_time_msbigintTotal CPU time (in milliseconds) use by target node’s operations so far.
database_idsmallintID of the database that contains the object on which the reads and writes are being performed.
object_idintThe identifier for the object on which the reads and writes are being performed. References sys.objects.object_id.
index_idintThe index (if any) the rowset is opened against.
scan_countbigintNumber of table/index scans so far.
logical_read_countbigintNumber of logical reads so far.
physical_read_countbigintNumber of physical reads so far.
read_ahead_countbigintNumber of read-aheads so far.
write_page_countbigintNumber of page-writes so far due to spilling.
lob_logical_read_countbigintNumber of LOB logical reads so far.
lob_physical_read_countbigintNumber of LOB physical reads so far.
lob_read_ahead_countbigintNumber of LOB read-aheads so far.
segment_read_countintNumber of segment read-aheads so far.
segment_skip_countintNumber of segments skipped so far.
actual_read_row_countbigintNumber of rows read by an operator before the residual predicate was applied.
estimated_read_row_countbigintApplies to: Beginning with SQL Server 2016 SP1.
Number of rows estimated to be read by an operator before the residual predicate was applied.

If the query plan node does not have any IO, all the IO-related counters are set to NULL.

The IO-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 groups the counters for all IO to a given table together. With this DMV you will get separate counters for every node in the query plan that performs IO to the table.

  • If there is a parallel scan, this DMV reports counters for each of the parallel threads working on the scan.

Starting with SQL Server 2016 SP1, the legacy query execution statistics profiling infrastructure exists side-by-side with a lightweight query execution statistics profiling infrastructure. The new query execution statistics profiling infrastructure dramatically reduces performance overhead of collecting per-operator query execution statistics, such as actual number of rows. This feature can be enabled either using global startup trace flag 7412, or is automatically turned on when query_thread_profile extended event is used.

System_CAPS_ICON_note.jpg Note

CPU and elapsed times are not supported under the lightweight query execution statistics profiling infrastructure to reduce performance impact.

SET STATISTICS XML ON and SET STATISTICS PROFILE ON always use the legacy query execution statistics profiling infrastructure.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

Step 1: Login to a session in which you plan to run the query you will analyze with sys.dm_exec_query_profiles. To 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  

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

Step 2: Login to a second session that is different from the session in which your query is running.

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;  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions