The SQL Server Index Tuning Checklist (A DBA's Field Guide)

Most indexing problems are over-indexing, not under-indexing. Write-heavy systems with 18 indexes per table are common; almost none of them are pulling their weight. Use this checklist in order — don't skip ahead to "add missing indexes" without doing the cleanup first.

Step 1: Inventory what you have

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_unique, i.is_primary_key,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
       ON i.object_id = ius.object_id AND i.index_id = ius.index_id
       AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
  AND i.type > 0
ORDER BY (ISNULL(ius.user_seeks,0) + ISNULL(ius.user_scans,0) + ISNULL(ius.user_lookups,0)) DESC;

This single query gives you the foundation: every index, how often it was used, and how often it was updated. Read it before you change anything.

Step 2: Drop unused indexes

Indexes with zero user_seeks/scans/lookups but high user_updates are pure overhead. Caveats:

• Stats are reset on service restart — only drop indexes that have a long uptime window or have been confirmed unused across multiple uptimes
• Constraints (UNIQUE, PK) and FK indexes have purposes the usage stats don't reflect
• If you use Query Store, you can cross-check against historical plans before dropping

Step 3: Consolidate duplicate & overlapping indexes

An index on (A, B) and another on (A, B, C) are usually redundant — the second covers the first. Find them:

WITH idx AS (
  SELECT i.object_id, i.index_id, i.name,
    (SELECT STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal)
     FROM sys.index_columns ic
     JOIN sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id
     WHERE ic.object_id=i.object_id AND ic.index_id=i.index_id AND ic.key_ordinal>0) AS keys
  FROM sys.indexes i
  WHERE i.type IN (1,2) AND OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
)
SELECT OBJECT_NAME(a.object_id) AS table_name,
       a.name AS index_a, a.keys AS keys_a,
       b.name AS index_b, b.keys AS keys_b
FROM idx a
JOIN idx b ON a.object_id=b.object_id AND a.index_id<b.index_id
WHERE a.keys = b.keys
   OR LEFT(b.keys, LEN(a.keys)) = a.keys
   OR LEFT(a.keys, LEN(b.keys)) = b.keys;

Step 4: Find heaps that should be clustered

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       SUM(p.rows) AS row_count
FROM sys.objects o
JOIN sys.indexes i ON o.object_id=i.object_id AND i.index_id=0
JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
WHERE o.type='U'
GROUP BY o.object_id, o.name
HAVING SUM(p.rows) > 100000
ORDER BY row_count DESC;

Large heaps are nearly always a mistake. Pick a clustered key — ideally narrow, static, unique, and increasing — and add it.

Step 5: Audit missing-index suggestions — don't just apply them

SQL Server's missing-index suggestions are hints, not answers. Each one needs three checks:

• Is there an existing index that would cover this with one added column? (Modify, don't add)
• What's the write cost on this table? Adding an index to a write-heavy table for a query that runs nightly is bad math
• Does the suggested key order make sense, or did the optimizer suggest a poor order because the query is poorly written?

SELECT TOP 20
    mig.index_group_handle,
    migs.unique_compiles,
    migs.user_seeks + migs.user_scans AS demand,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    OBJECT_NAME(mid.object_id) AS table_name,
    mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle=migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle=mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Step 6: Set fill factor by usage pattern, not by default

SQL Server's default fill factor of 0 (= 100%) is correct for read-heavy, append-only patterns. It's wrong for hot OLTP indexes with random inserts. Rules of thumb:

• Sequential keys (IDENTITY, sequence, ever-increasing date) — 100% (default)
• Random keys (GUID, hash) — 80–90%
• Highly volatile UPDATE patterns — 80–90%
• Static history tables — 100%

Step 7: Foreign key indexes

Every foreign key column should have an index. Missing FK indexes show up as deadlocks during cascade deletes and as scans on the child table during parent updates.

SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
       OBJECT_NAME(fk.parent_object_id) AS table_name,
       fk.name AS fk_name,
       c.name AS column_name
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id=fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id=c.object_id AND fkc.parent_column_id=c.column_id
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.index_columns ic
    JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
    WHERE ic.object_id=fkc.parent_object_id
      AND ic.column_id=fkc.parent_column_id
      AND ic.key_ordinal=1
);

Step 8: Fragmentation is rarely the problem you think it is

Modern SSD storage makes physical index fragmentation far less important than it was in the spinning-disk era. Logical fragmentation matters for range scans on large indexes; otherwise reorganize quarterly, rebuild only when fragmentation crosses 30%, and stop wasting maintenance windows.

Step 9: Measure

Every change — every drop, every add, every modification — should be followed by a measurement. Capture top-query CPU and duration before, change, wait a workday, measure again. Without before/after evidence, you're guessing.

---

Need a second opinion on your indexing strategy? A SQL Server health check includes a full index audit — unused, duplicate, missing, and miss-sized — prioritized by impact. Book below.