SQL Server indexes: interpreting Seek, Scan and query plans
A practical explanation of SQL Server indexes, seek versus scan, selectivity and the write-side cost of indexes.
Sample period date: Jan 22, 2016. These essays revisit earlier technologies from today's editorial perspective; they are not represented as posts originally published in those years.
Measure the symptoms before adding indexes
First measure execution time and rows accessed. Adding indexes without inspecting an execution plan is a guess, not a diagnosis. A scan may be perfectly reasonable for a nonselective query, while a seek can be much more efficient for a small target set. Compare logical reads and estimated versus actual row counts.
Seek is not always good and Scan is not always bad
An Index Seek targets key ranges while a Scan reads a broader set. If the query needs most of a table, scanning may be appropriate. Selectivity, statistics, distribution and required columns all matter. A predicate that does not align with the index's leading keys can limit its usefulness.
Indexes also have a write cost
Each additional index is another structure to maintain during inserts, updates and deletes. Building many indexes to speed up one report can slow everyday writes. Choose composite index order based on actual workload, and measure maintenance and storage overhead. Query gains must be weighed against write-side costs.
Run a reproducible experiment
Execute the query with the existing plan, then retest in a controlled environment with the proposed index. Keep parameters and dataset conditions as consistent as possible, and repeat with less selective parameter values. The goal is a reproducible performance improvement, not a flattering one-off screenshot.
Comments
0 real commentsNo real reader comments yet.
✎ Write a comment