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.
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.
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.
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.
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.
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)
1 Comment. Leave new
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;