Which Columns Are Looked Up?

A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let’s take a quick look.

Which Columns Are Looked Up?

Let’s take an example query:

SELECT p.NAME,
       AVG(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
    JOIN Production.Product AS p
        ON sod.ProductID = p.ProductID
WHERE sod.ProductID = 776
GROUP BY sod.CarrierTrackingNumber,
         p.NAME
HAVING MAX(sod.OrderQty) > 1
ORDER BY MIN(sod.LineTotal);

There is a nonclustered index on the ProductID column. Depending on the value, it’s going to reduce the rows returned extremely well. So, the optimizer is likely to pick that index. Here’s the index definition:

CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID
ON Sales.SalesOrderDetail (ProductID ASC)

In the query we’re referencing other columns from the SalesOrderDetail table, in this small query, an easily identifiable list. However, with bigger queries, that list gets harder. The execution plan for our SELECT statement looks like this:

I’ve gone ahead an blown up the relevant part of the plan. So, for each of the 228 rows retrieved from the nonclustered index, we have to go to the clustered index to retrieve the additional columns we need. That’s at least two extra reads per row, not to mention the overhead of performing the join. This is why, where we can, when it makes sense, we try to eliminate lookups.

Now, I promised an easy answer to “which columns are looked up”. The key, as it were, is in the lookup operator, in this case, a Key Lookup (see what I did there). It’s a Key Lookup because it has a clustered index. Otherwise, it’d be an RID Lookup (a heap lookup). Let’s go to the properties and look at the output from the Key Lookup operator:

Ta-Da!

Now you know which columns you need if you were going to make a covering index, either by adding columns to the key, or to the INCLUDE clause. Worth noting, it’s not the same list as the columns in the query. I’ll leave it to you to figure out why.

By the way, there’s an ellipsis you can click that will open a window so you can copy all column names, not just look at them in a list like the figure above.

NOTE: Not all lookups MUST be eliminated. However, all those extra reads, makes it a good candidate for query tuning.

Conclusion

Like I said, an easy, quick, way to understand which columns are looked up. I bring it up for two reasons. One, as with my example, the columns you think you need aren’t always the ones you actually need. Two, while you can usually spot the list of columns you need to consider, sometimes, it’s a long list. Why not have a quick & easy way to it.