Friday, November 19, 2010

A Second in the Life of a Query Operator

There are countless books and blogs out there that tell you that you should read a query plan from right to left.

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:

TOP 3 Query

This is a simple query that produces 3 rows of data. Here is the (actual) execution plan:

TOP 3 Query 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:

Clustered Index Scan Properties

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:

Clustered Index Seek Properties

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.

Executing Query...

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 Scan's Predicate

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 Scan's Output List

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 Seek's Output List

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 Top's Expression

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 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.

Another Job Well Done

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.


Sunday, November 7, 2010

I'm Not Dead

I'm Not DeadThe reports of my death are greatly exaggerated.

Yes, it’s true I haven’t written a blog post since September 13, which is 2 T-SQL Tuesdays ago. It does seem like I’ve disappeared from the face of the earth, but I have a few good excuses.

I had surgery in late September… nothing life-threatening or anything… but it was something that kept me at home convalescing for a good week, and I just didn’t feel like doing anything except watching crap on television. (Well, actually, it wasn't all crap... I had a blast watching the San Francisco Giants win the NL Playoffs and the World Series!)

Anyway, in early October, it was time to go back to work, and even though I wasn’t fully 100% recovered, everything came all at once. I had one client for whom I’ve been doing work for a couple of years, and one of their employees was going on vacation to China for two weeks. He was responsible over the last year for writing code to convert the data from their existing Visual FoxPro database to SQL Server, and the system is set to go live by the first of the year. So everything was starting to come to a head and they were in rush mode. And guess who had to pick up the conversion duties in addition to his own regular duties?

Yep. That would be me. I had no idea what was coming. I ended up putting in 12-hour days for a good two weeks and beyond, even after he came back. And in addition to that, I had two other clients who I was kind of putting off but still gave in and did some development work for them at night from home… not as much as I would like, but, hey, I’m only human.

And now I’ll have to put them off just one week more, because I’m here in Seattle to attend the SQL PASS Summit… my first one. People encouraged me to do a presentation here, but I just wanted to attend and absorb it all with no pressure. That being said, though, I will be hosting a Birds Of A Feather Luncheon table on Tuesday (11:30-1:00) and I will be sitting at one of the Ask The Experts tables on Wednesday (1:00-3:00). Come by and say hi if you’re in the neighborhood.

Flying up to Seattle this morning (Sunday) was an interesting experience. I don’t fly very often at all. When I’ve flown in the past, I usually put my laptop in a small computer bag, bringing only the bare essentials. However, I just bought a new laptop this past summer, and it ended up being just a tad too wide to fit in the bag, so I just kept it in my larger computer bag that I use every day.

Good-Bye, Old FriendWhen I went through security at the airport, I took my laptop out of the bag and put it in a separate bin, and then put both items through the scanner, along with a bin containing my shoes, jacket, belt, and pocket items. When I got to the other side of the scanner, they gave me a menacing look, and I was told that they would have to go through my computer bag by hand. Fine… no problem. But there was a problem. In the bag was my Swiss army knife that I bought in Switzerland 23 years ago when my wife and I were on our honeymoon.

So I had to say good-bye to it forever. Theoretically, I could have mailed it home, but that would mean I would have to leave the security area and re-enter and go through security again, and there wasn’t enough time. Oh well…

Still, the flight on Virgin America was very comfortable… it was my first time on that airline, and I was very pleased. And the Light Rail from SeaTac Airport to downtown Seattle was extremely reasonable and very very quiet (completely unlike the 150 decibels on BART trains in the San Francisco Bay Area). And the weather here was very nice and clear and sunny… not the pouring rain that I was expecting. (Ironically, it was raining back home).

So here I am, looking forward to the presentations at PASS, and especially looking forward to connecting with so many people that I know online but have never met. Hopefully, once I recover from this week, I’ll be able to get back in the saddle and post some interesting stuff here once again.