Waiting for a completed plan gives little help while the request is still running. sys.dm_exec_query_statistics_xml can expose an in-flight plan with current execution statistics. Read those numbers as changing progress, then combine them with waits and operator behavior before diagnosing the delay.
Call sys.dm_exec_query_statistics_xml for a Live Request
sys.dm_exec_requests lists active requests. The plan function accepts a session identifier and can return available transient Showplan statistics for that session. A request can finish between discovery and plan retrieval, so missing output is not necessarily a diagnostic failure.
I identify the session and database first. The statement's wait, elapsed time, and status help distinguish a request doing work from one waiting for another resource. A plan picture alone does not tell you which condition dominates at the sampling moment.
The next query excludes the inspecting session and keeps only user sessions through is_user_process. A session_id above 50 is not a safe user filter, since system tasks use those numbers too. CROSS APPLY keeps rows where the function returns a result. The query_plan column can still be NULL when no plan is available for the current context. Use an approved administrative account with the supported DMV permissions. A live plan is a useful window, but it does not pause the request so you can read comfortably.
SELECT r.session_id,r.request_id,DB_NAME(r.database_id) AS DatabaseName,
r.status,r.command,r.wait_type,r.wait_resource,r.total_elapsed_time,
x.query_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id=r.session_id
CROSS APPLY sys.dm_exec_query_statistics_xml(r.session_id) AS x
WHERE s.is_user_process=1 AND r.session_id<>@@SPID;Use sys.dm_exec_query_statistics_xml while the request is active, and keep its partial observations separate from a completed actual execution plan.
Open the Plan and Look for Current Work
In SSMS, open the returned XML plan through its plan link. Inspect the operators doing work and the flow of rows between them. The counts are transient and can change before the displayed plan is fully inspected. Save the capture time if the observation needs to be compared later.
Lightweight query profiling is enabled by default from SQL Server 2019, providing the infrastructure behind relevant live statistics. Earlier builds and specific settings require their own supported profiling conditions. Verify the server's actual configuration rather than assuming a missing plan means the request never had one.
I look for a consistent pattern over a few deliberate observations, not a rapid unrestricted polling loop. Large reads, an unexpectedly broad join, or a blocked operator can direct the next check. But an operator that has returned few rows can still be processing input. The live output count is not a completion percentage unless the operation's semantics and total required work support that interpretation.
Inspect Per-Operator Profile Rows Carefully
sys.dm_exec_query_profiles exposes profile data for qualifying active queries. Its row_count and estimate_row_count fields let you examine actual progress beside estimates. The rows also identify node and thread, so parallel work needs careful interpretation.
Set @SessionID to a session_id from the first query. The next query targets that session and retains thread_id instead of summing everything into one unexplained operator total. Estimates and actual counts can have different per-thread contexts. Do not add repeated estimate values and compare them with a casually summed actual count as if that were a valid cardinality ratio.
Which operator is growing far beyond its expected work? Look at the node's role, the current execution phase, and the estimate's scope. A partially completed scan is expected to have fewer rows than its final estimate. An early mismatch in the other direction can be useful evidence, but it still needs the complete operator context and eventual actual plan for confirmation.
DECLARE @SessionID smallint=51;
SELECT session_id,node_id,thread_id,physical_operator_name,row_count,estimate_row_count
FROM sys.dm_exec_query_profiles
WHERE session_id=@SessionID
ORDER BY node_id,thread_id;
Match the Live Plan With Wait Evidence
The next query shows active request state and SQL text for the selected session. The returned text can be the full batch, so identify the active statement through the request's context when several statements exist. Keep the session identifier and capture time with the plan.
A blocked query can show little operator progress because it is waiting on another transaction. A CPU-intensive query can continue progressing without a long current wait. Storage and memory conditions create other patterns. Correlate the plan with the relevant wait rather than selecting a fix from one changing row count.
The request's wait is also a snapshot. It can move between resources during execution. Use a small number of targeted observations and the appropriate longer-term evidence when the problem recurs. The objective is a concrete explanation of the request's current behavior, not a monitoring query that adds unnecessary pressure while the server is already struggling.
DECLARE @SessionID smallint=51;
SELECT SYSUTCDATETIME() AS CapturedAt,r.session_id,r.status,r.blocking_session_id,
r.wait_type,r.wait_time,r.wait_resource,r.cpu_time,r.logical_reads,t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id=@SessionID;Respect the Limits of sys.dm_exec_query_statistics_xml Snapshots
Live counts do not replace a completed actual execution plan. Operators can process input before producing output, execute repeatedly, or participate in parallel branches. A request's final resource use and results remain unknown while it is still running.
Plan availability also has supported limitations, including deep XML nesting and changing request lifetime. An empty profile result needs an infrastructure and timing check before concluding that the query performed no work. Keep capture failures distinct from workload observations.
Query text and plan parameters can contain sensitive context, so save only what the investigation requires and use the approved retention path. Profiling has overhead even when lightweight infrastructure is designed to reduce it. Avoid enabling broader expensive collection merely because a single transient value was absent. Start with the available supported evidence and add collection only when the unresolved question justifies it.
Confirm the Diagnosis After Completion
When the request completes, capture the final actual plan through the appropriate supported method and compare its final row counts and resource evidence with the live observations. Query Store can provide retained plans and runtime aggregates for recurring work when it captured the query.
Validate any proposed index, estimate, or query change with representative completed executions. A live observation is useful for locating the investigation, but a performance fix needs final correctness and workload evidence. Do not terminate a session simply because one partial operator count looked surprising.
sys.dm_exec_query_statistics_xml helps explain work while it is in progress. Pair its plan with per-thread profile context and current waits, then confirm the diagnosis after completion. The useful conclusion distinguishes observed progress, unresolved final behavior, and the evidence needed for the next action.
Related reading on this blog: Live Query Statistics: SQL in Sixty Seconds #104 and Alerting on Long-Running Queries With a SQL Agent Job.
An in-flight row count is not a final result, it is progress observed while the request is still changing.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
