But unless you’re used to reading Hebrew, isn’t that backwards? We read everything else from left to right… Why should we read query plans in the opposite direction?
The answer is: We really shouldn’t. You’ve been misinformed.
Yes, it’s true that the flow of information is from right to left… All those arrows that are pointing in that direction indicate that. We can see the sources of the data on the right and the eventual destination on the left.
But the query does not start with the operators on the right.
Let’s look at an example:
This is a simple query that produces 3 rows of data. Here is the (actual) execution plan:
The arrows are pointing from right to left, making it look like the Clustered Index Scan and Clustered Index Seek are the ones in charge. But they are NOT. In reality, they are just dumb file cabinets of data that only do what they are told.
Take a look at the properties of the Clustered Index Scan operator:
The job of a Scan operator is to scan the entire clustered index from beginning to end, isn’t it? In this case, it’s supposed to apply the predicate and only pay attention to those rows with ContactID 216. But the Actual Rows in the property sheet is equal to only 2. There are a heckuva lot more than 2 rows with ContactID 216 in the table. How did this operator know when to stop? Isn’t the TOP operator in charge of limiting the rows? But he’s two positions away from the Scan Operator in the plan!
And look at the properties of the Clustered Index Seek:
It shows that the Number of Executions is equal to 2. Why does it execute twice? And what does that have to do with 3 rows?
The actual execution of a query does not consist of the operators on the right pushing their rows to the left. Instead, query execution consists of operators on the left requesting rows from their child operators to their right.
Just before you start a query, all those operators are asleep. Pushing the Execute button in SSMS wakes up the operator on the far left (the SELECT in our example above), and that operator is in charge of waking up the other operators down the tree and instigating the action of acquiring and outputting rows.
Let’s take a closer, more intimate look at what really goes on in this plan when you hit the Execute button. We’ll do it in the form of a dramatic play, just to make it more interesting and up close and personal.
So sit back and enjoy. (Note: For those of you reading this on your phone or other mobile device or certain readers, you may not be able to enjoy the full experience. The entire script below is presented in resplendent color for your convenience and amusement).
Brad Schulz Productions Proudly Presents...
A Second in the Life of a Query Operator
A Play in One Act
Dramatis Personæ:
SQL Engine: It is assumed that you already know this character quite well.
Simon Select: A very efficient, reliable man with impeccable manners. Imagine the butler Hudson in Upstairs, Downstairs.
Tony Top: A somewhat impatient guy with a tough exterior that masks a sometimes kinder man inside. Imagine Tony Soprano.
Nora Nested-Loops: A no-nonsense woman who does her work without complaint. Imagine Mrs. Doubtfire.
Stuart Scan: A simple-minded country boy who is eager to please. Imagine Gomer Pyle.
Stanley Seek: An uncomplicated man who’s very fond of napping, since he never seems to get enough sleep. Imagine Dagwood Bumstead.
Act I Scene 1.
The Setting: A Query Plan Universe, consisting of 5 rooms, each containing an Operator, all asleep in their beds.
VOICE OF SQL ENGINE: Simon, wake up. I have a job for you.
Simon wakes up and promptly gets up from his bed, already wearing a suit with a black swallowtail coat that, amazingly, has no wrinkles in it whatsoever. He straightens his tie.
SIMON: Very good, sir. I will attend to it immediately.
He posts a sign in the status bar that reads “Executing query…” and he places a little green spinner icon next to it to indicate that the SPID is busy.
Then he walks to a door on the opposite side of his room. He has no idea who is in the room behind the door. He politely knocks.
SIMON: (Opening the door a crack) Excuse me.
TONY: Huh?
Tony rubs his eyes and sits up at the edge of his bed.
SIMON: Oh, good morning, Mister Top.
TONY: (Yawning) I keep tellin’ ya, Simon, you don’t have to call me Mister Top. You can call me Tony. How’re ya doin’?
SIMON: Just fine, thank you, Mis---… er… Tony. I have been engaged to display some rows in the SSMS Results Window, and I require your assistance.
TONY: Yeah, sure, no problem. I know the drill.
SIMON: Let us proceed, then. May I please have a row of data?
TONY: Comin’ right up. Just give me a millisecond.
Tony walks across to the door on the opposite side of his room and throws it open, banging the door against the wall.
TONY: Hey you! Get your a** outta bed! I got a job for ya.
Nora is startled out of her sleep by the loud voice, but she soon regains composure and gets up from her bed and approaches Tony and points her finger at him.
NORA: Mister Top! How dare you barge in like that! And there’s no need to be so coarse. I know that I have a job to do, but I will not stand for vulgarity, is that clear?
TONY: Yeah, yeah, okay, sorry, Nora. I didn’t know it was you. Many times the query operators I work with are pretty slow in responding. You ever work with Sonny Sort? Jeez, he takes forever!
NORA: I understand that, but one should always try to treat his or her coworkers with respect.
TONY: Yeah, sure, Nora, whatever. Well, let’s get to work. Gimme a --- … Sorry. (He clears his throat) May I please have a row of data?
NORA: That’s much better, Tony, dear. I’m glad we’re getting down to business. I’ll have that row for you in no time.
Nora walks to the opposite side of her room, where there are two doors. She opens the door on the left.
NORA: Rise and shine! We’ve got work to do.
Stuart stirs in his bed and looks toward the door.
STUART: Well, gawwwllleeee! If it ain’t Nora Nested-Loops!
NORA: Hello, Stuart.
STUART: Well I’m about as excited as a bag full of puppies to be working with you again, Nora. How long has it been since we worked together?
NORA: About 12 seconds, dear.
STUART: Hooo-wheee! Has it really been that long? Well, what can I do for you?
NORA: You know the routine, Stuart. I need a row.
STUART: Right away.
Stuart walks over the file cabinet in his room and looks at the work order that’s tacked to the wall above it.
STUART: Looks like I have to get rows just for ContactID 216 from this here clustered index. I sure do hope that this clustered index ain’t too large. A second or two ago, I had a job where I had to scan over 10,000,000 rows, and, boy, I was busier than a long-tailed cat in a room full of rocking chairs! In fact, I said to myself that---
NORA: Time waits for no one, Stuart dear. I need more action and less talk.
STUART: Oh, sure, sorry, Nora.
He opens the drawer of the file cabinet. It’s full of pages of data, with each page consisting of rows in order of SalesOrderID. He takes out the first page and examines each row on the page, looking for ones with a ContactID value of 216. He finds one. He checks the work order once again.
STUART: Looks like I only gotta give you the SalesOrderID and OrderDate columns.
He pulls those columns from the row and walks back to the door to hand them (as a row) to Nora.
STUART: Here ya go, Nora.
NORA: Thank you, Stuart.
Nora looks at her work order and sees that she is to perform an INNER JOIN using an outer reference of SalesOrderID. She examines the row in her left hand that she received from Stuart and then opens the second door, which is to the right of the door to Stuart’s room.
NORA: Time to get up!
Stanley is startled out of his sleep and falls out of his bed and bumps his head.
STANLEY: (Rubbing the back of his head) Time to get up already? It seems like I’ve only been sleeping for a few hundred milliseconds.
NORA: Yes, it’s that time again, Stanley. We’ve got a job to do. (She looks again at the row she received from Stuart in her left hand) I need you to get me a row for SalesOrderID 43660.
STANLEY: (Yawning) Okay, just a millisecond.
He walks over to a file cabinet in his room. It has three drawers. They are labeled 43659 thru 51821, 51822 thru 62790, and 62791 thru 75123 respectively. Stanley opens the top drawer, and looks through the folders inside and immediately finds one that is labeled 43659 thru 43875. He leafs through the pages inside and immediately finds the page containing rows for SalesOrderID 43660. He checks his work order.
STANLEY: Looks like I’m only supposed to give you the ProductID and UnitPrice columns.
He pulls those columns from the row and walks back to the door to hand them (as a row) to Nora.
STANLEY: Here it is, Nora… A row for SalesOrderID 43660.
NORA: Thank you, dear.
Nora accepts the row in her right hand. She walks over to the copy machine in her room and makes a Xerox copy of the row she has in her left hand (from Stuart). She takes the copy of that row and joins it with the row in her right hand (from Stanley), creating a new row consisting of columns SalesOrderID, OrderDate, ProductID, and UnitPrice. She walks to the door where Tony is waiting.
NORA: Here’s your row, Tony.
TONY: Thanks, Nora.
Tony accepts the row and looks over his work order.
TONY: (to himself) Only 3 rows, huh? Okay. (He makes a tick mark to keep track of the number of rows he receives) That’s number one.
He walks over to the door where Simon is waiting.
TONY: Here ya go, Simon.
SIMON: Thank you, Mister Top.
Simon accepts the row. He meticulously writes the columns of the row into a nice grid structure in the SSMS Results Window, using the font indicated in his work order. Then he walks back to the door.
SIMON: Another row, please, Mister Top.
TONY: Okay, I’m on it. (Walking to the opposite door) Nora, I need another row.
NORA: Alright, Tony. (She looks at the row from Stuart that’s still in her left hand and walks to Stanley’s door) Stanley, dear, I need another row for SalesOrderID 43660.
STANLEY: (Jerking his head up from almost nodding off) Huh? Oh, okay, Nora.
He goes back to the page where he had acquired the previous row. He finds another row for SalesOrderID 43660 and pulls out the appropriate columns and walks back towards Nora.
STANLEY: Here you go, Nora.
NORA: Thank you.
Nora once again makes a Xerox copy of the row in her left hand and joins that copy with the row acquired from Stanley and walks back to Tony.
NORA: Here you are, Tony dear.
TONY: Thanks. (Making another tick mark on his worksheet) That’s two. (Walking back to Simon) Here’s your row, Simon.
SIMON: Very good, Mister Top.
Simon writes the columns of the new row to the grid in the SSMS Results Window and returns once again to Tony’s door.
SIMON: Another row, if you please, Mister Top.
TONY: Okay, you got it. (Walking to the opposite door) Nora, I need another row.
NORA: Alright, Tony. (She looks once again at the row from Stuart that’s still in her left hand and walks to Stanley’s door) Stanley, dear, once again I need another row for SalesOrderID 43660.
STANLEY: (Yawning) Okay.
He goes back to the page where he had acquired the previous row. But there isn’t another row for SalesOrderID 43660.
STANLEY: Sorry, Nora, I’ve already given you all I’ve got for that SalesOrderID.
NORA: Well, thank you for your help, Stanley. You can go back to sleep now.
STANLEY: Thank goodness!
As Stanley shuts the drawer of the file cabinet and rushes towards the bed, Nora closes his door. She throws the row in her left hand into the trash and then goes to the door on her left, where Stuart is standing.
NORA: Stuart, dear, I need another row from you.
STUART: Okay, Nora… I can get that for you faster than green grass goes through a goose.
He goes to the file cabinet and continues scanning rows, looking for the next one with ContactID 216.
STUART: I found one. (Walking back towards Nora) Here it is.
NORA: Thank you, dear.
She accepts the row in her left hand and examines the SalesOrderID in the row, then walks over to Stanley’s door and opens it.
NORA: Wake up, Stanley, dear! I need a row for SalesOrderID 47660.
Stanley falls out of his bed, bumping his head once again.
STANLEY: (Groaning) What? You told me to go back to sleep only a few nanoseconds ago.
NORA: Well, that’s the nature of the job, Stanley, dear. Every time I get a row from Stuart here, I have to wake you up and get rows from you to join to his. Once you give me all the rows relating to Stuart’s then you get to go back to sleep. This is my second row from Stuart, so I’m waking you up for the second time. Every time I wake you up, it’s called an execution.
STANLEY: Yeah, I know. But all this going to sleep and waking up over and over again is killing me! I think the word “execution” is very appropriate! (Yawning) Okay, hold on… What was that SalesOrderID again?
NORA: 47660, dear.
STANLEY: Okay.
Stanley goes to his file cabinet and opens the appropriate drawer and the appropriate folder and the appropriate page and finds the first row for SalesOrderID 47660.
STANLEY: Got one. (Walking back to Nora) Here you go.
NORA: Thank you.
Nora once again makes a Xerox copy of the row in her left hand and joins that copy with the row acquired from Stanley and walks back to Tony.
NORA: Here you are, Tony dear.
TONY: Thanks. (Making another tick mark on his worksheet) That’s three. (Walking back to Simon) Here ya go, Simon.
SIMON: Excellent… Thank you, Mister Top.
Simon adds the new row to the grid in the SSMS Results Window and returns once again to Tony’s door.
SIMON: May I have another row, please, Mister Top?
TONY: (Double-checking his tick marks against his work order) Sorry, Simon, but I can’t give you any more rows. I’ve reached my quota.
SIMON: Well, I suppose that’s it, then. Thank you for your assistance, Mister Top. Time to shut down the operation.
TONY: Okay, Simon. Hey, Nora, time to shut down.
NORA: Alrighty. Stanley and Stuart, it’s time to shut down. You can go both go back to sleep.
STANLEY: Yes! Sleep-time again!
Stanley closes his door and jumps into his bed and is out cold before his head even hits the pillow.
STUART: Okay, Nora. I actually am a little tired. In fact, I’m more exhausted than a mule in---
Nora closes his door before he can finish his hokey down-home analogy. Stuart shrugs his shoulders and climbs into his bed and goes to sleep.
NORA: (Walking back to the door of Tony’s room) Good night, Tony, dear.
TONY: See ya later, Nora.
Nora closes the door, turns off the light, and slips into her bed.
TONY: (Walking back to the door of Simon’s room) Well, Simon, until next time…
SIMON: Always a pleasure, Mister Top.
Tony closes the door, turns off the light, and gets into his bed.
Simon writes the message “(3 row(s) affected)” to the Messages Window. Then he posts a sign that reads “Query executed successfully.” and removes the green spinner icon, replacing it with a checkmark icon.
Then Simon sits at the edge of his bed and smiles to himself on another job well done. He lies down and reaches over for the light and turns it off.
And then he gently closes his eyes.
THE END.
Posts
