Index Slows Down SELECT? What an Unused Index Costs You

An index slows down SELECT queries only a little when the plan never reads it. The real bill arrives on writes. The question starts when a query gets slower after someone adds an index. A test with a counter for each effect separates the causes.

What an Unused Index Cannot Do

A SELECT reads only the structures in its plan. If the plan never touches an index, that index adds no page reads to the query. So an index slows down SELECT queries only when the plan changed or the compile work grew. If neither happened, look elsewhere. Cache state, blocking and a noisy timing are the usual suspects.

An index can still reach the SELECT in two ways. The optimizer considers it while it builds the plan, and it can pick it. The test below measures both.

Build the Test

The first script creates a shipments table with 20,000 rows and one index that covers the query. A small procedure runs the query. A view reads the compile memory, the plan hash and the logical reads from the plan cache.

IF DB_ID(N'IndexCompileDemo') IS NULL CREATE DATABASE IndexCompileDemo;
GO
USE IndexCompileDemo;
GO
DROP PROCEDURE IF EXISTS dbo.ShipmentProbe;
DROP VIEW IF EXISTS dbo.ProbeInfo;
DROP TABLE IF EXISTS dbo.Shipments;
CREATE TABLE dbo.Shipments (
    ShipmentID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    ShipDate   date NOT NULL,
    Status     varchar(12) NOT NULL,
    Region     varchar(20) NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Weight     int NOT NULL,
    Packages   int NOT NULL
);
INSERT INTO dbo.Shipments
SELECT n, 1 + (n * 31) % 500, DATEADD(DAY, (n * 17) % 365, '2026-01-01'),
       CHOOSE(n % 3 + 1, 'Open', 'Shipped', 'Closed'), CHOOSE(n % 4 + 1, 'East', 'West', 'North', 'South'),
       10 + n % 90, 1 + n % 40, 1 + n % 5
FROM (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
CREATE INDEX IX_Shipments_Date ON dbo.Shipments (ShipDate) INCLUDE (CustomerID, Status, Amount);

CREATE PROCEDURE dbo.ShipmentProbe
AS
BEGIN
    DECLARE @total decimal(18,2);
    SELECT @total = SUM(Amount)
    FROM dbo.Shipments
    WHERE ShipDate >= '2026-03-01' AND ShipDate < '2026-05-01' AND CustomerID = 17;
END;

CREATE VIEW dbo.ProbeInfo
AS
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT (SELECT COUNT(*) - 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.Shipments')) AS NonclusteredIndexes,
       qp.query_plan.value('(//StmtSimple/@QueryPlanHash)[1]', 'varchar(20)') AS PlanHash,
       qp.query_plan.value('(//StmtSimple/QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemoryKB,
       ps.last_logical_reads AS LogicalReads
FROM sys.dm_exec_procedure_stats AS ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE ps.database_id = DB_ID() AND ps.object_id = OBJECT_ID(N'dbo.ShipmentProbe');

Twelve Unrelated Indexes

Run the procedure once, and read the view. That is the baseline with one index. Then create twelve indexes whose leading columns are Weight, Packages or Region. The query filters on none of those columns, so the optimizer has no reason to pick them. Run the procedure and the view again.

EXEC dbo.ShipmentProbe;
SELECT N'Baseline' AS Step, * FROM dbo.ProbeInfo;

CREATE INDEX IX_U1 ON dbo.Shipments (Weight);
CREATE INDEX IX_U2 ON dbo.Shipments (Packages);
CREATE INDEX IX_U3 ON dbo.Shipments (Weight, Packages);
CREATE INDEX IX_U4 ON dbo.Shipments (Packages, Weight);
CREATE INDEX IX_U5 ON dbo.Shipments (Region, Weight);
CREATE INDEX IX_U6 ON dbo.Shipments (Region, Packages);
CREATE INDEX IX_U7 ON dbo.Shipments (Weight, Packages, Region);
CREATE INDEX IX_U8 ON dbo.Shipments (Packages, Weight, Region);
CREATE INDEX IX_U9 ON dbo.Shipments (Region);
CREATE INDEX IX_U10 ON dbo.Shipments (Weight, Region);
CREATE INDEX IX_U11 ON dbo.Shipments (Packages, Region);
CREATE INDEX IX_U12 ON dbo.Shipments (Region, Weight, Packages);

EXEC dbo.ShipmentProbe;
SELECT N'Twelve unrelated indexes' AS Step, * FROM dbo.ProbeInfo;

The plan hash is identical, so the plan is the same plan. The reads are identical, so the unused indexes added no page reads. The compile memory grew by 16 KB, about 5 percent. That is the compile cost of twelve indexes the plan never used. It matters most for a query that compiles thousands of times a minute.

So an index with unrelated columns has almost no effect on the query. The compile memory grew by 16 KB and the plan stayed the same. A query whose plan stays the same shows no difference in reads.

One Related Index

Now add an index that fits the query: the customer first, then the date. The plan changes, because the optimizer has a better path.

CREATE INDEX IX_R1 ON dbo.Shipments (CustomerID, ShipDate) INCLUDE (Status, Amount);

EXEC dbo.ShipmentProbe;
SELECT N'One related index' AS Step, * FROM dbo.ProbeInfo;

The plan hash changed, and the reads fell from 18 to 2. This is the other way an index reaches a SELECT. Here the new plan is better. With bad row estimates, a new index can also pull a query into a worse plan. That is a plan change, and the fix is to compare the plans, not to blame the index pages.

A change of database compatibility level can change plans in the same way, because the cardinality estimator changes with it. Compare the old and new plan, and keep Query Store on so you have the history.

The Real Cost Is on Writes

Inserts and deletes touch every index on a table. The next script inserts 5,000 rows and counts the leaf inserts per structure.

SET NOCOUNT ON;
DECLARE @before TABLE (index_id int PRIMARY KEY, inserts bigint);
INSERT @before
SELECT index_id, leaf_insert_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'dbo.Shipments'), NULL, NULL);
INSERT INTO dbo.Shipments
SELECT 100000 + n, 1 + n % 500, '2026-06-01', 'Open', 'East', 25, 5, 2
FROM (SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
SELECT COUNT(*) AS StructuresTouched,
       SUM(CASE WHEN o.leaf_insert_count - b.inserts = 5000 THEN 1 ELSE 0 END) AS GotAll5000Rows,
       SUM(o.leaf_insert_count - b.inserts) AS TotalInserts
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'dbo.Shipments'), NULL, NULL) AS o
JOIN @before AS b ON b.index_id = o.index_id;

