Indexes are among the most effective SQL Server tuning tools, which also makes them easy to misuse. An index is not free acceleration. It occupies storage and buffer pool space, must be maintained by every relevant insert, update, and delete, and gives the optimizer another access path to evaluate. Good indexing is a workload design exercise, not a contest to eliminate every table scan.
Adding every missing-index recommendation
The missing-index dynamic management views describe indexes that could have helped individual optimization events. They do not understand the complete workload, account fully for write cost, or consolidate similar suggestions. After a busy week, they may propose several variations of the same index with slightly different key and included columns.
Treat those rows as leads. Group them by table, compare them with current definitions, and inspect the queries that generated the requests. One carefully designed index may cover several important statements. Conversely, a high estimated improvement attached to a query that ran once may matter less than a modest improvement to a transaction executed thousands of times during the business day.
Creating overlapping indexes
Consider indexes beginning with CustomerId, CustomerId, OrderDate, and CustomerId, OrderDate, Status. They are not automatically duplicates, but they deserve a joint review. Their include lists, uniqueness, filters, sort direction, query usage, and size determine whether each has a distinct purpose.
Overlapping indexes consume memory and lengthen write operations. They also make maintenance take longer. Before combining or removing one, capture a representative period of seeks, scans, lookups, and updates. Remember that SQL Server restarts and database detach operations reset much of this usage evidence.
Putting key columns in an unhelpful order
The order of index keys determines how efficiently SQL Server can seek and preserve ordering. The most selective column is not automatically the correct leading key. The leading columns should match common equality predicates, range predicates, joins, and ordering requirements across the workload.
An index on OrderDate, CustomerId can serve a date-range report well but may not support a request for all orders from one customer unless a useful date condition is also supplied. Reversing the keys changes that tradeoff. Design from actual query predicates and plans, then validate with realistic parameter values.
Using INCLUDE as unlimited storage
Included columns can eliminate lookups without affecting key order, but very wide covering indexes carry a price. More pages must be read into memory. Updates to included values touch the index. Rebuilds, backups, integrity operations, and replicas move more data. Large character columns are especially easy to add for convenience and expensive to retain.
A lookup is not inherently bad. A selective query returning a handful of rows may use lookups efficiently. The same plan becomes troublesome when an estimate is wrong and thousands of rows are retrieved. Fixing the estimate or query pattern can be better than covering every output column.
Choosing a poor clustered key
The clustered key is carried in every nonclustered index as the row locator. A wide clustered key silently widens the rest of the indexing structure. A frequently changing key can move rows, while a nonsequential key can distribute inserts across many pages. None of these properties makes a design universally wrong, but the cost multiplies on a busy, heavily indexed table.
- Prefer a clustered key that is narrow, stable, unique, and appropriate for common access patterns.
- If the chosen key is not unique, understand that SQL Server may add an internal uniquifier.
- Separate the logical primary key decision from the physical clustering decision when the workload calls for it.
Applying fill factor and fragmentation rules everywhere
A low fill factor reserves space on index pages, but it also increases page count immediately. That means more reads, a larger buffer pool footprint, and more maintenance. It can help an index that experiences measured page splits in the middle of the key range; it is not a standard remedy for all indexes.
Likewise, rebuilding every index above a fixed fragmentation percentage ignores index size and workload. A tiny index can show high percentage fragmentation while fitting in very few pages. A large read-mostly index may perform well without constant rebuilding. Base thresholds on page count, observed scan behavior, maintenance windows, log generation, and high-availability impact.
Ignoring filtered and purpose-built options
When queries repeatedly target a small stable subset, such as open work items or rows with a nullable completion date, a filtered index can be smaller and more precise than a full-table index. Computed columns can make an otherwise non-sargable business expression indexable when defined correctly. Columnstore can serve analytic workloads, but it should not be attached casually to an operational table without testing write and maintenance behavior.
Measure the whole transaction cost
Before changing an index, record query duration, logical reads, CPU, row counts, plan shape, and execution frequency. For a write-heavy table, also measure log generation, lock duration, and batch throughput. Test creation and removal scripts, including rollback, in a representative environment.
The right index portfolio is the smallest set that reliably supports the important workload, not the largest set the server will accept.
Revisit indexing after application releases and major data growth. Queries change, distributions shift, and yesterday's useful index can become tomorrow's write tax. Deliberate review is safer than accumulating indexes indefinitely or deleting them from a single snapshot.