7

I have the following tables and content

create table t(i int primary key, j int, k char(6000))
create  index ix on t(j)

insert into t values(1,1,1)
insert into t values(2,1,1)
insert into t values(3,1,1) 
insert into t values(4,1,1)
insert into t values(5,1,1)
insert into t values(6,1,1)
insert into t values(7,1,1)
insert into t values(8,2,2) 
insert into t values(9,2,2)

select * from t where j = 1
select * from t where j = 2

I'm really confused on why the first SELECT uses only a Clustered Index Scan (Clustered) while the second one uses a Index Seek (NonClustered) and a Key Lookup (Clustered).

Execution Plans

Can someone clear this up for me?

  • Your primary key is on i. – Jason B. Jul 13 '16 at 20:47
  • I know this has been a while, but I'm getting this situation when creating an index proposed by Brent Ozar's SP_BlitzIndex and also from Azure portal recommendations. I have missing index warning and PK scans. So I create an index, it get's a lot of seeks but also generates PK lookups is this a bad thing? – Pimenta Jul 6 at 17:14
12

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.

Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain percentage of the table (AKA The tipping point) the optimizer falls back to a Clustered Index scan, as that is the more efficient plan.

See Kimberly Tripp's blog post about The Tipping Point

| improve this answer | |
  • Your are right Thank you for your help Chad. – Diogo Jul 13 '16 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.