Index Scans

Gail Shaw proves that sometimes, an index scan isn’t a full index scan:

A scan of the entire index is over 1600 pages. This query read three. It’s a scan, but it’s a scan which stopped after reading one page of the leaf (the other two are likely the root and intermediate pages, used to locate the first page in the leaf).

The scan read one row and then stopped, because that’s all that was needed. It did that, because there was a 1 row row-goal added to the query. For more details on row goals, see Paul White’s article on the subject.

Read the whole thing.

Corollary:  sometimes a seek isn’t really a seek; sometimes it’s a scan even when the icon says “seek” because that’s more efficient.

Related Posts

Query Store Indexes

Arthur Daniels shows what you can learn from the indexes on Query Store tables: It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. […]

Read More

Gotchas When Indexing Partitioned Tables

Andrew Pruski gives us a couple of considerations when creating indexes on partitioned tables in SQL Server: Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not. But also […]

Read More

1 Comment

  • Gail on 2016-01-27

    No, a seek is always a seek. But ‘seek’ doesn’t mean efficient and doesn’t mean ‘one page’. It means that the b-tree was used to locate a value or start of a range. That’s all that ‘seek’ means

Comments are closed


January 2016
« Dec Feb »