One very common question that I see on the forums is on index seeks and index scans. A query is resulting in a table/clustered index scan, even though there’s an index on one or more of the columns been searched on.
One of the more common reasons that this happens is because the index in question is not covering, and SQL has determined that the cost of doing the lookups to fetch the extra columns is higher than the cost of scanning the entire table.
If an index does not cover a query, then bookmark lookups are required to get the additional columns, bookmark lookups are run one row at a time, and are seeks on the clustered index. Hence it’s clear that bookmark lookups on a large number of rows are exceedingly expensive and that is why SQL will switch to a clustered index/table scan when lookups are required on a significant percentage of the rows in the table.
So, what constitutes a significant percentage of the rows in the table? 50%? 20%? 10%?
The answer’s often surprising. It’s usually under 1% of the total rows in the table.
Here’s some test code to demonstrate that.
CREATE TABLE dbo.SeekOrScan( ID int IDENTITY(1,1) NOT NULL primary key, SomeNumber int, padding char(100) ) CREATE NONCLUSTERED INDEX idx_SeekOrScan ON dbo.SeekOrScan (SomeNumber)
insert into SeekOrScan (SomeNumber) select top 1000000 0 from master..spt_values a cross join master..spt_values b where a.name is null and b.name is null update SeekOrScan set SomeNumber = ID -- just so we've got sequential numbers for the between GO dbcc freeproccache select * from SeekOrScan where somenumber between 1 and 100000 -- 10% of table -- Clustered index scan dbcc freeproccache select * from SeekOrScan where somenumber between 1 and 50000 -- 5% of table -- Clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 10000 -- 1% -- Clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 5000 -- 0.5% of table -- clustered index scan dbcc freeproccache select * from [SeekOrScan] where somenumber between 1 and 3000 -- 0.3% of table -- nonclustered index seek
So somewhere around 0.4% of the table, the index seek with key lookup became more expensive than a table scan. The result is specific to this table and will differ for tables of different row size because the point at which a scan of the clustered index becomes cheaper than multiple key lookups depends on the number of pages in the table.