SQL Server Index Usage Review

Purpose

Analyses SQL Server index usage statistics to identify:

1. Unused indexes (no seeks, scans, or lookups since last statistics clear)
2. Indexes with high write cost and low read benefit
3. Missing index recommendations from the query optimizer

Required Permissions

VIEW SERVER STATE — required for sys.dm_db_index_usage_stats and sys.dm_db_missing_index_*.

Risk Level

Low for the diagnostic script itself. Decisions made on the output (dropping indexes) are Medium risk — ensure you understand the usage statistics age before acting.

Preconditions

• Index usage statistics in sys.dm_db_index_usage_stats reset when the SQL Server service restarts or DBCC SQLPERF('sys.dm_db_index_usage_stats', CLEAR) is run.
• Check when the service last restarted before interpreting zero-use indexes as truly unused.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Script

-- ============================================================
-- SQL Server Index Usage Review
-- Permissions: VIEW SERVER STATE
-- Risk: Read-only (script); Medium (action on output)
-- ============================================================

-- 1. Unused indexes by database
--    Excludes: primary keys, unique constraints, clustered indexes
SELECT
    DB_NAME(ius.database_id)          AS database_name,
    OBJECT_NAME(ius.object_id, ius.database_id) AS table_name,
    i.name                            AS index_name,
    i.type_desc                       AS index_type,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    CAST(8 * SUM(a.used_pages)
        / 1024.0 AS DECIMAL(10,1))    AS index_size_mb
FROM sys.dm_db_index_usage_stats     AS ius
JOIN sys.indexes                     AS i
    ON ius.object_id = i.object_id
    AND ius.index_id = i.index_id
JOIN sys.allocation_units            AS a
    ON a.container_id = (
        SELECT p.partition_id
        FROM sys.partitions p
        WHERE p.object_id = i.object_id AND p.index_id = i.index_id
    )
WHERE ius.database_id = DB_ID()
  AND i.type_desc NOT IN ('HEAP', 'CLUSTERED')
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND ius.user_seeks = 0
  AND ius.user_scans = 0
  AND ius.user_lookups = 0
GROUP BY
    ius.database_id, ius.object_id, i.name, i.type_desc,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
ORDER BY ius.user_updates DESC;

-- 2. High write / low read ratio indexes
SELECT
    DB_NAME(ius.database_id)          AS database_name,
    OBJECT_NAME(ius.object_id, ius.database_id) AS table_name,
    i.name                            AS index_name,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS total_reads,
    ius.user_updates                  AS total_writes,
    CASE WHEN ius.user_updates > 0
        THEN CAST(
            (ius.user_seeks + ius.user_scans + ius.user_lookups) * 1.0
            / ius.user_updates AS DECIMAL(10,4)
        )
    END                               AS read_per_write_ratio
FROM sys.dm_db_index_usage_stats     AS ius
JOIN sys.indexes                     AS i
    ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ius.database_id = DB_ID()
  AND i.type_desc NOT IN ('HEAP', 'CLUSTERED')
  AND i.is_primary_key = 0
  AND ius.user_updates > 10000
  AND (ius.user_seeks + ius.user_scans + ius.user_lookups) < 100
ORDER BY ius.user_updates DESC;

-- 3. Missing index recommendations (current database)
SELECT
    ROUND(
        migs.avg_total_user_cost * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans), 0
    )                                  AS improvement_measure,
    mid.statement                      AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats   AS migs
JOIN sys.dm_db_missing_index_groups        AS mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details       AS mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Interpreting Results

Unused indexes: Zero user_seeks, user_scans, and user_lookups since last restart. High user_updates means the index is being maintained but never read. These are candidates for removal after verifying statistics age.

High write / low read: A read-per-write ratio below 0.01 on a high-update table is a strong signal that an index is adding overhead without benefit.

Missing index recommendations: The optimizer recorded that a specific index would have improved a query. The improvement_measure is a relative estimate — it is not an absolute metric. Use it for prioritisation, not as a guaranteed performance uplift.

Safe Execution Guidance

Run in SSMS or sqlcmd. Safe at any time. Always confirm service restart time before treating zero-usage indexes as permanently unused.

Rollback Steps

If a dropped index is found to be required, recreate it online:

CREATE INDEX idx_name ON dbo.tablename (column)
WITH (ONLINE = ON);

Keep the DROP INDEX statement and the matching CREATE INDEX statement together in your change-control record.