Every new index promises faster reads, but too many indexes can make writes crawl. The next index can rescue a report while making the busiest transaction slower. The right question is which indexes earn the work required to maintain them.
Why Too Many Indexes Add Write Work
A nonclustered index is another structure SQL Server must keep consistent with the table. Inserting a row adds entries to relevant indexes. Deleting removes them. Updating an indexed key can remove one entry and insert another. Included columns also matter because their values live in leaf rows. This work consumes log space, CPU, and buffer pool pages.
The cost is easy to overlook when the index is created to fix one slow SELECT. That SELECT can run a few times a day while an order table receives thousands of changes. I start by naming the write path before adding an index. If the write path is the business-critical path, the read improvement needs a stronger case.
Recognize Overlap Before Counting
Raw index count is a warning, not a limit. Two indexes with the same leading key columns can overlap, especially when one includes the columns covered by the other. Key order and sort direction still matter. An index on CustomerID, OrderDate serves different seeks from an index on OrderDate, CustomerID. A filtered index also has different eligibility.
I compare definitions side by side before calling anything redundant. One index can support a foreign key check, another can enforce uniqueness, and a third can serve a narrow reporting predicate. Dropping one because its name resembles another is a fast way to turn a clean-looking inventory into a production complaint.
List the Indexes on a Table
Start with a specific table and include key columns, included columns, filters, size, and uniqueness. This catalog query gives the structural inventory. Change the two variables to target the table under review. It does not decide which index to remove. It makes the comparison possible.
Read the output with the workload in mind. An index with more included columns can be wider than its name suggests. A unique index can encode a business rule, even if no query currently seeks it. Keep primary keys and uniqueness constraints in a separate protected category during review.
DECLARE @object_id int = OBJECT_ID(N'dbo.Orders');
SELECT i.name, i.index_id, i.is_unique, i.is_primary_key,
i.has_filter, i.filter_definition,
STRING_AGG(CASE WHEN ic.is_included_column = 0
THEN c.name END, N', ') WITHIN GROUP
(ORDER BY ic.key_ordinal) AS key_columns
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = @object_id AND i.index_id > 0
GROUP BY i.name, i.index_id, i.is_unique, i.is_primary_key,
i.has_filter, i.filter_definition
ORDER BY i.index_id;Measure Use Alongside Cost
Index usage counters show seeks, scans, lookups, and updates since the counters last reset. They are a clue, not a lifetime history. Server restart, database detach, or index recreation can reset the evidence. A monthly process can appear unused after a recent restart. Capture the observation period and job calendar before drawing a conclusion.
The update counter records operations against an index, not the exact number of affected rows. A single update statement can change many rows. I compare usage with execution plans, Query Store history, and known application paths. An index that supports a rare but essential close process can still earn its place.
SELECT i.name, i.index_id, s.user_seeks, s.user_scans,
s.user_lookups, s.user_updates,
os.cntr_value AS seconds_since_start
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
ON s.database_id = DB_ID()
AND s.object_id = i.object_id AND s.index_id = i.index_id
CROSS JOIN sys.dm_os_performance_counters AS os
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
AND os.object_name LIKE N'%SQL Statistics%'
AND os.counter_name = N'SQLServer uptime';
Watch for Duplicate Shapes
Suppose one index has keys CustomerID, OrderDate and includes TotalAmount. Another has the same keys and includes TotalAmount and Status. The wider index can cover both query groups, but that does not automatically make the smaller one expendable. The smaller structure can be cheaper to read and maintain. Compression, filters, and partition alignment also change the comparison.
Look at actual plans and row counts for the queries using each candidate. Which predicates seek on the leading keys? Which queries rely on ordered output? Which ones need the extra included columns? An index recommendation from a missing-index DMV is a starting suggestion, not a design decision. Those suggestions can create nearly identical indexes.
Choose an Index Budget by Workload to Avoid Too Many Indexes
There is no universal safe number of indexes per table. A small read-heavy dimension table can tolerate several specialized paths. A high-volume event table can suffer with far fewer. The relevant budget is the write cost and storage footprint that the workload can afford, balanced against the reads those indexes prevent.
I use a simple decision table: important queries helped, frequency and cost of writes, index width, uniqueness role, and operational maintenance. If two indexes solve the same query group, test the narrower or better consolidated design first. Avoid consolidating so aggressively that an index becomes a wide answer to every question and a good answer to none.
Test a Removal Carefully
Before removing an index, save its exact definition, including filter, options, compression, and partition scheme. Confirm that it is not backing a constraint. Review plans for critical queries, scheduled jobs, and seasonal processes. If possible, use a representative test environment and replay the relevant workload. A quiet five-minute window cannot represent month-end demand.
Drop one candidate at a time during a controlled change window. Monitor query duration, CPU, reads, blocking, and write throughput, with a clear rollback script ready. An index can be recreated, but doing so on a large table can consume time, log, and space. Treat the rollback cost as part of the decision.
Keep the Inventory Current
Too many indexes accumulate because fixes are additive. A tuning ticket creates an index, another ticket creates a similar one, and nobody owns removal. Make the index inventory part of periodic workload review. Tie every proposed new index to the query or constraint that needs it and record the expected benefit.
When a query changes or a feature retires, revisit its supporting index. After a major workload shift, old usage counters are less useful than fresh measurements. I prefer a small, documented set of indexes with clear owners to a large set of plausible guesses. The maintenance bill arrives on every write, whether anyone reads the invoice or not.
Judge Too Many Indexes With Evidence, Not a Number
A table with many indexes deserves attention, but a count alone cannot tell you which one is wrong. Structural overlap, observed read use, write cost, and business importance provide a defensible decision. Measure during a meaningful period, especially when periodic reports and maintenance jobs are involved.
If the workload is changing rapidly, postpone a destructive cleanup until the new access pattern is visible. Keep the definitions and baseline measurements so the next review starts from evidence. The final goal is reliable transactions and predictable queries, not an aesthetically pleasing index count in a catalog query.
Related reading on this blog: Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses and Validating AI-Generated Index Recommendations.
Too many indexes is not a fixed count, it is write cost without enough read value.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
