The query is still running, and everyone wants to know whether it is moving. Lightweight query profiling lets you inspect operator activity without collecting the full historical profile. The numbers describe work in progress, not a reliable countdown clock.
Separate Live Observation From Completed Results
An estimated plan explains the optimizer's chosen strategy before execution. An actual plan adds information gathered while that strategy runs. An in-flight plan shows counters before the request finishes, which answers a different troubleshooting question.
I use live observation when a request appears stuck and its waits need context. I also inspect its blocking and resource waits before interpreting operator counters. A stationary row count can describe a blocked request rather than an inefficient operator.
Lightweight query profiling reduces collection overhead compared with the older standard profiling infrastructure. Reduced overhead does not mean zero overhead. Enable and use observation according to the supported version and the workload you actually need to investigate.
The live view is temporary evidence. Capture it while the request exists and record the observation time. After execution ends, use retained plans, Query Store, or your own saved output for the later investigation.
Check Lightweight Query Profiling for Your Version
SQL Server 2019 introduced the third version of the lightweight infrastructure and enabled it by default. The LIGHTWEIGHT_QUERY_PROFILING database scoped configuration applies from that release onward. SQL Server 2025 retains that configuration.
Earlier lightweight infrastructure appeared in SQL Server 2014, with improvements in SQL Server 2016 Service Pack 1. Those older versions have different activation and collection details. Do not apply current configuration syntax to an older engine without checking its documentation.
SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion,
DB_NAME() AS CurrentDatabase;
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = N'LIGHTWEIGHT_QUERY_PROFILING';On a supported version, the following change enables collection for the current database. Inspect and record the previous setting before changing it. Use your test database first, rather than treating a troubleshooting article as blanket configuration approval.
ALTER DATABASE SCOPED CONFIGURATION
SET LIGHTWEIGHT_QUERY_PROFILING = ON;Changing the setting does not recreate missing history from completed requests. Collection must exist while the relevant query executes. The older trace flag 7412 does not control this infrastructure on SQL Server 2019 and later.
Natively compiled stored procedures are another boundary to recognize. Their execution is not covered in the same way by this profiling infrastructure. Check the supported instrumentation for that procedure type instead of expecting identical operator output.
Create a Controlled Request to Observe
The sample builds an isolated temporary input and performs a grouping operation. Run it in connection A, and record the session identifier. Increase the generated input only when the test finishes too quickly for a second connection to observe.
SELECT @@SPID AS TargetSessionId;
DROP TABLE IF EXISTS #ProfileInput;
CREATE TABLE #ProfileInput
(
ItemId int NOT NULL PRIMARY KEY,
GroupId int NOT NULL,
Payload char(100) NOT NULL
);
;WITH Digits AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n AS n
FROM Digits AS a CROSS JOIN Digits AS b
CROSS JOIN Digits AS c CROSS JOIN Digits AS d CROSS JOIN Digits AS e
)
INSERT #ProfileInput
SELECT n, n % 100, REPLICATE('x', 100)
FROM Numbers;
SELECT a.GroupId, COUNT_BIG(*) AS PairTotal
FROM #ProfileInput AS a
JOIN #ProfileInput AS b
ON b.GroupId = a.GroupId AND b.ItemId < a.ItemId
GROUP BY a.GroupId
OPTION (MAXDOP 2);The join deliberately supplies more work than a simple table scan. Run it only where that work is acceptable. MAXDOP supplies a limit, not a promise that the optimizer will choose a parallel plan.
On my SQL Server 2025 test, it ran long enough for a second connection to watch it. Your hardware and selected plan decide the duration. End the experiment normally, or cancel it if its resource use exceeds your test budget.
Read Rows by Operator and Thread
In connection B, replace the sample session value with A's reported identifier. The profiles view returns counters for active execution operators. Inspect each node and thread instead of assuming one row represents the whole operator.
DECLARE @SessionId int = 52; -- Replace with connection A's identifier.
SELECT session_id, node_id, thread_id, physical_operator_name,
row_count, estimate_row_count,
elapsed_time_ms, cpu_time_ms, logical_read_count
FROM sys.dm_exec_query_profiles
WHERE session_id = @SessionId
ORDER BY node_id, thread_id;Rows can increase between samples, remain zero, or disappear when execution ends. Parallel plans distribute work across threads, so inspect the distribution. Unequal counters deserve investigation when they accompany unequal work and request-level waits.
In my test, elapsed_time_ms and cpu_time_ms stayed at zero, and logical_read_count stayed NULL. Lightweight collection skips those per-operator timings. Compare row_count with estimate_row_count instead.
Do not divide actual rows by estimated rows and publish the result as completion percentage. Estimates are predictions and can be wrong. Different operators also perform different amounts of work for each output row.
Blocking operators complicate that shortcut further. A sort can consume its input before returning its first output row. A zero output counter therefore does not mean that no useful work has happened.
Capture the In-Flight Plan XML
The statistics XML function returns an execution plan for a running request when the required instrumentation is available. It can return no useful plan after the request finishes. Save a returned plan promptly if you need to compare multiple observations.
DECLARE @SessionId int = 52; -- Replace with connection A's identifier.
SELECT session_id, request_id, sql_handle, plan_handle, query_plan
FROM sys.dm_exec_query_statistics_xml(@SessionId);
SELECT session_id, status, wait_type, blocking_session_id,
cpu_time, total_elapsed_time, reads, logical_reads
FROM sys.dm_exec_requests
WHERE session_id = @SessionId;The request view supplies the surrounding state that operator counters cannot explain alone. Blocking, memory waits, and I/O waits all change how the plan progresses. Associate each saved observation with the same session, request, and collection time.
Permissions differ between these interfaces and server versions. On SQL Server 2022 and later, the XML function requires VIEW SERVER PERFORMANCE STATE. Check the profiles view’s own permission entry for your release as well.
Earlier releases document different state permissions, so grant according to the exact interface. Do not add broad administrative membership simply to avoid reading the permission requirement. Monitoring identities should receive only the access needed for their task.
Lightweight Query Profiling Versus Standard Profiling
SET STATISTICS PROFILE and SET STATISTICS XML use the standard profiling infrastructure. The SSMS Include Live Query Statistics option also enables standard profiling. Do not assume every interface displaying a live plan automatically uses the lightweight implementation.
Use the fuller path when your investigation requires details supplied by that collection method. Compare its cost in a representative test before broad use. Historical lightweight versions also differ in their available timing details.
I save two or more observations before calling a request motionless. I then compare its waits and operator activity over the same interval. One frozen screenshot has a remarkable ability to make every query look frozen.
Which operator is producing rows, and what is the request waiting for right now? That pair of questions keeps lightweight query profiling useful. It also prevents an attractive progress display from becoming an invented finish time.
Use lightweight query profiling to locate the active work, then preserve the evidence that supports your diagnosis. A saved observation should include its collection time. Without that context, two accurate counters can still describe different execution intervals.
Related reading on this blog: Live Query Statistics: SQL in Sixty Seconds #104 and Live Query Statistics in 2016 … and More! Notes from the Field #111.
A live plan is not a completion clock, it is evidence of work in progress.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
