Recently I’ve been looking into simplifying my performance tuning. One of the more straightforward ways to improve a query is to get rid of key lookups. A key lookup happens when SQL Server finds rows through a nonclustered index but needs columns that aren’t in that index. It goes back to the clustered index for each row to get the rest. Microsoft’s operator reference calls it “a bookmark lookup on a table with a clustered index,” and the same thing on a heap shows up as a RID Lookup. Either way, it’s extra I/O for every row the index finds.
Investigating the I/O Performance
Since I want to focus on I/O, I start by running SET STATISTICS IO ON to measure logical reads for each query, and I also turn on the actual execution plan to check for index suggestions from the optimizer.
This is the query, against a Users table:
SELECT DisplayName, UpVotes, DownVotes
FROM Users
WHERE DisplayName = 'Alex'The STATISTICS IO output:
Table 'Users'. Scan count 7, logical reads 320359, physical reads 0, page server reads 0, read-ahead reads 316400, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.The number that matters here is 320,359 logical reads. The table has no indexes except the clustered index on the primary key, and the execution plan suggests creating this one:
CREATE NONCLUSTERED INDEX [IX_Users_DisplayName]
ON Users (DisplayName)Adding the Index
After adding the index, I ran the query again. The results already show an improvement:
Table 'Users'. Scan count 7, logical reads 76773, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.That’s a big drop in logical reads, but 76,773 still seems high for such a simple query. What’s left is the key lookups. UpVotes and DownVotes aren’t part of the new index, so SQL Server goes back to the clustered index to get them for every row the index finds.
The suggested index only had DisplayName in it. Missing index suggestions can include columns, and Microsoft’s guide to missing index suggestions lists included columns among the things they suggest. The same guide is clear that they’re estimates made before the query runs, and that they “aren’t prescriptions to create indexes exactly as suggested.” So it’s worth checking whether an index built from a suggestion actually covers the query.
Including Columns
UpVotes and DownVotes aren’t part of the WHERE clause, so they don’t need to be key columns. I added them to the INCLUDE clause of the index instead:
CREATE NONCLUSTERED INDEX [IX_Users_DisplayName_INCLUDES]
ON Users (DisplayName)
INCLUDE (UpVotes, DownVotes)
Then I ran the query one last time:
Table 'Users'. Scan count 1, logical reads 32, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.With the key lookups gone, logical reads went from 320,359 to 32, and the scan count went from 7 to 1.
Keep in mind that creating several small indexes like this will slow down INSERT, UPDATE, and DELETE operations, just like any index does, so use them sparingly and create covering indexes where they make sense. Microsoft’s guide makes a related point. Before creating an index from a suggestion, look at the indexes already on the table and combine them where you can, instead of adding one that overlaps. Here, that means IX_Users_DisplayName is redundant once IX_Users_DisplayName_INCLUDES exists, since they have the same key. Monitoring and maintaining your indexes is just as important as creating new ones.