FREAK OUT
You wrote a query. You joined tables.
You have the right ON clause.
You have the right WHERE clause.
But the query plan has a problem!
How Could This Happen To Me?
Oh, relax. You’re not crazy. You just assumed the worst.
Like me whenever I feel pain near my liver.
Call the mortician.
For a query like this, the optimizer can play some tricks.
|
1 2 3 4 5 |
SELECT TOP 10 p.OwnerUserId, c.Id FROM dbo.Posts AS p JOIN dbo.Comments AS c ON p.OwnerUserId = c.UserId WHERE p.OwnerUserId = 22656 |
One of our join columns is in the where clause, too.
That means our plan looks like this!
You see, when the optimizer looks at the join and the where, it knows that if it pushes the predicate to the two index seeks, whatever values come out will match.
I don’t think it even needs the join at that point, but hey.
It certainly doesn’t need the warning.
Thanks for reading!
Brent says: Erik’s like that dying replicant at the end of Blade Runner. He’s seen things you people wouldn’t believe. Also, he has superhuman strength.
15 Comments. Leave new
Wow! I knew it! Super Erik
Thank You, Erik. I like to freak out, too!
Peace
Thanks, Darrell. I like you a lot better than Single Barrell Steve.
“And all his query plans will vanish in time, like tears in rain.”
Time to reboot.
That legit made me emotional.
Would this create a false positive for the No Join Predicate warning in sp_BlitzCache? Just happens that I was reviewing a few of those the other day, and saying to myself, “I Most Certainly Do Have A Join Predicate!”
DW – yeah, unfortunately. I’ve been trying to think of ways to avoid it without adding a lot of overhead to the already sturdy xml parsing.
Okay, that makes me feel better. I thought I was missing something when trying to figure that out.
One other question – The heap lays on its back, its rows baking in the hot sun, beating its RIDs, trying to create a clustered index but it can’t. Not without your help. But you’re not helping. Why is that?
DW — It’s Jared Leto.
if you want people to subscribe and take your supposedly super human educational courses, you need to provide a better explanation and not just assume everyone understands the point you are trying to make. We know you know it but if you cannot properly explain the idea, it is a waste of time to bother with your training or your blog.
Explain to us lesser beings why this is such a problem.
Roberts — Sure, let me know what you feel short changed on, and I’d more than happy to update the post.
When I wrote it, I intended for it to quickly explain why someone may see this warning when it’s undeserved. I think it holds up to that.
Have a great weekend!
Erik
Does anyone else use ‘helper’ in their index naming conventions? Indexes do help, so I kind of like it.
Aww, that’s adorable!
With thanks! Valuable information!
Interesting. I’m amazed that there are execution plans available to begin with and that there are people that come up with the repeatable demonstrable code to explain the anomalies. Well done, Mr. Darling!
Jeff — well, I probably learned from watching you, so there 😉