SQL SERVER – Number of Rows Read Per Threads in Parallel Operations

Let us discuss a very interesting question recently I received during the 1 FREE consulting hour for  SQL Server Performance Tuning Practical Workshop – Recorded Classes. The question was about is there any way to know how many rows each thread execute in parallel operations.

Of course, SQL Server reveals a lot of information about Parallel Operations in the execution plan and there are two different methods to know them.

SQL SERVER - Number of Rows Read Per Threads in Parallel Operations readperthreads-800x221

Setup for Parallel Operations

Before we see the answer, let us write a simple query based on the WideWorldImporters database. It is a very popular sample database available for the latest version of SQL Server. You can read more about this over here: How to Import New Sample Database WideWorldImporters?

Now let us run the following query which runs parallel on my machine.

USE WideWorldImporters
GO
SELECT *
FROM Sales.Invoices i
INNER JOIN Sales.InvoiceLines il 
	ON il.InvoiceID = i.InvoiceID
ORDER BY i.ConfirmedDeliveryTime,
			i.DeliveryInstructions
GO

Next, enable an actual execution plan for the query. Here is how you can enable the actual execution plan in SQL Server Management Studio (SSMS).

Method 1: Execution Plan Operator Properties

When you run the above query with enabled an actual execution plan, you will get the following execution plan in the execution plan. Now go to the right-most operator and right-click over it and select properties.

SQL SERVER - Number of Rows Read Per Threads in Parallel Operations parallel-operations1

Once selected it will open an entire new window with all the properties related to that operator. Here expand the Property Actual Number of Rows or Number of Rows Read. Once expanded you will see how many threads were used for this query and the number of rows reads per threads.

SQL SERVER - Number of Rows Read Per Threads in Parallel Operations parallel-operations2

Method 2: Execution Plan XML

This method also requires the execution plan. Find an open white space in the execution plan and right-click once again and this time select the option of Show Execution Plan XML.

SQL SERVER - Number of Rows Read Per Threads in Parallel Operations parallel-operations3

Once the XML execution plan opens scroll down or search RunTimeCountersPerThread. Over here you will find the run time information of actual rows per threads in the parallel operations.

SQL SERVER - Number of Rows Read Per Threads in Parallel Operations parallel-operations4

Your Task – Parallel Operations and DMV

Well, the two option displayed here is the two very simple and easy option. I was told that there is one more way to find the rows read per threads with the help of DMV. If you know the query for it, please post it here in the comments section, I will blog about it with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Execution Plan, Parallel, SQL CPU, SQL DMV, SQL Scripts, SQL Server, SQL Server Management Studio, SSMS
Previous Post
SQL SERVER – Dynamic SQL and Temporary Tables
Next Post
SQL SERVER – QUOTENAME Function and Custom Quote Character

Related Posts

1 Comment. Leave new

  • Lawrence Cohan
    August 10, 2023 10:10 pm

    You can spy on a query using the sys.dm_exec_query_profiles DMV like below in order to find how many CPU’s are used by the SPID (65 in my case):

    Transact-SQL
    select o.session_id,
    o.scheduler_id,
    w.worker_address,
    qp.node_id,
    qp.physical_operator_name,
    o.task_state,
    wt.wait_type,
    wt.wait_duration_ms,
    qp.cpu_time_ms
    from sys.dm_os_tasks o
    left join sys.dm_os_workers w on ost.worker_address=w.worker_address
    left join sys.dm_os_waiting_tasks wt on w.task_address=wt.waiting_task_address
    and wt.session_id=o.session_id
    left join sys.dm_exec_query_profiles qp on w.task_address=qp.task_address
    where o.session_id=65
    order by scheduler_id, worker_address, node_id;

    Reply

Leave a Reply