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)
相关文章
- SQL Query aggregate may not appear in WHERE clause
- Find or Query Data with the mongo Shell
- Match All Query
- PDO 学习与使用 ( 一 ) :PDO 对象、exec 方法、query 方法与防 SQL 注入
- BuguMongo是一个MongoDB Java开发框架,集成了DAO、Query、Lucene、GridFS等功能
- MySQL慢查询(二) - pt-query-digest详解慢查询日志 pt-query-digest 慢日志分析
- 【bzoj1803】Spoj1487 Query on a tree III DFS序+主席树
- [Power Query]数据转置与反转行