Sep 15 2011

Probe Residual on Hash Match

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

image

and this in the property sheet:

image

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,
soh.AccountNumber,
p.Name,
sod.OrderQty,
sod.LineTotal,
cc.CardNumber
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:

image

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 Comments

  • By Rob Farley, September 16, 2011 @ 12:17 am

    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.

    Rob

  • By Grant Fritchey, September 16, 2011 @ 9:36 am

    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.

  • By Rob Farley, September 17, 2011 @ 1:55 am

    Don’t worry – there are plenty of things that I don’t remember. Come to my Spotlight Session so I can have you in the audience. :)

  • By Peter Novosad, February 7, 2013 @ 3:14 pm

    Thanks Grant and Rob, I was not sure why Hash Keys Probe shows up in Probe Residual.

Other Links to this Post

  1. Probe Residual on Hash Match | Database, Performance and Performance Tuning | Syngu — September 16, 2011 @ 1:39 am

  2. SQL Server Execution Plans | Home Of The Scary DBA — September 22, 2011 @ 9:06 am

RSS feed for comments on this post. TrackBack URI

Leave a comment