Last three days we have been discussing on SQL Server Key Lookup operators and if you are reading this post ahead of them, I recommend you to read Part1 , Part2 , and Part3 first to have a better understanding.
As mentioned in previous post, presence of a Key Lookup operator is an indication that query optimizer might benefit from presence of a covering/included index. Today, we will revisit our query then create a covering index to include required columns and check if there are any changes to the execution plan.
USE [AdventureWorks2012] SELECT Address.AddressID, Address.AddressLine1, Address.StateProvinceID, Address.PostalCode FROM Person.Address WHERE Address.StateProvinceID = 10
Looking at key lookup ToolTip, it is clear that to retrieve AddressLine1 and PostalCode column SQL Server optimizer has to use index key PK_Address_AddressID causing key lookup operation. Let us now include these columns in IX_Address_StateProvinceID used in seek operation (see screenshot below from index seek nonclustered).
USE [AdventureWorks2012] --Step 1 (run first) CREATE NONCLUSTERED INDEX IX_Address_AddrPostalCode ON Person.Address (StateProvinceID) INCLUDE (AddressLine1, PostalCode) --Step 2 (run only after Step 1) SELECT Address.AddressID, Address.AddressLine1, Address.StateProvinceID, Address.PostalCode FROM Person.Address WHERE Address.StateProvinceID = 10
Key lookup disappeared from the query plan and we have index seek nonclustered. SQL optimizer is able to find all the required columns in index key IX_Address_AddrPostalCode now and no additional lookup required here.
I hope you liked this short series on key lookup but we have so much to explore on One operator a day series, stay tuned.