sys.dm_pdw_nodes_exec_query_profiles (Transact-SQL)

Applies to: Azure Synapse Analytics

Monitors real time data warehouse query progress while the query is in execution.

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Table returned

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 name	Data type	Description
pdw_node_id	int	Unique numeric ID associated with the node.
session_id	smallint	Identifies the session in which this query runs. References dm_exec_sessions.session_id.
request_id	int	Identifies the target request. References dm_exec_sessions.request_id.
sql_handle	varbinary(64)	Is a token that uniquely identifies the batch or stored procedure that the query is part of. References dm_exec_query_stats.sql_handle.
plan_handle	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. References dm_exec_query_stats.plan_handle.
physical_operator_name	nvarchar(256)	Physical operator name.
node_id	int	Identifies an operator node in the query tree.
thread_id	int	Distinguishes the threads (for a parallel query) belonging to the same query operator node.
task_address	varbinary(8)	Identifies the SQLOS task that this thread is using. References dm_os_tasks.task_address.
row_count	bigint	Number of rows returned by the operator so far.
rewind_count	bigint	Number of rewinds so far.
rebind_count	bigint	Number of rebinds so far.
end_of_scan_count	bigint	Number of end of scans so far.
estimate_row_count	bigint	Estimated number of rows. It can be useful to compare to estimated_row_count to the actual row_count.
first_active_time	bigint	The time, in milliseconds, when the operator was first called.
last_active_time	bigint	The time, in milliseconds, when the operator was last called.
open_time	bigint	Timestamp when open (in milliseconds).
first_row_time	bigint	Timestamp when first row was opened (in milliseconds).
last_row_time	bigint	Timestamp when last row was opened(in milliseconds).
close_time	bigint	Timestamp when close (in milliseconds).
elapsed_time_ms	bigint	Total elapsed time (in milliseconds) used by the target node's operations so far.
cpu_time_ms	bigint	Total CPU time (in milliseconds) use by target node's operations so far.
database_id	smallint	ID of the database that contains the object on which the reads and writes are being performed.
object_id	int	The identifier for the object on which the reads and writes are being performed. References sys.objects.object_id.
index_id	int	The index (if any) the rowset is opened against.
scan_count	bigint	Number of table/index scans so far.
logical_read_count	bigint	Number of logical reads so far.
physical_read_count	bigint	Number of physical reads so far.
read_ahead_count	bigint	Number of read-aheads so far.
write_page_count	bigint	Number of page-writes so far due to spilling.
lob_logical_read_count	bigint	Number of LOB logical reads so far.
lob_physical_read_count	bigint	Number of LOB physical reads so far.
lob_read_ahead_count	bigint	Number of LOB read-aheads so far.
segment_read_count	int	Number of segment read-aheads so far.
segment_skip_count	int	Number of segments skipped so far.
actual_read_row_count	bigint	Number of rows read by an operator before the residual predicate was applied.
estimated_read_row_count	bigint	Applies to: Beginning with SQL Server 2016 (13.x) SP1. Number of rows estimated to be read by an operator before the residual predicate was applied.

Remarks

The same remarks in sys.dm_exec_query_profiles apply.

Permissions

Requires VIEW SERVER STATE permission on the server.

See also

Azure Synapse Analytics and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)

Next steps

Azure Synapse Analytics development overview](/azure/sql-data-warehouse/sql-data-warehouse-overview-develop).