Recently I’ve been looking into simplifying my performance tuning efforts. One of the more straightforward methods for improving the performance of a query is to eliminate key lookups. A key lookup occurs when SQL Server retrieves additional columns not included in a non-clustered index, requiring it to access the clustered index or the table itself for the missing data. This means SQL Server needs to perform extra I/O operations, which typically results in slower queries or higher server load.
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 enable the actual execution plan to check for any index suggestions from the optimizer.
Let’s look at the following I/O-intensive query:
SELECT DisplayName, UpVotes, DownVotes
FROM Users
WHERE DisplayName = 'Alex'The results from STATISTICS IO 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 key figure here is 320,359 logical reads. After reviewing, I notice there are no indexes on this table except 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 rerun the query. The results already show some 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.While that’s a major reduction in logical reads, 76,773 still seems high for such a simple query. The remaining issue stems from key lookups—UpVotes and DownVotes are being fetched separately because they’re not part of the index.
Including Columns
Since UpVotes and DownVotes aren’t part of the WHERE clause, I’ll add them to the INCLUDE clause of the index:
CREATE NONCLUSTERED INDEX [IX_Users_DisplayName_INCLUDES]
ON Users (DisplayName)
INCLUDE (UpVotes, DownVotes)We run the query one last time and here are the 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 the key lookups, we’ve reduced logical reads from 320,359 to just 32—turning a novel into a postcard. Keep in mind that creating several small indexes such this will slow down INSERT, UPDATE, and DELETE operations, just like any index, so be sure to use them sparingly, and create covering indexes where appropriate. Monitoring and maintaining your indexes is just as important as creating new ones.