SQL Server Indexes: Make Reads Faster Without Making Writes Expensive

An index can make a slow SQL Server query dramatically faster. That does not mean every column should have one.

An index can help SQL Server find, order and return rows efficiently. SQL Server must also store and maintain that index whenever data changes. The objective is not to collect indexes. It is to design a small, purposeful set around the real workload.

Think of an index as an alternative route to the data

SELECT OrderId, CustomerId, OrderDate
FROM Sales.Orders
WHERE CustomerId = 1250;

Without a useful index beginning with CustomerId, SQL Server may need to examine a large part of the table. A scan is not automatically bad: it can be appropriate when the table is small, the query needs most rows, no available index provides a cheaper route, or repeated lookups would cost more than reading the table.

CREATE INDEX IX_Orders_CustomerId
ON Sales.Orders(CustomerId);

When the query needs a small number of rows from a large table, SQL Server can navigate the index to the relevant range instead of inspecting every row. This is an index seek. The useful question is not simply whether the plan contains a seek or scan, but how much data SQL Server read to produce the result.

Clustered and nonclustered indexes have different jobs

A clustered index defines the logical order of a table's data rows at its leaf level. Because the table can only be arranged this way once, it can have only one clustered index. A nonclustered index is a separate structure containing its key columns and a way to locate the corresponding row in the underlying table.

Nonclustered index key
        ↓
Row locator or clustered key
        ↓
Complete table row

A table without a clustered index is a heap. Heaps can be appropriate for particular workloads, especially staging or transient data, but they should be a deliberate decision rather than an accidental result of forgetting to design the table.

A primary key and clustered index are different decisions

CREATE TABLE Sales.Orders
(
    OrderId int NOT NULL,
    CustomerId int NOT NULL,
    OrderDate date NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId)
);

SQL Server commonly creates a clustered index when a primary key is declared unless told otherwise. A primary key answers which value uniquely identifies the row. A clustered index answers which key should organise the table and be carried by its nonclustered indexes. Sometimes the same column is right for both; sometimes it is not.

CONSTRAINT PK_Orders
    PRIMARY KEY NONCLUSTERED (OrderId)

Choose the clustered key carefully

• ✓Narrow: its value is also stored in nonclustered indexes.
• ✓Stable: changing it can relocate rows and update related index entries.
• ✓Unique: otherwise SQL Server may need an internal value to distinguish duplicates.
• ✓Useful for common ranges or ordering: particularly when rows are frequently retrieved together.

An ever-increasing key can make inserts predictable, but it can concentrate concurrent inserts on the final page. These characteristics are design guidance, not a substitute for testing against the real workload.

Column order controls how a composite index can be used

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON Sales.Orders(CustomerId, OrderDate);

This index naturally supports a query that uses equality on CustomerId and then a range on OrderDate. It may be much less useful for a query filtering only by OrderDate because that query does not constrain the leading key. A composite index is not a bag of columns; its order describes the route SQL Server can follow.

Keep predicates searchable

-- Harder to seek efficiently
WHERE YEAR(OrderDate) = 2026

-- Searchable range
WHERE OrderDate >= '2026-01-01'
  AND OrderDate <  '2027-01-01'

A well-designed index may not support an efficient seek when the query transforms its indexed column. A leading wildcard such as LastName LIKE '%Ahmed' similarly removes the known starting point. When reviewing a slow query, check both the available index and whether the predicate is written in a form that can use it.

A seek can still perform more work than expected

SQL Server may seek on one key and then test another condition as a residual predicate. Do not stop the investigation when you see the word Seek. Inspect seek predicates, residual predicates, estimated and actual rows, rows read versus rows returned, execution count and logical reads. A seek that reads 500,000 rows to return 10 still deserves attention.

Cover the query when measurement justifies it

CREATE INDEX IX_Orders_CustomerId
ON Sales.Orders(CustomerId)
INCLUDE (OrderDate, SalesPersonId);

A nonclustered index may find the correct rows but lack columns requested by the query, requiring a key lookup. A lookup can be efficient for a few rows and expensive when repeated thousands of times. Key columns help SQL Server search and order; included columns help return data without enlarging the searchable key.

Do not cover every query automatically. Wider indexes require more storage, memory and write maintenance. Cover high-value queries when evidence shows repeated lookups are a genuine problem.

Filtered indexes can target the important minority

CREATE INDEX IX_Jobs_Pending
ON Processing.Jobs(CreatedAt)
INCLUDE (JobId)
WHERE Status = 'Pending';

If most jobs are completed and the application repeatedly requests pending jobs, a filtered index can use less storage, require less maintenance and provide more focused statistics. Test filtered indexes carefully with parameterised queries because SQL Server must know that the compiled plan is safe for the possible parameter values.

Missing-index suggestions are evidence, not instructions

Execution plans and missing-index dynamic management views can suggest useful indexes. A suggestion is produced for a particular optimization and may not account for overlapping indexes, write cost, storage, maintenance, other queries, consolidation opportunities or a query that should be corrected first. Its estimated improvement is not a production promise.

• ✓Compare the suggestion with existing indexes.
• ✓Look for overlapping key and included columns.
• ✓Identify the queries expected to benefit.
• ✓Record the current plan and logical reads.
• ✓Test the proposed index safely and measure again.
• ✓Observe its effect on insert, update and delete activity.

Fragmentation is not an automatic emergency

Fragmentation can affect scans and range scans, but it does not follow that every fragmented index must be rebuilt every night. Consider index size, whether the workload scans it, page density, storage, maintenance duration, log growth, blocking and the measurable benefit. Point-seek workloads may gain little from removing fragmentation.

Unused indexes still have a cost

SELECT *
FROM sys.dm_db_index_usage_stats;

The usage statistics include user seeks, scans, lookups and updates. An index with no recorded reads and heavy update activity may be a removal candidate, but counters can reset after a restart and some important indexes serve infrequent business processes. Review a representative period and check dependencies before removal.

A practical index-review workflow

• ✓Capture the actual execution plan.
• ✓Record duration, CPU time and logical reads.
• ✓Identify the rows the query truly needs.
• ✓Check scans, seeks, lookups, sorts and residual predicates.
• ✓Review existing indexes before designing another.
• ✓Choose key columns from filtering, joins and ordering.
• ✓Add included columns only when avoiding lookups is worthwhile.
• ✓Consider a filtered index for a stable, selective subset.
• ✓Test with realistic data and measure again.
• ✓Check the effect on inserts, updates and deletes.
• ✓Keep, revise or remove the index based on evidence.

The lesson to remember

Code-review question

Which query will use this index, what work will it avoid, and what maintenance cost will the application pay?

That question leads to better indexing decisions than reacting automatically to every scan, lookup or missing-index warning.