SQL Server Key Lookup Operator – Part 3

Dear Friends,

I hope you liked my last two blog posts Part1 and Part2 on sql server key lookup operator. Starting from where we stopped yesterday, let us modify StateProvinceID to 79 in our query that was used yesterday and observe if SQL Server optimizer uses same plan operators.

USE [AdventureWorks2012]

SELECT Address.AddressID, Address.AddressLine1, Address.StateProvinceID, Address.PostalCode
FROM Person.Address
WHERE Address.StateProvinceID = 79




SQL Server optimizer decided to use index scan this time and has returned 2636 rows. In this case, optimizer uses the value of the query’s Address.StateProvinceID to estimate the cardinality of the predicate and tries to find out most efficient plan. Here query optimizer estimated more rows than when Address.StateProvinceID equals to 10 and decided to scan rather than key lookups.

A key lookup requires random I/O which can be very expensive and it depends on the number of records that decides which operator is going to be a good choice. In tomorrow’s post, we are going to see how we remove SQL Server Key Lookup operator from the query plans.

Happy learning!



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.