I Most Certainly Do Have A Join Predicate

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!

BugBurg

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.

One of our join columns is in the where clause, too.

That means our plan looks like this!

Lemons!

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.

Previous Post
Troubleshooting Parameter Sniffing Issues the Right Way: Part 3
Next Post
How to Back Up SQL Server to Azure Blob Storage

15 Comments. Leave new

  • DoubleBarrellDarrell
    March 9, 2018 1:39 pm

    Wow! I knew it! Super Erik
    Thank You, Erik. I like to freak out, too!
    Peace

  • “And all his query plans will vanish in time, like tears in rain.”
    Time to reboot.

  • 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!”

    • Erik Darling
      March 9, 2018 4:21 pm

      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?

  • Roberts Paul
    March 9, 2018 6:19 pm

    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.

    • Erik Darling
      March 9, 2018 6:35 pm

      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

  • Kevin James
    March 9, 2018 7:20 pm

    Does anyone else use ‘helper’ in their index naming conventions? Indexes do help, so I kind of like it.

  • 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!

Menu
{"cart_token":"","hash":"","cart_data":""}