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…