A SQL Server Query Plan Riddle
Video Summary
In this video, I present a riddle involving two SQL Server queries in SQL Server Management Studio (SSMS) to challenge your analytical skills and deepen your understanding of query optimization. The first query includes the `owner user ID` column from the `post` table with a hash join hint, while the second query omits this column, leading to an optimizer error despite no apparent reason for it. If you enjoy riddles or are interested in SQL Server performance tuning, I invite you to sign up for a membership and help me solve this intriguing puzzle. Your support will ensure more riddle content in future videos! For those who prefer straightforward solutions, feel free to leave a comment expressing your desire for no more riddles—though that might just make the riddles even more tempting.
Full Transcript
Erik Darling here with Darling Data, and in today’s video I have a riddle for you. So if you enjoy riddles, stick around. If you don’t enjoy riddles, I’m giving plenty of warning to avoid having to be riddled by anything. But today I’m going to present a riddle, and tomorrow I’m going to do my best to answer the riddle to your satisfaction. Hopefully you will not react as terribly as the troll under the bridge, and you will not react as terribly as the troll under the bridge. I’m going to eat one of my toes. It hurts to stand. If you would like, if you like riddles, boy, you can show me how much you like riddles by signing up for a membership. So the more people who sign up for a membership after this video, the more riddle content you will get. I will riddle my butt off. If you hate riddles, you can also leave a comment that says, hey, I would sign up for a membership if you never tell another one. The link to do that is down in the video description. If you are just sort of nonplussed by riddles, I hope that’s the right word for that. You can like, you can comment, you can subscribe, or if, I don’t know, whatever you feel like doing. If you want to ask me a question during office hours, I was going to go somewhere with that, and then I decided against it.
If you would like to ask me a question for my office hours episode, that is the link. It will not be blue in the video description, so don’t get freaked out if the colors aren’t the same. If you have your own query plan riddles, if you just have so many riddles that you can’t deal with them all, and you would like some help with that, I am available for hire. You can literally pay me money to solve your SQL Server performance problems.
Health checks, performance analysis, hands-on tuning, dealing with performance emergencies, and training your developers so that they don’t get their toes eaten by the troll under the query plan riddle bridge, whatever. I do all that, and as always, my rates, they’re reasonable. If you would like some also reasonably priced stuff from me, I have training, about 24 hours of it, a little bit more than that, depending on if you watch it at double speed.
If you watch it at regular speed, it’s about 25, 26 hours. But, you can get all that for about 150 US bucks with the discount code right there. Also, it will not be blue in the video description, but the link will be there for you.
Upcoming events, boy, oh boy, oh boy. Spending a lot of time in New York City, aren’t I? SQL Saturday, New York City, coming up on May the 10th, fast approaching, just a couple few weeks away. Get your seats while you can.
There is a performance tuning pre-con on May the 9th by Andreas Valter. And we are all very excited about all of this. We are putting on our finest New York City attire to attend this event. So, please dress appropriately.
And the fine folks at Redgate are bringing Pass on Tour. We will be mini-passing in New York City, August 18th to 20th. The most beautiful time of year to be in New York City.
Then we’ll be in Dallas, September 15th to 17th. Again, beautiful time of year to be in Dallas. And then Amsterdam. Wow, I mean, just beautiful times of year all around. October 1st to 3rd.
And all that’s going to be great. And you should buy tickets and come and hang out with me. Imagine seeing Erik Darling in Amsterdam. Be amazing, right? Who knows?
Who knows what hijinks we could get in together. And then, of course, the big event, Pass Data Community Summit, taking place in Seattle, November 17th to 21st. We will be having a grand old time in beautiful Seattle on the edge of fall.
If you look out across the soundy area, there’s just wonderful foliage everywhere. Highly recommend it. But with that out of the way, let’s riddle ourselves a little bit.
Let’s go over to SQL Server Management Studio. And I’m going to show you two queries. All right? Two queries. We got two of them.
Otherwise, there would be no riddle. And the first query, well, actually, let’s just say there is one difference. Shut up, tooltips. There is one difference between these two queries.
And the first query, the column owner user ID from the post table, that’s P. That’s P up there, is present in the select list. And we have a hash join hint down here.
All right? Obviously, the hash join hint is integral to the riddle. And in the second query, the only difference is that the owner user ID column has been removed. We have quoted that out.
So if we run this first query. Wait, wait, wait. Give this a few seconds. This thing doesn’t have any very special indexes to help it. It takes about four seconds.
But it runs and returns results that are correct. And we get a query plan from it. You can see all this stuff. SSMS is hiding our missing index request a little bit. That’s not so important to the riddle, though.
But we run and we get a valid plan and everything is okay. Now, if I try to run this second query with owner user ID removed, what happens? You get an error.
Not an Eric. We get an error. Though I am frequently confused with such things. The error that we get is that the query processor cannot produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using set force plan.
Well, why is that? Why would owner user ID being emitted from the select list make this an invalid query? Now, if we don’t highlight the hash join hint, if we just highlight this part of the query and we get an estimated plan, this is what it looks like.
We don’t have hash joins at all. We have a nested loops join. We have a nested loops join where SQL Server is saying we don’t have a join predicate, which is a little bit silly because if we highlight, or rather, if we bring up this tooltip and we see that we have a predicate here for where p.owner user ID equals 22656.
And if we highlight this, we will see that we have a predicate where user ID in the comments table equals 22656. So technically, the predicate has been resolved and we don’t need a predicate at the nested loops join. But we also have now a nested loops join going to the users table.
So there are no hash joins in this query. Your job is to figure out why. Your job is to figure out which part of the query makes this query optimizer error occur and try to come up with a reasonable explanation for it. Should you choose to accept this mission, of course.
If you don’t, I understand. You have better things to do. Waiting for the answer. So anyway, this is where I’m going to leave things.
These two queries and, of course, a link to the demo database will be available in a GitHub GIST or GIST. I don’t know if it’s a GitHub GIST, GitHub GIST, GitHub GIST, I don’t know, something like that. It’s hard to tell how they wanted that pronounced.
I’m not going to get into the GIF versus GIF debate on this one. It’s weird to say a GitHub GIST. Anyway, I promise I’m dead sober for these.
That’s the worst. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope that you enjoyed this riddle and I hope that you will try to answer this riddle. If not, then stick around for tomorrow’s video in which I will provide as in-depth an answer to this as possible.
Anyway, thank you for watching. Where’s the button? There’s the button.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.