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.
How can you tell how long each operator took in a query plan
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?
Sign in to comment
...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.
Sign in to comment
1 additional answer
Sort by: Most helpful
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
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.