How can you tell how long each operator took in a query plan

Question

How can you tell how long each operator took in a query plan

Hollomon, Brett 41

I have a many to many join operator in a query that takes a long time to run. I have the plan XML but I don't know how to see how long each operator took. I have attached a part of a query plan. In this image is it saying that

the clustered index scan took 1.6 seconds,
the table scan and sort took .13 and .17 seconds
but the many to many merge sort took a whopping 40 minutes?

Answer 1

Erland Sommarskog 78,111 MVP

So the Sort alone did not take 40 minutes and the Merge Join another 40 minutes. The Merge join took 40 minutes, but the time for the Sort includes the time for the operators right to it. Same goes for the other operators. The Sort in the lower right on its own seems to have taken 42 ms., that is 0.174-0.132.

tibor_karaszi@hotmail.com 4,291 Reputation points MVP

2021-09-19T14:38:15.43+00:00

...and to make it clear, many operators can work in parallel on the query. I.e., one operator gets a row from the right-hand side and does whatever its task is, and then hand over the row to its left-hand operator. I.e., rows flow from right-to-left over several operators at the same time. OTOH, other operators (like sort) cant ouput any data until it has done all its work.

So you can't really say how long time one operator takes, but you can say how long time it took until an operator has finished its work, from the time when you started the query.

Answer 2

Hi @Hollomon, Brett ,

The plan is interpreted from right-to-left and top-to-bottom.
Please see this article about execution plans: https://www.sqlshack.com/execution-plans-in-sql-server/
Here is a MS doc: https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

How can you tell how long each operator took in a query plan

1 additional answer

Activity