We Had 47 Indexes on One Table. None of Them Were the Right One.

The investigation started with a complaint: writes to the Orders table were taking 800ms average. Reads were fine. Inserts and updates were slow. When we opened the table definition and counted the indexes, we counted again to be sure. Forty-seven non-clustered indexes on one table.

The Alert

Application performance monitoring flagged order placement latency climbing from 120ms to 800ms over three months. The gradient was gradual — no single deployment caused it. Query execution times for the INSERT and UPDATE operations on the Orders table were the source. Every index on the table must be maintained on every write.

First Hypothesis: Write Contention

With 800ms write latency, lock contention was the first assumption. We checked blocking chains and wait statistics.

SELECT TOP 10
  wait_type,
  waiting_tasks_count,
  wait_time_ms / 1000.0        AS wait_time_sec,
  max_wait_time_ms / 1000.0    AS max_wait_sec,
  signal_wait_time_ms / 1000.0 AS signal_wait_sec
FROM   sys.dm_os_wait_stats
WHERE  wait_type NOT IN ('SLEEP_TASK','BROKER_TO_FLUSH','HADR_WORK_QUEUE','CLR_AUTO_EVENT')
ORDER BY wait_time_ms DESC;

Dominant wait type: PAGEIOLATCH_EX — page I/O latch waits. Not lock contention. The writes were waiting on I/O, not on other sessions. Each INSERT to the Orders table was generating I/O across 47 index structures.

The Discovery: The Index Audit

SELECT
  i.name                           AS index_name,
  i.type_desc,
  ius.user_seeks,
  ius.user_scans,
  ius.user_lookups,
  ius.user_updates,
  ius.last_user_seek,
  ius.last_user_scan,
  (SELECT STRING_AGG(c.name, ', ') 
   FROM   sys.index_columns ic 
   JOIN   sys.columns       c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
   WHERE  ic.object_id = i.object_id AND ic.index_id = i.index_id 
     AND  ic.is_included_column = 0)  AS key_columns
FROM       sys.indexes             i
LEFT JOIN  sys.dm_db_index_usage_stats ius
        ON ius.object_id = i.object_id AND ius.index_id = i.index_id
           AND ius.database_id = DB_ID()
WHERE  i.object_id = OBJECT_ID('dbo.Orders')
  AND  i.type > 0  -- non-clustered only
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups);

Result: 31 of the 47 indexes had zero reads since the last SQL Server restart (14 days ago). Zero seeks. Zero scans. Zero lookups. They were being written on every INSERT and UPDATE, maintained at I/O cost, and never consulted by any query. They had been created by following missing index DMV suggestions over four years without ever auditing what already existed.

Incident Timeline

Root Cause

Four years of following missing index DMV suggestions without cross-checking existing coverage produced 47 indexes on one table. The DMV shows what indexes would have helped individual queries. It does not show redundancy with existing indexes, total write overhead, or whether the query needing the index is even still running. Each index adds maintenance overhead proportional to write volume. 47 indexes on a high-write table turned every INSERT into a 47-way I/O operation.

The Fix

SELECT
  'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' 
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';' AS drop_statement,
  ius.user_seeks + ius.user_scans + ius.user_lookups               AS total_reads,
  ius.user_updates                                                   AS total_writes
FROM       sys.indexes             i
JOIN       sys.tables              t  ON t.object_id = i.object_id
JOIN       sys.schemas             s  ON s.schema_id = t.schema_id
LEFT JOIN  sys.dm_db_index_usage_stats ius
        ON ius.object_id = i.object_id AND ius.index_id = i.index_id
           AND ius.database_id = DB_ID()
WHERE  t.object_id = OBJECT_ID('dbo.Orders')
  AND  i.type > 0
  AND  (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0
ORDER BY ius.user_updates DESC;

Prevention

A quarterly index review runs the zero-read query and flags any index with more than 10,000 writes and zero reads for removal. Missing index DMV suggestions are reviewed against existing indexes before any new index is created. The rule: before adding an index, prove that no existing index already covers the access pattern.