When it comes to query performance tuning in SQL Server, key lookups are often the silent culprits that slow things down. This seemingly small operation can become expensive when repeated over thousands—or millions—of rows. In this article, we will break down what a key lookup is, why it happens, and how you can eliminate it with smarter indexing strategies. By addressing key lookups, you can significantly reduce I/O and boost query speed. So grab your execution plan and let’s uncover the hidden costs!
Figure-1: Key lookup
Key Lookup
A Key Lookup in SQL Server happens when a query uses a nonclustered index to find matching rows, but the index doesn’t include all the columns needed to satisfy the query. So, SQL Server has to go back to the clustered index to fetch the remaining column values.
Why Key Lookup occurs
Let's work on Users table of StackOverflow Database. Create an index on DisplayName field. SQL Server maintain separate table for every indexes. If you run sp_BlitzIndex query on Users table, it will show two separate size of two different indexes. The clustered index (PK_Users_Id) is bigger as it holds the full dataset. On the other hand, IX_Users only stores id and DisplayName.
EXEC dbo.sp_BlitzIndex @DatabaseName='StackOverflow', @SchemaName='dbo', @TableName='Users';
Code Snippet-1: Check with sp_BlitzIndex
Figure-2: Index size
If you run below query on nonclustered index DisplayName, you will get a execution plan similar to Figure-3.
SELECT TOP (5) [Id]
,[AboutMe]
,[CreationDate]
,[DisplayName]
,[Location]
FROM [StackOverflow].[dbo].[Users]
WHERE [DisplayName] = 'Mehedi'
Code Snippet-2: Select using Nonclustered Index
Figure-3: Key lookup execution plan
The nonclustered index DisplayName has only two fields in its index table namely id and DisplayName. For additional columns like AboutMe, CreationDate, and Location, it needs to perform Nested Loops (Inner Join) with Clustered index (Figure-4).
Figure-4: Nonclustered index storage
If you hover mouse on Index Seek and Key Lookup Operator of execution plan (Figure-3), it will show the tool tip menu like (Figure-5). The Index Seek operator only provides id & DisplayName and Key Lookup operator returns AboutMe, CreationDate, and Location.
Figure-5: Tool tip of Index Seek and Key Lookup Operator
Why it matters
- It is okay for small datasets or one-time lookup.
- But if it happens for every row in a large result set, it can slow down your query a lot.
- It can cause high logical reads and I/O, making your execution plan less efficient.
How to Remove Key Lookup
Covering index will be game changer here. A covering index is a non-clustered index where all columns of SELECT statements are either part of the index keys or are specified in the included column clause of the index creation statement. As necessary columns are part of the index, so no extra IO is required additional columns. Thus, it boost up the performance. Let's drop the existing index on DisplayName and create a new covering index.
CREATE NONCLUSTERED INDEX ix_Users_AboutMe_CreationDate_Location
ON [StackOverflow].[dbo].[Users] (DisplayName)
INCLUDE (AboutMe,CreationDate,Location)
Code Snippet-3: Create Covering Index
Now, run the Code Snippet-2 again and check the execution plan.
Figure-6: Execution plan discards Key Lookup
Voila! The issue is resolved. The covering index removes the Key Lookup.
Final Words
Key lookup can silently slow down your queries by fetching extra data row-by-row. While they are not always bad, too many of them in large workloads can hurt performance. With covering indexes—you can avoid them and give your queries a performance boost. A little tuning can go a long way!
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.