DBA Scripts: Get Unused Indexes

Peter Whyte

–

Part of the DBA-Tools Project.

Every non-clustered index has to be maintained by SQL Server on every INSERT, UPDATE, and DELETE against the table. A table with 12 non-clustered indexes means 12 index updates for every row changed. Some of those indexes earn their keep by serving real queries. Others haven’t been read by a single query since the last restart, and are pure write overhead with nothing to show for it.

This script finds indexes with zero reads but non-zero writes, the clearest drop candidates, and includes a second query that classifies every index’s usage pattern so you can see the full picture, not just the extreme cases.

Why Unused Indexes Matters

Indexes accumulate over time. A developer adds one to fix a slow query. A consultant adds three during a performance engagement. The workload shifts and some of those indexes stop serving anything, but nobody goes back to remove them, because there’s no built-in alert for “this index stopped being useful.”

The only way to find them is sys.dm_db_index_usage_stats, which tracks reads and writes per index since the last SQL Server restart:

  • Every unused index still pays the full write-maintenance cost on every DML operation
  • Unused indexes consume disk space and buffer pool memory for zero query benefit
  • They extend backup size and time
  • They add to the index-maintenance workload (fragmentation, statistics) for something nothing ever reads

When to Run This Script

  • Routine SQL Server health checks, after a representative workload period
  • Investigating elevated write latency on a heavily-indexed table
  • Auditing a server or database you’ve just inherited
  • Alongside Duplicate Indexes and Index Design Issues as part of a general index-hygiene pass

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-UnusedIndexes
Category    : performance
Purpose     : Identifies non-clustered indexes with zero read activity but non-zero
              write overhead since the last SQL Server restart. These indexes slow
              every INSERT, UPDATE, and DELETE on the table without benefiting any query.
              Run in the context of the database you want to audit.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-unused-indexes/)
Requires    : VIEW SERVER STATE, VIEW DATABASE STATE
Notes       : Usage stats reset on SQL Server restart — run only after several days
              of representative workload to avoid false positives.
              Do NOT drop without checking all environments — a zero-read index on
              PROD may be critical for a month-end report or a rarely-run job.
              PKs and unique constraints are excluded (structural, cannot be dropped).
              Review write_count vs total_reads ratio. Drop candidates: write_count
              high, total_reads = 0, and size_mb large.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- SCOPE:CurrentDatabase

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,
    ISNULL(s.user_seeks,   0)                                            AS seeks,
    ISNULL(s.user_scans,   0)                                            AS scans,
    ISNULL(s.user_lookups, 0)                                            AS lookups,
    ISNULL(s.user_seeks,0) + ISNULL(s.user_scans,0)
        + ISNULL(s.user_lookups,0)                                       AS total_reads,
    ISNULL(s.user_updates, 0)                                            AS write_count,
    ISNULL(p.rows,         0)                                            AS table_rows,
    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,2))             AS size_mb,
    'DROP INDEX ' + QUOTENAME(i.name)
        + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
        + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';'               AS drop_statement
FROM sys.indexes                                                         i
JOIN sys.tables                                                          t
    ON  i.object_id = t.object_id
JOIN sys.partitions                                                      p
    ON  i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units                                                a
    ON  p.partition_id = a.container_id
LEFT JOIN sys.dm_db_index_usage_stats                                    s
    ON  s.object_id   = i.object_id
    AND s.index_id    = i.index_id
    AND s.database_id = DB_ID()
WHERE i.type_desc              <> 'HEAP'
  AND i.is_primary_key          = 0
  AND i.is_unique_constraint    = 0
  AND t.is_ms_shipped           = 0
  AND ISNULL(s.user_seeks,0) + ISNULL(s.user_scans,0) + ISNULL(s.user_lookups,0) = 0
  AND ISNULL(s.user_updates, 0) > 0
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, i.is_unique,
         s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, p.rows
ORDER BY write_count DESC, size_mb DESC;

The script queries sys.dm_db_index_usage_stats in the context of the current database and returns non-clustered, non-PK, non-unique-constraint indexes with zero reads and at least one write, along with a ready-to-run DROP INDEX statement.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Unused indexes in a specific database (required — usage stats are per-database):
.\run.ps1 Get-UnusedIndexes -Database YourDatabaseName

# To run against a remote sql server:
.\run.ps1 Get-UnusedIndexes -ServerInstance SQLSERVER01 -Database YourDatabaseName

This script lives in the repo at:

