Indexes tend to outlive the problems that created them. It usually starts with a slow query. An engineer adds an index, the immediate issue goes away, and everyone moves on. At the time, the decision makes sense. The problem is that the index often stays in place long after the application changes, a report is replaced, or a feature quietly falls out of use.
Over time, a busy table can collect a long list of nonclustered indexes. Some are still doing useful work. Others are only there because nobody has had a good reason, or enough confidence, to remove them.
That's where sys.dm_db_index_usage_stats comes into play. An index that is updated frequently but has no recorded seeks, scans, or lookups is worth investigating. However, it is not proof that the index is no longer needed. The counters reset when SQL Server restarts, and some workloads only run at specific times. An index that looks unused today may still support month-end reporting, an annual reconciliation, or an audit query that runs once in a while.
That is why DBAs are cautious about dropping indexes. Dropping based on one snapshot could create a problem that does not show up until the next business cycle. This is why DBAs are cautious, even when an index appears unnecessary.
So the goal here is not to remove every index with a zero next to it. The goal is to get enough evidence to make a safe call, one thing at a time, then watch the workload after the change, and have a rollback script ready if the workload paints a different picture.
The cost of 'just in case' indexes
The thing about an unused index is that it does not become free just because nobody is reading from it. If you update an order, SQL Server may have to update the order row and every index that includes the columns that changed. That adds work to inserts, updates, and deletes. A few useful indexes are worth that trade-off. Once an older index stops helping queries, though, SQL Server still has to keep it current.
That work is recorded as user_updates in sys.dm_db_index_usage_stats. Wider indexes, particularly those with large INCLUDE lists, also use more pages, create more log activity, and make rebuilds and maintenance jobs heavier. Microsoft warns against speculative and overly wide indexes for the same reasons.
That does not mean a table with many indexes is automatically a problem. A reporting table may need several of them. An insert-heavy queue table may need far fewer. The question is whether each index still helps enough to justify the work it adds.
This balance can be lost, since indexes are added one slow query at a time. That pattern repeated, with one team describing tables with more than 70 indexes in a 2023 DBA Stack Exchange discussion. That does not mean all 70 were unnecessary. It does show how old fixes can linger in the database long after the original problem has changed.
Start with a baseline, not a deletion list
The first job is to build a picture of what is actually in the database. Look at usage, size, and basic index metadata together. If the index is big and is constantly being updated, then an index with no reads is more interesting. But on its own, it's not enough to bring about a decline.
The query returns a starting inventory of standard rowstore indexes. This excludes heaps, primary keys, unique indexes and hypothetical indexes. That is deliberate. The other indexes are candidates for review, not indexes that are safe to delete.
WITH index_size AS
(
SELECT object_id,
index_id,
SUM(used_page_count) * 8.0 / 1024 AS used_mb
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
)
SELECT
schema_name = s.name,
table_name = t.name,
index_name = i.name,
i.type_desc,
size_mb = CAST(COALESCE(z.used_mb, 0) AS decimal(18,1)),
reads = COALESCE(u.user_seeks, 0)
+ COALESCE(u.user_scans, 0)
+ COALESCE(u.user_lookups, 0),
writes = COALESCE(u.user_updates, 0),
u.last_user_seek,
u.last_user_scan,
u.last_user_lookup,
u.last_user_update,
os.sqlserver_start_time
FROM sys.indexes AS i
JOIN sys.tables AS t
ON t.object_id = i.object_id
JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
ON u.database_id = DB_ID()
AND u.object_id = i.object_id
AND u.index_id = i.index_id
LEFT JOIN index_size AS z
ON z.object_id = i.object_id
AND z.index_id = i.index_id
CROSS JOIN sys.dm_os_sys_info AS os
WHERE i.type = 2
AND i.is_hypothetical = 0
AND i.is_primary_key = 0
AND i.is_unique = 0
ORDER BY writes DESC, reads ASC, size_mb DESC;
Keep the SQL Server restart time in the output. As Microsoft's DMV documentation explains, the counters are cleared when the Database Engine starts. A restart, detach, or shutdown can leave an index looking unused simply because SQL Server has not been running long enough to see its normal workload. Zero reads after six days means six days of evidence. It does not tell you the full history of the index.
So, store the results in an admin database or monitoring platform and keep collecting them over time. Ensure that the observation window includes the workloads that are most important. These include weekly jobs, month-end reporting, quarter-end processing, seasonal peaks, and disaster-recovery tests, as appropriate.
There is no universal cut-off, but Azure SQL automatic tuning waits for more than 90 days before treating an index as unused. That is a useful check against making a decision after one quiet week.
Duplicate and overlapping are different findings
Exact duplicates are the easiest candidates to investigate. They have the same key columns in the same order, including sort direction, as well as the same included columns and filter options. Their names do not matter.
Overlaps need more judgment. Consider these two indexes:
CREATE INDEX IX_Order_Customer
ON Sales.OrderHeader (CustomerID)
INCLUDE (OrderDate, TotalDue);
CREATE INDEX IX_Order_Customer_Date
ON Sales.OrderHeader (CustomerID, OrderDate)
INCLUDE (TotalDue);
Both start with CustomerID, so the second may cover some of the same work as the first. But it is wider, and it can be better for queries that filter or sort by OrderDate. That makes it an overlap, not a duplicate. The same caution applies to filtered indexes, unique indexes, and indexes with a different sort direction. Similar column names do not mean the indexes do the same job.
This is also how missing-index recommendations can create clutter. SQL Server stores up to 600 missing-index groups, and Microsoft notes that similar suggestions often need to be combined. Treat each one as a clue about a query workload, not as a script to run.
Before removing an overlapping index, check the queries and plans that use both indexes. Query Store is useful because it keeps plan and runtime history beyond the current plan cache. Also check application code for index hints. A hinted query may rely on an index that looks redundant in the metadata.
Use a retirement queue
Unused indexes should go into a review queue, not straight to a DROP INDEX script.
Check telemetry more than once and review queries and business timing around it then assign an owner and change window. Test the exact CREATE INDEX rollback script in production before you create it. It should preserve key order, included columns, filters, uniqueness, compression, filegroup or partition scheme, and relevant options.
Remove a small batch, then watch the workload through an agreed period. If performance changes, restore the index. This makes it possible to connect a regression to a specific change.
Do not treat ALTER INDEX ... DISABLE as an easy test. Disabling a nonclustered index removes its physical data and requires a rebuild to use it again. Disabling a clustered index makes the table inaccessible. A scripted drop with a tested create script is often simpler.
Azure SQL does similar work automatically: it observes queries after a drop, and recreates the index if they slow down. The practical rule is the same. Change, watch and reverse when the evidence tells you to.
Put the change under operational control
SQL Server telemetry should lead the investigation. dbForge Studio for SQL Server gives the team a practical place to inspect and control the change once they have a candidate.
In Table Editor, engineers can review an index's key and included columns, type, storage details, fragmentation, and usage statistics. This is particularly useful when one table has several similar index definitions and catalog output is becoming hard to read.
Before removing anything, generate the CREATE INDEX script and attach it to the change. dbForge can generate CREATE, DROP, and DROP and CREATE scripts, so the team can review the actual T-SQL and keep a usable rollback. Schema Compare then helps confirm that the indexes selected for removal are the only intended schema differences.
Use dbForge Source Control to compare the change against the repository, then commit the removal and rollback scripts together. That gives the team a record of what changed, why it changed, and how to reverse it.
Watch the workload that matters
Compare performance before and after change over similar time periods. Query Store helps here, as it stores plans and run time history. This is the default option for new SQL Server 2022 databases.
Do not use averages across the whole database. Watch for the queries that used the index, the application endpoints behind them, and the scheduled jobs that are most likely to detect its absence. Duration, CPU, reads, writes, timeouts, blocking and log IO checks. You should also see the benefit that is expected on the write side. If dropping a large index doesn't make a difference, it's worth finding out why.
Set the rollback threshold prior to the change. If the overall CPU is not changed, but an important order lookup suddenly takes twice as many logical reads, restore the index. If the index was adding a lot of write and maintenance work, a little slowdown in a five minute reporting job might be acceptable. Query frequency is a factor but so is business impact.
Leave evidence for the next engineer
Record what was reviewed and why. At a minimum, keep the index and table name, observation period, restart events, usage and size data, overlap analysis, affected queries, test results, monitoring window, final decision, and rollback script.
Document the indexes you keep as well. A note such as "retained because it supports the quarterly close" can save the next engineer from repeating the same investigation six months later.
This is what makes index cleanup normal engineering work rather than occasional housekeeping. New indexes have a reason and an owner. Old ones can be questioned with evidence.
Takeaway
Unused indexes should not remain forever because nobody wants to drop them. But they should not disappear because one DMV returned zero either. Look across the right business cycle, make small reversible changes, and judge the result by the workload users actually feel. dbForge Studio helps keep the inspection, scripts, schema comparison, and repository history in one place. The process is what makes the change safe.
Top comments (1)
Great article! Clear, practical, and easy to follow. Thanks for sharing!