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!
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
