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

Anonymous
2021-09-17T20:45:41.457+00:00

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

  1. the clustered index scan took 1.6 seconds,
  2. the table scan and sort took .13 and .17 seconds
  3. but the many to many merge sort took a whopping 40 minutes?
    133234-image.png
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,631 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-09-17T21:42:30.233+00:00

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-20T02:57:42.77+00:00

    Hi @Anonymous ,

    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.

    0 comments