Query tuning 101: What’s a probe residual?

Query tuning is an important process that will probably never go away and sharpening your tuning skills is always a good idea.

I’ve spoken on this topic many times and talked about probe residuals before. Mostly at SQL Saturday events and for some online webinars.

A probe residual is important because they can indicate key performance problems that might not otherwise be brought to your attention.

What is a probe residual?

Simply put, a probe residual is an extra operation that must be performed to compete the matching process. Extra being left over things to do.

Let’s look at the following example:

SELECT   P.Name,
D.OrderQty,
D.UnitPrice
FROM     Production.ProductArchive2013 AS P
INNER JOIN Sales.SalesOrderDetail AS D ON (p.ProductID = D.ProductID)

SELECT   P.Name,
D.OrderQty,
D.UnitPrice
FROM     Production.ProductArchive2014 AS P
INNER JOIN Sales.SalesOrderDetail AS D ON (p.ProductID = D.ProductID)
GO

These two queries are the same minus selecting from two different productarchive tables. Note that the top query has a slightly lower cost.

What’s different?

image

Without knowing about Probe Residual you’d think these plans were the same when in fact they are not.

On further investigation, the Hash Match operator reveals a residual operation:

image

Why is that and what is that?

In this particular case, the residual operation is due to an implicit conversion; though, this isn’t indicated anywhere in the query plan whatsoever. The implicit isn’t even found searching through the plan XML.

image

Reviewing the table DDL shows that the ProductID column of the ProductArchive2014 was created with BIGINT and it’s INT everywhere else.

CREATE TABLE [Production].[ProductArchive2014](
[ProductID] [bigint] NOT NULL, …

CREATE TABLE [Production].[ProductArchive2013](
[ProductID] [int] NOT NULL, …

CREATE TABLE [Sales].[SalesOrderDetail](

[ProductID] [int] NOT NULL, …

Simply put, this query could be faster by fixing the data types and the Probe Residual helps reveal this.

Note that not every Probe Residual will be caused by an implicit conversion; however, they are a noteworthy tuning item to review.

For more about query plans be sure to check out my other site: How’s My Plan (howsmyplan.com)

One thought on “Query tuning 101: What’s a probe residual?

  1. Excellent post, thanks! I’ve seen this a couple of times and been rather puzzled by it. I also see it in bitmaps when dealing with large sets of data. Now I’ve got another clue to help puzzle things out 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s