Unused Index Script: Find Indexes That Only Cost You Writes

This Unused Index Script lists nonclustered indexes that SQL Server keeps writing to and no query ever reads. Each one slows every insert, update and delete, and it gives your queries nothing back. The list tells you where to look. The checks below tell you whether to act.

What an Unused Index Costs

An index is a sorted copy of some columns. When a row changes, SQL Server must update every index that holds those columns. An index that no query reads still gets those updates, so it adds work and takes space for no gain.

SQL Server keeps a tally for each index in sys.dm_db_index_usage_stats. A seek jumps to the rows it needs. A scan reads a range or the whole index. A lookup fetches the rest of a row from the table. Those three are reads. An update is a write, and an index with writes and no reads is a candidate.

The write tally counts statements, not rows. One UPDATE that changes 10,000 rows adds one to the count. So a high number means many statements touched the index, and a low number doesn’t mean few rows changed.

How Long Is the Evidence?

The tally starts empty when the SQL Server service starts. It’s also cleared for a database that is detached or closed, for example by AUTO_CLOSE, and after a failover. A readable replica keeps its own tally, so check the one that runs the reads. An index behind a quarterly report shows no reads until the quarter ends.

So the script prints the start time first. If the server restarted last week, don’t act on the list. I wait for a full business cycle, such as a month-end close. On SQL Server 2025 an index rebuild leaves the tally alone. Older versions cleared an index’s row on a rebuild.

A Table With Three Suspects

The script below creates a database named UnusedIndexDemo, used only for this example. It builds customers, orders and a price list, each with indexes, and loads 100,000 orders. Run it on a test instance.

IF DB_ID(N'UnusedIndexDemo') IS NULL CREATE DATABASE UnusedIndexDemo;
GO
USE UnusedIndexDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.Customers;
DROP TABLE IF EXISTS dbo.PriceList;
CREATE TABLE dbo.Customers (
    CustomerID   int          NOT NULL PRIMARY KEY,
    CustomerName nvarchar(60) NOT NULL
);
CREATE TABLE dbo.Orders (
    OrderID    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID int          NOT NULL REFERENCES dbo.Customers (CustomerID),
    OrderDate  date         NOT NULL,
    Status     tinyint      NOT NULL,
    Total      decimal(9,2) NOT NULL
);
CREATE TABLE dbo.PriceList (
    ProductID int          NOT NULL PRIMARY KEY,
    Price     decimal(9,2) NOT NULL
);
INSERT INTO dbo.Customers (CustomerID, CustomerName)
SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N'Customer'
FROM sys.all_columns;
INSERT INTO dbo.Orders (CustomerID, OrderDate, Status, Total)
SELECT TOP (100000)
       1 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5000),
       DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 365, '2026-01-01'),
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3,
       10 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 90)
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
CREATE INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate) INCLUDE (Total);
CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);
CREATE INDEX IX_PriceList_Price ON dbo.PriceList (Price);

Now give the server some work. One query reads a month of orders by date, which uses the date index. Three updates change the status of different ranges of orders.

SELECT SUM(Total) AS JuneTotal
FROM dbo.Orders
WHERE OrderDate >= '2026-06-01' AND OrderDate < '2026-07-01';
UPDATE dbo.Orders SET Status = 2 WHERE OrderID <= 10000;
UPDATE dbo.Orders SET Status = 1 WHERE OrderID BETWEEN 10001 AND 20000;
UPDATE dbo.Orders SET Status = 0 WHERE OrderID = 5;

The Unused Index Script

This Unused Index Script starts from sys.indexes, not from the usage view. That matters. The usage view gets a row for an index only after something touches it. A script that starts there never sees an index nobody has used at all. Starting from the index list catches that kind too.

The script skips primary keys, unique indexes, unique constraints, disabled and hypothetical indexes, and anything Microsoft ships. A unique index enforces a rule, so zero reads doesn’t make it safe to drop. The CheckFirst column flags two more cases: an index that leads with a foreign key column, and a filtered index. It needs SQL Server 2017 or later, for STRING_AGG.

SELECT sqlserver_start_time AS CollectingSince
FROM sys.dm_os_sys_info;

SELECT CONCAT(SCHEMA_NAME(t.schema_id), N'.', t.name) AS TableName,
       i.name AS IndexName,
       ISNULL(u.user_seeks + u.user_scans + u.user_lookups, 0) AS Reads,
       ISNULL(u.user_updates, 0) AS Writes,
       sz.RowsInIndex,
       sz.SizeMB,
       CONCAT_WS(N', ',
                 CASE WHEN EXISTS (SELECT 1
                                   FROM sys.foreign_key_columns AS fk
                                   JOIN sys.index_columns AS lead ON lead.object_id = fk.parent_object_id
                                                                 AND lead.column_id = fk.parent_column_id
                                   WHERE lead.object_id = i.object_id AND lead.index_id = i.index_id
                                     AND lead.key_ordinal = 1) THEN N'foreign key' END,
                 CASE WHEN i.has_filter = 1 THEN N'filtered' END) AS CheckFirst,
       CONCAT(N'ALTER INDEX ', QUOTENAME(i.name), N' ON ', QUOTENAME(SCHEMA_NAME(t.schema_id)), N'.', QUOTENAME(t.name), N' DISABLE;') AS DisableStatement,
       CONCAT(N'CREATE NONCLUSTERED INDEX ', QUOTENAME(i.name), N' ON ', QUOTENAME(SCHEMA_NAME(t.schema_id)), N'.', QUOTENAME(t.name),
              N' (', k.KeyList, N')',
              CASE WHEN inc.IncludeList IS NOT NULL THEN CONCAT(N' INCLUDE (', inc.IncludeList, N')') END,
              CASE WHEN i.has_filter = 1 THEN CONCAT(N' WHERE ', i.filter_definition) END, N';') AS RecreateStatement
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_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
CROSS APPLY (SELECT SUM(ps.row_count) AS RowsInIndex,
                    CAST(SUM(ps.used_page_count) * 8 / 1024.0 AS decimal(10, 1)) AS SizeMB
             FROM sys.dm_db_partition_stats AS ps
             WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id) AS sz
