April 21, 2021 at 2:29 am
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!
April 21, 2021 at 10:00 am
Nearly right, plans are read Top to Bottom, Right to Left
April 21, 2021 at 11:00 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2021 at 2:34 pm
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
Change is inevitable... Change for the better is not.
April 21, 2021 at 6:11 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply