Index tuning: clustered, nonclustered and included columns

-- indexes · Ronald de Groot

The right index is the difference between milliseconds and minutes. But "just throw an index at it" is not a strategy: every index costs write performance, storage and maintenance. In this article, the trade-offs I make in practice.

The clustered index: choose it deliberately

The clustered index is the table: the leaf pages contain all the data, physically sorted by the clustering key. You only get one, so choose deliberately. A good clustering key is:

Narrow — the key is repeated in every nonclustered index on the table.
Unique — otherwise SQL Server silently appends a 4-byte uniquifier.
Ever-increasing — new rows go at the end, no page splits in the middle of the table.
Static — changing the clustering key moves the whole row and touches every nonclustered index.

An INT IDENTITY or increasing BIGINT usually does fine. A random UNIQUEIDENTIFIER (via NEWID()) is the classic pitfall: page splits and fragmentation guaranteed.

Nonclustered: column order is everything

A nonclustered index on (A, B) is sorted by A, and only within equal A values by B. Think of a phone book: sorted by last name, then first name. Searching by first name alone? Then you have to go through the whole book.

CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerID, OrderDate);

-- Index Seek: leading column in the predicate
WHERE CustomerID = 42
WHERE CustomerID = 42 AND OrderDate >= '2026-01-01'

-- No seek possible: leading column missing
WHERE OrderDate >= '2026-01-01'

Rules of thumb for the order of the key columns:

Columns you filter with = first, then the range columns (>, <, BETWEEN).
With multiple equality columns: the most selective combination up front is nice, but reusability for other queries often weighs heavier.
An index on (A, B) makes a separate index on (A) redundant.

INCLUDE: the covering index

Columns in the INCLUDE list only live on the leaf pages of the index — they don't affect the sort order, but they do prevent the Key Lookup back to the table. If you see a Key Lookup with many rows in an execution plan, this is almost always the fix:

-- Query:
SELECT OrderDate, TotalAmount
FROM   dbo.Orders
WHERE  CustomerID = 42;

-- Covering index: seek on CustomerID, no lookup needed anymore
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);

Why not just put everything in the key? Because key columns live at every level of the index tree and count towards the 900-byte/32-column key limit. INCLUDE columns only live in the leaf: cheaper. But be frugal — an index that includes ten columns "just in case" is half a copy of the table that has to be maintained on every update.

Cleaning up: which indexes do nothing?

Indexes that are never read but are still maintained only cost you. This is how you find them:

SELECT o.name AS [table], i.name AS [index],
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM   sys.dm_db_index_usage_stats s
JOIN   sys.indexes i  ON i.object_id = s.object_id AND i.index_id = s.index_id
JOIN   sys.objects o  ON o.object_id = s.object_id
WHERE  s.database_id = DB_ID()
  AND  o.is_ms_shipped = 0
  AND  s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;

Careful: this DMV is cleared on restart. Only judge after a full business cycle — that one index may exist for the month-end or year-end process.

Missing index suggestions: a starting point, not gospel

The missing index DMVs (and the green hint in SSMS) are useful but naive: they look per query, suggest column orders that aren't always right, and happily stack ten nearly identical indexes on top of each other. My approach: collect the suggestions, group them per table, and then design one or two indexes yourself that serve multiple queries at once.

That's how you keep an index landscape that carries your queries instead of strangling your writes: a deliberate clustered index, a handful of well-designed nonclustered indexes with the right column order, INCLUDE where it removes lookup pain — and periodically clean up what isn't used.

In doubt about the index design of a busy table? I'm happy to take a look.