CROSS APPLY (SELECT STRING_AGG(CONCAT(QUOTENAME(c.name), CASE WHEN ic.is_descending_key = 1 THEN N' DESC' END), N', ')
                              WITHIN GROUP (ORDER BY ic.key_ordinal)
             FROM sys.index_columns AS ic
             JOIN sys.columns AS 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 k(KeyList)
OUTER APPLY (SELECT STRING_AGG(QUOTENAME(c.name), N', ') WITHIN GROUP (ORDER BY c.column_id)
             FROM sys.index_columns AS ic
             JOIN sys.columns AS 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 = 1) AS inc(IncludeList)
WHERE i.type = 2
  AND i.is_primary_key = 0 AND i.is_unique = 0 AND i.is_unique_constraint = 0
  AND i.is_disabled = 0 AND i.is_hypothetical = 0
  AND t.is_ms_shipped = 0
  AND ISNULL(u.user_seeks + u.user_scans + u.user_lookups, 0) = 0
ORDER BY Writes DESC, sz.SizeMB DESC;

The first result is the start time of the evidence. The second lists the candidates. IX_Orders_Status has no reads and three writes, one for each UPDATE. IX_Orders_OrderDate isn’t listed, because the date query read it.

IX_Orders_CustomerID and IX_PriceList_Price show zero reads and zero writes. Neither appears in the usage view. The price list has no rows, and no statement has changed CustomerID since its index was built. A script that started from the usage view would have missed both. Zero writes also means there’s no evidence either way. The first one carries the foreign key flag.

Zero reads isn’t the only warning sign. For example, an index with two reads and 400,000 writes tells a similar story. The script keeps to zero reads on purpose, so the list stays short and safe. To widen it, change the last condition in the WHERE clause.

Respect the Foreign Key Flag

A foreign key index does its work when someone deletes a parent row. SQL Server then checks the child table for matching rows. Adding a customer and deleting it again gives IX_Orders_CustomerID its first seek. Until a parent row is deleted, that index shows no reads. Don’t drop it on the strength of a zero.

INSERT INTO dbo.Customers (CustomerID, CustomerName) VALUES (6000, N'Temporary');
DELETE FROM dbo.Customers WHERE CustomerID = 6000;
SELECT i.name AS IndexName, u.user_seeks AS Seeks
FROM sys.dm_db_index_usage_stats AS u
JOIN sys.indexes AS i ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE u.database_id = DB_ID() AND i.name = N'IX_Orders_CustomerID';

Disable Before You Drop

A disabled index keeps its definition, but SQL Server stops maintaining it. The DisableStatement column writes the command. RecreateStatement covers the keys, the INCLUDE columns and the filter only. Script the full index from Management Studio before you drop one that uses compression, partitions or a non-default filegroup. The cost of an index shows up at once. This test updates 1,000 rows before and after the disable.

SET STATISTICS IO ON;
UPDATE dbo.Orders SET Status = 1 WHERE OrderID <= 1000;
ALTER INDEX IX_Orders_Status ON dbo.Orders DISABLE;
UPDATE dbo.Orders SET Status = 2 WHERE OrderID <= 1000;
SET STATISTICS IO OFF;

Now wait a full business cycle. A hinted query or a forced plan fails with error 315 while the index is disabled. Other queries quietly choose a different plan, and the only sign is a slower run. So watch durations and plan changes in Query Store or your plan history, not only errors. If nothing gets slower, drop it. If something does, a rebuild turns the index back on.

ALTER INDEX IX_Orders_Status ON dbo.Orders REBUILD;
SELECT name, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Orders') AND name = N'IX_Orders_Status';

You could argue that zero reads is proof enough, and that waiting only costs more writes. That’s fair for an index a developer built last week and can explain. It’s weak for an old index nobody can explain, on a table with a quarter-end job. The wait costs a few writes. A wrong drop costs a rebuild when someone is waiting for a report.

What to Remember

Read the start time first, then Reads and Writes. Leave unique and foreign key indexes alone until you know why they exist. Disable, wait a business cycle, and only then drop.

In my health checks, I list unused indexes in the report. I don’t drop one from a vendor database without the vendor’s word. To add the right indexes, read Missing Index Script: Read the Suggestions Before You Create. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE UnusedIndexDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE UnusedIndexDemo;

An unused index is not free storage, it is a cost you pay on every write.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.