2017-04-21

Running Query Execution Statistics

SQL Server 2014 introduced a new DMV, sys.dm_exec_query_profiles, which can be used to monitors real time query progress while the query is in execution. The session to check must be either SET STATISTICS XML ON, or SET STATISTICS PROFILE ON, so most likely you will run the query to check in SSMS.
Below sql script employs this DMV to show the operations of a particular sql session, with the most expensive I/O operations shown on top:

DECLARE @session_id smallint = <spid of the query to check in SSMS>
SELECT
P.plan_handle, P.physical_operator_name, OBJECT_NAME(P.[object_id]) AS objName,
P.node_id, P.index_id, P.cpu_time_ms, P.estimate_row_count,
P.row_count, P.logical_read_count, P.elapsed_time_ms,
P.read_ahead_count, P.scan_count
FROM sys.dm_exec_query_profiles AS P
WHERE P.session_id = @session_id
ORDER BY (P.row_count-P.estimate_row_count) + P.logical_read_count DESC

Sample output:

No comments:

Post a Comment