Adding two columns to an INCLUDE clause took this query from 320,359 logical reads to 32. The index that got it most of the way there was the one the execution plan suggested, and that index still left 76,773 reads on the table, which is the part I want to show.
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, since UpVotes and DownVotes are being fetched separately because they’re not part of the index.
This is where I stopped the first few times I did this. The plan suggested an index, I created it, the reads dropped by three quarters, and that looked like the job. The missing index suggestion only covers the WHERE clause, so it gets you the seek and leaves the lookup in place, and the plan won’t suggest a second index to fix what the first one didn’t.
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. The scan count went from 7 to 1 as well, which is the parallel scan collapsing into a single seek.
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. I’d also rather widen an existing index than add another one, since two indexes on DisplayName where one includes two extra columns is a duplicate in everything but name, and I’ve left both in place before and not noticed for months.