Every one of the 15 structures took all 5,000 rows, so 5,000 inserted rows became 75,000 index inserts. The twelve unrelated indexes gave nothing back for that work.

An update is kinder. It maintains only the indexes that hold a changed column. The next script changes Status on the new rows. Only the clustered index and the two indexes that include Status do any work.

SET NOCOUNT ON;
DECLARE @before TABLE (index_id int PRIMARY KEY, updates bigint);
INSERT @before
SELECT index_id, leaf_update_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'dbo.Shipments'), NULL, NULL);
UPDATE dbo.Shipments SET Status = 'Closed' WHERE ShipmentID > 100000;
SELECT COUNT(*) AS StructuresTouched,
       SUM(CASE WHEN o.leaf_update_count - b.updates > 0 THEN 1 ELSE 0 END) AS WithUpdates,
       SUM(o.leaf_update_count - b.updates) AS TotalUpdates
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'dbo.Shipments'), NULL, NULL) AS o
JOIN @before AS b ON b.index_id = o.index_id;

Three structures did the work, 5,000 updates each. Indexes also take space and add to backup and restore time. For a read-only look at which indexes earn their keep, see my Unused Index Script post.

You could argue that storage is cheap and an extra index does no harm. Disk space is cheap. The write work is not, because it runs on every insert and delete for the life of the index. It also adds locks and log records to the busiest part of the workload.

What to Remember

If you suspect that an index slows down SELECT queries, compare the plans first. Same plan hash and same reads mean the index is not the cause of a slower execution. A different plan hash means the optimizer chose differently, and the estimates need a look.

Judge an index by what it saves on reads and what it costs on writes. Test the change on a copy, keep the before and after plans, and drop the indexes that save nothing. Then run the cleanup script.

USE master;
GO
IF DB_ID(N'IndexCompileDemo') IS NOT NULL
BEGIN
    ALTER DATABASE IndexCompileDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE IndexCompileDemo;
END;

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

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