See Also — Get-IndexUsageStats

Where Get-UnusedIndexes only surfaces the extreme zero-read case in one database, Get-IndexUsageStats classifies every index across every database by usage pattern (WRITE_ONLY, SCAN_HEAVY, NORMAL), useful when you want the fuller, instance-wide picture rather than just the drop candidates in one database.

Example Output

Get-UnusedIndexes output showing key columns, SQL Server output

Run against a single database (-Database RandomLab in this example):

schema_name table_name index_name type_desc seeks scans lookups total_reads write_count
dbo IndexDemo IX_IndexDemo_Status_Unused NONCLUSTERED 0 0 0 0 1
dbo IndexDemo IX_IndexDemo_CustomerId NONCLUSTERED 0 0 0 0 1
dbo IndexDemo IX_IndexDemo_CustomerId_Dup NONCLUSTERED 0 0 0 0 1

All three flagged indexes here are genuinely idle: IX_IndexDemo_Status_Unused was never queried by design, and the two customer_id indexes lost out to a wider composite index that already covers the same seeks (see Get Duplicate Indexes for that angle on the same table).

Understanding the Results

  • seeks / scans / lookups — all zero for every row this script returns; that’s the filter condition, not something to double check.
  • write_count — how many times this index has been updated. A high number here means real, ongoing write cost with nothing to show for it.
  • size_mb — disk space and buffer pool memory the index is consuming for no benefit.
  • drop_statement — a ready-to-run DROP INDEX statement. Review before executing, don’t run it blind.

How to Fix Unused Indexes

Stats reset on SQL Server restart. sys.dm_db_index_usage_stats accumulates from the moment the instance started. A server restarted last week has only a week of data, and an index serving a monthly batch job won’t show any reads in that window even though dropping it would break the job.

Wait for a representative period before trusting the data. For most OLTP systems, 2-4 weeks covers the full daily and weekly cycle; for systems with monthly jobs, 6-8 weeks is safer. Check sys.dm_os_sys_info.sqlserver_start_time to see how long the current stats have been accumulating.

Safe removal process:

  1. Run this script after a representative workload period.
  2. Sort by size_mb DESC. Larger indexes carry larger overhead, so prioritise those first.
  3. For each candidate, check query history, check all environments (a PROD copy used for month-end reporting may need what production doesn’t), and confirm it isn’t a unique index protecting data integrity.
  4. Script the index definition before dropping, so it can be recreated quickly if something was missed.
  5. Drop one index at a time and monitor write performance before moving to the next.
-- Script the index definition before dropping (fill in name/table)
SELECT
    'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 'NONCLUSTERED INDEX ['
    + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ('
    + key_cols.cols + ')' AS create_statement
FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
CROSS APPLY (
    SELECT STRING_AGG('[' + c.name + '] ' + CASE ic.is_descending_key WHEN 1 THEN 'DESC' ELSE 'ASC' END, ', ')
           WITHIN GROUP (ORDER BY ic.key_ordinal) AS cols
    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
) key_cols
WHERE i.name = 'YourIndexName' AND t.name = 'YourTableName';

-- Then drop it
DROP INDEX [YourIndexName] ON [dbo].[YourTable];

Best Practices

  • Unique non-clustered indexes with zero reads may still be protecting data integrity, not just serving queries. Review with the application owner before dropping.
  • Filtered indexes may serve a rare but important query. Check the filter definition before deciding.
  • Never drop an unused index without checking every environment (DR, reporting replicas, month-end copies) that shares the schema.
  • Re-run this script after a representative period following any drop, to confirm nothing regressed.

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

How long should I wait before trusting “unused” results?

At least 2-4 weeks of representative workload for most OLTP systems, longer if the environment has monthly or quarterly jobs. Check sqlserver_start_time to see how long the current stats window actually covers.

Is it safe to drop every index this script returns?

Not automatically. It’s a strong candidate list, not a guaranteed-safe list. Confirm the index isn’t a unique constraint, isn’t referenced by an index hint, and isn’t only used by a rare job or a different environment before dropping.

Summary

Every non-clustered index that isn’t earning its keep is a small, permanent tax on every write against its table. Unlike most performance problems, fixing this one is close to risk-free: removing an index that’s genuinely unused costs nothing in query performance.

Run this script (and its broader sibling, Index Usage Stats) after a representative workload period, prioritise the largest write-cost offenders first, and re-check after a representative period following any drop to confirm nothing regressed.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *