Query tuning 101: What’s a probe residual?

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?

 

 

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:

 

 

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.

 

 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)

 

作者:Chuck Lu    GitHub