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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
So, what constitutes a significant percentage of the rows in the table? 50%? 20%? 10%?<\/p>\n
The answer’s often surprising. It’s usually under 1% of the total rows in the table.<\/p>\n
Here’s some test code to demonstrate that.<\/p>\n
Setup code:<\/p>\n
CREATE TABLE dbo.SeekOrScan(\r\n ID int IDENTITY(1,1) NOT NULL primary key,\r\n SomeNumber int,\r\n padding char(100)\r\n)\r\n\r\nCREATE NONCLUSTERED INDEX idx_SeekOrScan ON dbo.SeekOrScan (SomeNumber)<\/pre>\nTest code:<\/p>\n
insert into SeekOrScan (SomeNumber)\r\nselect top 1000000 0\r\nfrom master..spt_values a cross join master..spt_values b\r\nwhere a.name is null and b.name is null\r\n\r\nupdate SeekOrScan set SomeNumber = ID -- just so we've got sequential numbers for the between\r\nGO\r\n\r\ndbcc freeproccache\r\n\r\nselect * from SeekOrScan\r\nwhere somenumber between 1 and 100000 -- 10% of table\r\n-- Clustered index scan\r\n\r\ndbcc freeproccache\r\n\r\nselect * from SeekOrScan\r\nwhere somenumber between 1 and 50000 -- 5% of table\r\n-- Clustered index scan\r\n\r\ndbcc freeproccache\r\n\r\nselect * from [SeekOrScan]\r\nwhere somenumber between 1 and 10000 -- 1%\r\n-- Clustered index scan\r\n\r\ndbcc freeproccache\r\n\r\nselect * from [SeekOrScan]\r\nwhere somenumber between 1 and 5000\u00a0 -- 0.5% of table\r\n-- clustered index scan\r\n\r\ndbcc freeproccache\r\n\r\nselect * from [SeekOrScan]\r\nwhere somenumber between 1 and 3000\u00a0 -- 0.3% of table\r\n-- nonclustered index seek<\/pre>\nSo 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.<\/p>\n","protected":false},"excerpt":{"rendered":"