sequence of SQL server execution plan

  • As per my understanding,  when we read the execution plan, the sql server is run from the bottom to top and from right to left,  the sequence I marked it picture, is it right? if not please tell me the right sequence and how to read the execution plan. thanks!

    Attachments:
    You must be logged in to view attached files.
  • Nearly right, plans are read Top to Bottom, Right to Left

  • OK. This is a fun one.

    Execution plans are logically executed from left to right, and roughly, top to bottom. You can validate I'm right about this by looking at the NodeID for each operator. You'll see that the first node (not the SELECT/INSERT/UPDATE/DELETE node, but the first operating node) will have a NodeID of zero (0). Each subsequent node, going to the right, will have a greater value (they won't always perfectly count 1,2,3,4, etc., there may be gaps, but they'll always be higher).

    Now, the reason people say to read plans from right to left is because of the data flow. While the plans logically instantiate through the NodeIDs, the data flows from the right most, top most, operator. Following the data is a valid way to understand what the plan is doing. Also, you're going to find that the output of a lot of operators changes what you're looking at in the earlier operators. You can get a query that is returning nothing but Expr1012, Expr1042 in the operators, but you think it's TableID, TableValue as the columns. Following outputs in the direction of data flow lets you understand where things like Expr1012 come from.

    So, you can look at plans both ways. The key to really understanding them is knowing that, row mode or batch mode, each operator instantiates in the NodeID order. Each operator then asks the appropriate next operator for, in row mode, one row, in batch mode, (roughly) 1,000 rows. Each of these requests continues down the chain of NodeIDs until a data movement node is reached (scan, seek, spool), and then values are passed back through the chain, 1 row or (roughly) 1,000 rows.

    In short, both ways is right. Hope that helps. For LOTS more detail on reading execution plans, get my book. It's free to download. Paper copies can be purchased from Amazon.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Heh... I agree with Grant on logical execution being left to right and data flow being right to left and both being top to bottom (except on super-umbra-red moons on Tuesday).

    Shifting gears a bit, one of the most important things to remember is that, no matter how you read them, even "Actual" execution plans are riddled with estimates and you must never solely rely on such plans to determine the best performing or lowest resource usage code.

    One of my favorite examples of that is to compare the execution plans of an rCTE to Itzik Ben-Gan's cCTE method (GetNums function) for counting from 1 to a million.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Heh... I agree with Grant on logical execution being left to right and data flow being right to left and both being top to bottom (except on super-umbra-red moons on Tuesday).

    Shifting gears a bit, one of the most important things to remember is that, no matter how you read them, even "Actual" execution plans are riddled with estimates and you must never solely rely on such plans to determine the best performing or lowest resource usage code.

    One of my favorite examples of that is to compare the execution plans of an rCTE to Itzik Ben-Gan's cCTE method (GetNums function) for counting from 1 to a million.

    Total agreement. An execution plan is a description of what happened, or could happen, not a measure of what happened. Always differentiate the two.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply