I have to say, I only recently noticed this on a tool tip:


and this in the property sheet:


The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn’t remember having seen one before.

Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter:

[sourcecode language=”sql”]SELECT  soh.PurchaseOrderNumber,
FROM    Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
JOIN Sales.CreditCard AS cc
ON soh.CreditCardID = cc.CreditCardID;[/sourcecode]
The entire execution plan looks like this:


We’re focused on the Hash Match join at the top. For logical processing it would be the first operation. For physical operations it would be the last.

The way a Hash Match works is by creating a hash table (in tempdb by the way) and making a hash value. Then, it makes a hash value of the stuff being compared and tries to find matches in the hash table. That’s it.

The residual is if there are additional predicates that also must be matched in order to fully satisfy the query. That’s all. It’s actually quite simple.

What are the implications? Well, this is where it gets fun. You see, the first match, in the hash has to take place, and then, it also has to do the residual probe. The first match is part of the process. The second match is additional work. That’s not good. You can see it in this example plan because the Hash Match operation is estimated as the most costly and that’s probably true.

The key is, drill down to understand what your execution plans are up to.

6 thoughts on “Probe Residual on Hash Match

  1. I’m surprised you haven’t come across this, Grant…

    It’s not just about additional predicates. Most of the time when a hash function is applied, the residual will include the Probe values too, in case two values hash to the same bucket.

    However, you do need to be wary of having additional predicates involved. If those predicates are the selective ones, you can find yourself with a particularly nasty operation.

    I’ll go through a lot more of this in both my precon and Spotlight session at the PASS Summit, and you can read some more on my blog at http://sqlblog.com/blogs/rob_farley/archive/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans.aspx

    After all, residuals also appear in Merge Join and Seeks. And you could even consider that what’s going on inside a Filter or a Scan is a residual – because it’s a predicate applied without any smarts behind it.


  2. It’s one of those things. I’m positive I’ve seen it before, but it wasn’t in any way in my conscious brain. I looked at it and everything went blank. That’s why I’m always impressed by you guys that keep all that stuff in your head. I have to write it down to hope to remember it and then frequently come back to my writings to remind myself how things work again

    Nice post BTW.

Leave a Reply