Rows Read Per Thread: Four Ways to See Them in SQL Server

Rows read per thread show whether a parallel query shares its work evenly. SQL Server hands each thread a slice of the table. The actual plan records how many rows each slice held.

Why the Split Matters

A parallel query finishes when its slowest thread finishes. If one thread gets most of the rows, the others wait for it. The query then runs longer than the thread count suggests. The rows read per thread tell you whether the work was shared or piled onto one thread.

Four methods show the numbers. Two use the actual plan in Management Studio. One reads the last plan with T-SQL. The fourth watches a query that is still running.

Build a Parallel Query

The demo creates a database named ParallelRowsDemo with 500,000 parcels. The database setting LAST_QUERY_PLAN_STATS keeps the last actual plan of each query, which method three reads. It needs SQL Server 2019 or later. The setting belongs to this demo database, and setting it to OFF is the undo.

IF DB_ID(N'ParallelRowsDemo') IS NULL CREATE DATABASE ParallelRowsDemo;
GO
USE ParallelRowsDemo;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
GO
DROP TABLE IF EXISTS dbo.Parcels;
CREATE TABLE dbo.Parcels (ParcelID int NOT NULL PRIMARY KEY, Zone int NOT NULL, WeightKg decimal(8,2) NOT NULL);
INSERT INTO dbo.Parcels (ParcelID, Zone, WeightKg)
SELECT TOP (500000) n, n % 50, 1 + n % 30
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x
ORDER BY n;

The next query groups the parcels by zone. Two hints keep the demo repeatable. MAXDOP 4 allows four threads. The plan preference hint asks for a parallel plan even though the table is small. On a real server, the optimizer decides by cost. The demo shows four threads on a server with four or more processors. With fewer processors, you get that many threads. The alias parcelsRun lets the later query find this statement.

SELECT COUNT(*) AS ZoneCount, SUM(g.TotalKg) AS TotalKg
FROM (SELECT Zone, SUM(WeightKg) AS TotalKg
      FROM dbo.Parcels AS parcelsRun
      GROUP BY Zone) AS g
OPTION (MAXDOP 4, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

Method 1: Operator Properties

Turn on Include Actual Execution Plan in SSMS 22 with Ctrl+M and run the query. Open the Execution plan tab and right-click the Clustered Index Scan operator. Choose Properties. Expand Actual Number of Rows for All Executions. The pane lists one line per thread. The scan shows worker threads 1 to 4. Thread 0 is the coordinator, and it appears only above the Parallelism operator.

Method 2: The Plan XML

Right-click an empty area of the plan and choose Show Execution Plan XML. Search for RunTimeCountersPerThread. Each element carries a Thread number, an ActualRows count and an ActualRowsRead count. Number of Rows Read appears for scans and seeks alike, with or without a predicate. Compare the two counts on each thread to see how many rows it read and how many it passed on.

Method 3: Read the Plan With T-SQL

The same numbers sit in the plan XML that SQL Server keeps for the last run. This query finds the statement by its alias and turns each per thread counter into a row. It needs no plan window, so it works from a script or a monitoring job.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT n.value('@NodeId', 'int') AS NodeId,
       n.value('@PhysicalOp', 'nvarchar(60)') AS Operator,
       t.value('@Thread', 'int') AS Thread,
       t.value('@ActualRows', 'bigint') AS ActualRows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) AS ps
CROSS APPLY ps.query_plan.nodes('//RelOp') AS r(n)
CROSS APPLY r.n.nodes('RunTimeInformation/RunTimeCountersPerThread') AS c(t)
WHERE st.text LIKE N'%AS parcelsRun%'
  AND st.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY NodeId, Thread;

The result lists every operator with a row for each thread. The scan is the interesting one, because it carries the rows read per thread. This sample shows its four threads. Operators above the scan show their own small counts.

The split changes on every run, so your numbers will differ. One number does not change. The scan threads always add up to the 500,000 rows of the table. This query proves it by summing the scan.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT s.Operator, COUNT(*) AS Threads, SUM(s.ActualRows) AS TotalRows
FROM (SELECT n.value('@PhysicalOp', 'nvarchar(60)') AS Operator,
             t.value('@ActualRows', 'bigint') AS ActualRows
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) AS ps
      CROSS APPLY ps.query_plan.nodes('//RelOp[@PhysicalOp="Clustered Index Scan"]') AS r(n)
      CROSS APPLY r.n.nodes('RunTimeInformation/RunTimeCountersPerThread') AS c(t)
      WHERE st.text LIKE N'%AS parcelsRun%'
        AND st.text NOT LIKE N'%dm_exec_query_stats%') AS s
GROUP BY s.Operator;

Four worker threads share the scan. The coordinator, thread 0, appears only for operators above the Parallelism operator.

Method 4: Watch It Live

The first three methods look back at a finished query. A DMV shows the counters while the query runs.

Run a query that lasts a few seconds in one window and the next query in a second window. Replace 60 with the session ID of the first window. The view needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. In SQL Server 2019 and later, lightweight profiling is on by default, so nothing else is needed. Older versions need SET STATISTICS PROFILE ON in the session you watch.

DECLARE @Session int = 60;
SELECT session_id, node_id, physical_operator_name, thread_id, row_count, estimate_row_count
FROM sys.dm_exec_query_profiles
WHERE session_id = @Session
ORDER BY node_id, thread_id;

While a long parallel query runs, the view lists one row for each operator and thread. The row counts grow with every refresh. When the query ends, the rows disappear, so the statement returns nothing for a session that is idle.

Two sibling posts use the same hint from other angles. Force a Parallel Plan: ENABLE_PARALLEL_PLAN_PREFERENCE Hint shows how to force and prove a parallel plan. ENABLE_PARALLEL_PLAN_PREFERENCE Limits: Rows Per Thread covers what the hint cannot do.

Reading an Uneven Split

Small differences are normal, as the sample shows. A thread with 141,312 rows next to one with 117,760 is not a problem. A thread that reads nearly all rows while three read almost none is. That pattern points to skewed data or to a part of the plan that cannot run in parallel.

You could argue that the split is a detail and total time is what counts. Users feel the total. The split explains why the total is long.

What to Remember

Read the rows read per thread from the plan, from T-SQL or from the live DMV. Compare the threads of the scan first, because the scan carries most of the rows. Expect small differences, and investigate a large one.

Run the cleanup script when you finish.

USE master;
GO
DROP DATABASE IF EXISTS ParallelRowsDemo;

A parallel plan is not a fair split, it is a split you can read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.