TL;DR:
ScenarioIndex SeekIndex Scan
When it happensHigh selectivity (<5% of rows)Low selectivity or range queries
IO patternRandom IO, minimal pagesSequential IO, reads entire index
CPU usageLowHigher (for sorting/filtering)
ScalabilityScales well with table sizePerformance degrades with size
Performance impactFast, efficientSlower, more resource intensive

Index Seek vs Index Scan: The Performance Difference

When your SQL Server query suddenly slows down, the culprit is often hiding in the execution plan. You’ll see either an Index Seek or Index Scan operation, and understanding the difference can make or break your application’s performance.

The Core Difference

An Index Seek is surgical, SQL Server knows exactly where to find your data and jumps directly to those pages. Think of it like using a book’s index to find a specific topic on page 247.

An Index Scan reads through index pages sequentially until it finds what it needs. It’s like flipping through every page of a book to find all mentions of a topic.

To know more about indexes, check out my SQL Server Indexes: Clustered vs Non-Clustered, and Performance Tuning with Real-World Examples.

How SQL Server Makes the Choice

The cost-based optimizer decides between seek and scan based on:

  • Cardinality estimates (how many rows it expects to return)
  • Index selectivity (how unique the indexed values are)
  • Available statistics on your data distribution

Here’s the decision matrix:

OperationTriggerCost PatternBest For
Index SeekHighly selective predicatesLow I/O, predictableWHERE CustomerID = 12345
Index ScanLow selectivity or missing statsHigh I/O, scales with table sizeWHERE Status IN ('Active', 'Pending', 'Inactive')

Real-World Example: When Seeks Become Scans

Consider this query against a 10M row Orders table:

-- This uses an Index Seek when OrderDate is specific
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders 
WHERE OrderDate = '2024-01-15'
-- Returns ~2,000 rows, uses Index Seek

-- This causes an Index Scan when the range is too broad
SELECT OrderID, CustomerID, OrderDate, TotalAmount  
FROM Orders
WHERE OrderDate >= '2024-01-01'
-- Returns ~3M rows, switches to Index Scan

The tipping point typically occurs when SQL Server estimates it needs to return more than 20-30% of the table’s rows. At that threshold, scanning becomes more efficient than multiple seeks.

Common Mistakes That Force Scans

These patterns kill seek operations:

-- Functions on indexed columns
WHERE YEAR(OrderDate) = 2024  -- Causes scan
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'  -- Uses seek

-- Leading wildcards
WHERE CustomerName LIKE '%Smith%'  -- Causes scan
WHERE CustomerName LIKE 'Smith%'   -- Uses seek

-- Implicit conversions
WHERE CustomerID = '12345'  -- Scan if CustomerID is INT
WHERE CustomerID = 12345    -- Seek with proper type

Fixing Unexpected Scans

When you see scans where you expect seeks:

  1. Update statistics - Outdated stats cause poor cardinality estimates
  2. Add covering indexes - Include all SELECT columns to avoid key lookups
  3. Rewrite predicates - Remove functions from WHERE clauses
  4. Check data types - Ensure parameters match column types exactly

The key insight: SQL Server execution plans aren’t just academic, they directly impact your application’s response time. A query that seeks today might scan tomorrow as your data grows, so monitor those plans regularly.

Understanding index seek vs index scan behavior helps you write queries that scale and troubleshoot performance issues before they hit production.

To understand more about how to optimize your SQL queries with indexing and execution plans, check out my SQL Query Optimization in Action: Indexing and Execution Plan Deep Dive.

Frequently Asked Questions about Index Seek vs Scan

What is the difference between an index seek and an index scan in SQL Server?

An index seek is a targeted operation where SQL Server uses the index structure to quickly locate specific rows, resulting in minimal I/O and fast performance. An index scan, on the other hand, reads through all or most of the index pages sequentially, which is less efficient and typically happens when the query is less selective. Seeks are generally preferred for performance, especially on large tables.

When does SQL Server choose an index scan over an index seek?

SQL Server chooses an index scan when the query is expected to return a large percentage of rows, usually more than 5% of the table, or when the predicate is not sargable (search-argument-able). Scans also occur if statistics are outdated or if the index does not cover all required columns. The optimizer bases this decision on estimated row counts and index statistics.

How can I force SQL Server to use an index seek?

To encourage index seeks, write sargable queries (avoid functions on indexed columns), use correct data types, and ensure your indexes match the query predicates. You can also use query hints, such as WITH (INDEX(IndexName)), but this should be done sparingly. Example- SELECT OrderID, TotalAmount FROM CustomerOrders WITH (INDEX(IX_CustomerOrders_Status)) WHERE OrderStatus = 'Completed';

What are common mistakes that cause index scans?

Common mistakes include using functions on indexed columns, implicit data type conversions, and leading wildcards in LIKE queries. For example, WHERE YEAR(OrderDate) = 2024 or WHERE CustomerID = '12345' (if CustomerID is INT) will force scans. Always write predicates that allow SQL Server to use the index efficiently.

How do I monitor the ratio of index seeks to scans in my database?

You can monitor seek and scan activity using the sys.dm_db_index_usage_stats DMV. This view provides statistics on how often each index is used for seeks, scans, and lookups. Example query- SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName, s.user_seeks, s.user_scans FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE s.database_id = DB_ID();

What is a sargable predicate and why does it matter?

A sargable predicate is a query condition that allows SQL Server to use an index efficiently, typically in the form of Column = Value or Column BETWEEN Value1 AND Value2. Non-sargable predicates, such as those using functions or calculations on columns, prevent index seeks and force scans. Always write queries with sargable predicates for optimal performance.

How do filtered indexes help with index seeks?

Filtered indexes are non-clustered indexes that include only rows meeting a specific condition, defined by a WHERE clause. They are smaller and more efficient, making index seeks more likely for queries that match the filter. Example- CREATE NONCLUSTERED INDEX IX_CustomerOrders_Pending ON CustomerOrders (OrderDate, CustomerID) WHERE OrderStatus = 'Pending';

Why do outdated statistics cause index scans?

Outdated statistics can lead SQL Server to make poor cardinality estimates, causing the optimizer to choose a scan when a seek would be more efficient. Regularly updating statistics ensures the optimizer has accurate information about data distribution. Use the following command to update statistics- UPDATE STATISTICS CustomerOrders;

How can I reduce index scans in my queries?

To reduce index scans, ensure your queries use sargable predicates, keep statistics up to date, and design indexes that match your most common query patterns. Avoid unnecessary functions and implicit conversions in WHERE clauses. Also, consider filtered or covering indexes for frequent queries.

What is the real-world performance impact of index seeks vs scans?

Index seeks are much faster and more scalable than scans, especially on large tables. For example, a seek might result in 24 logical reads and 31ms CPU time, while a scan could require thousands of reads and over a second of CPU time. Always aim for seeks in performance-critical queries.
See other sql posts