I’ve been working on simplifying my performance tuning approach lately. One effective way to improve query performance is eliminating key lookups. A key lookup happens when SQL Server needs to get columns that aren’t in a non-clustered index, so it has to go to the clustered index or table for the missing data. This creates extra I/O operations, which usually means slower queries and more server load.
Investigating the I/O Performance
Since I’m focused on I/O, I start with SET STATISTICS IO ON to measure logical reads for each query, and I also turn on the actual execution plan to see if the optimizer suggests any indexes.
Let’s look at this I/O-heavy query:
SELECT DisplayName, UpVotes, DownVotes
FROM Users
WHERE DisplayName = 'Alex'The STATISTICS IO results show:
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 important number here is 320,359 logical reads. After checking, I see this table only has the clustered index on the primary key. The execution plan suggests creating this index:
CREATE NONCLUSTERED INDEX [IX_Users_DisplayName]
ON Users (DisplayName)Adding the Index
After adding the index, I run the query again. The results already show 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. The problem is key lookups - UpVotes and DownVotes are being fetched separately because they’re not in the index.
Including Columns
Since UpVotes and DownVotes aren’t in the WHERE clause, I’ll add them to the index’s INCLUDE clause:
CREATE NONCLUSTERED INDEX [IX_Users_DisplayName_INCLUDES]
ON Users (DisplayName)
INCLUDE (UpVotes, DownVotes)We run the query one more time and get these results:
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.By eliminating key lookups, we cut logical reads from 320,359 to just 32. Remember that creating several small indexes like this will slow down INSERT, UPDATE, and DELETE operations, just like any index does. Use them carefully and create covering indexes where they make sense. Monitoring and maintaining your indexes is just as important as creating new ones.