Contact us
Contact us
Contact us
LinkedIn
X
Facebook

SQL Server – Should I Drop Unused Indexes?

Table of Contents

Yes, but there’s an edge case…

Firstly, a quick note on why unused indexes are a bad thing

Indexes are essential for helping SQL Server fetch rows efficiently. However, as the saying goes, “there’s no such thing as a free lunch” because: 

  1. As data changes, indexes must be updated, which slows down INSERT/UPDATE/DELETE statements. 
     
  1. Indexes increase the size of our database causing backups and other maintenance tasks to take longer. 

So, should I drop all unused indexes?

First of all, it’s important that you check when the server was last restarted. If it was done recently, then your usage stats may not be a true reflection of what’s happening on your server.  

That aside, it’s generally a good idea to drop indexes that aren’t being used (i.e. no seeks, no scans, no lookups) by your queries. It’s one of the ‘quick’ wins I start with when performing a SQL Server Health Check, and it’s super-easy to do using Pinal Dave’s Unused Index script.

However, be aware of the little-known edge case

This is the part that may surprise you (and the reason that I decided to  write this blog!): SQL Server can make use of an index WITHOUT physically touching it. Here’s why: 

When an index is created, SQL Server will also create a statistic for that index. The SQL Server optimiser can then use this statistic when generating the query execution plan. However, this doesn’t actually touch the index itself (and without registering a usage stat in sys.dm_db_index_usage_stats). 

If the index is subsequently dropped, so is the statistic. 

Example 1

To illustrate the scenario, we’ll create a couple of identical tables and insert a large amount of dummy data. We’ll then create an index, run a query to join the tables and examine the execution plan.  

				
					DROP TABLE IF EXISTS IndexStatsDemo; 
DROP TABLE IF EXISTS IndexStatsDemo2; 
GO 

-- Step 1: Create the tables 
CREATE TABLE IndexStatsDemo 
( 
    Id INT IDENTITY(1, 1) PRIMARY KEY, 
    Category INT, 
    SomeValue INT, 
    Padding CHAR(100) 
); 
GO 


CREATE TABLE IndexStatsDemo2 
( 
    Id INT IDENTITY(1, 1) PRIMARY KEY, 
    Category INT, 
    SomeValue INT, 
    Padding CHAR(100) 
); 
GO 


-- Step 2: Insert skewed data 
-- Most rows have Category = 1, few have Category = 2 
INSERT INTO IndexStatsDemo 
( 
    Category, 
    SomeValue, 
    Padding 
) 
SELECT CASE 
           WHEN x <= 950000 THEN 
               1 
           ELSE 
               2 
       END,
       x % 100, 
       REPLICATE('X', 100) 
FROM 
( 
    SELECT TOP (1000000) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x 
    FROM sys.all_objects a 
        CROSS JOIN sys.all_objects b 
) AS numbers; 
GO 
 
INSERT INTO IndexStatsDemo2 
( 
    Category, 
    SomeValue, 
    Padding 
) 
SELECT CASE 
           WHEN x <= 950000 THEN 
               1 
           ELSE 
               2 
       END, 
       x % 100, 
       REPLICATE('X', 100) 
FROM 
( 
    SELECT TOP (1000000) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x 
    FROM sys.all_objects a 
        CROSS JOIN sys.all_objects b 
) AS numbers; 
GO

If we examine the data, we can see that it’s highly skewed towards category=1. 

				
					SELECT Category, 
       COUNT(*) 
FROM dbo.IndexStatsDemo 
GROUP BY Category;
Data for unused indexes

In these circumstances, having good statistics is essential in helping the optimiser choose an efficient query plan. 

Let’s create an index on the Category column on both tables. This will cause SQL Server to also create a statistic for that index. 

				
					-- Step 3: Create a nonclustered index on Category 
CREATE NONCLUSTERED INDEX IX_Category ON IndexStatsDemo (Category);
GO 

CREATE NONCLUSTERED INDEX IX_Category ON IndexStatsDemo2 (Category);
GO

Now, let’s run a query against these tables and examine the execution plan:

				
					SELECT * 
FROM IndexStatsDemo t1 
    INNER JOIN IndexStatsDemo2 t2 
        ON t1.Id = t2.Id 
WHERE t1.Category = 1 
OPTION (RECOMPILE);

At first glance, our new index appears not to be used. SQL Server is instead choosing to scan the clustered index on each table.

Clustered index on each table - unused index

…and if we re-run Pinal Dave’s Unused Index script, the usage stats remain zero: 

Data for unused indexes

However, it we look at the execution plan XML we can see that our category index is referenced:

				
					<Batch> 
      <Statements> 
        <StmtSimple … 
            <OptimizerStatsUsage> 
              <StatisticsInfo Database="[MYDB]" Schema="[dbo]" Table="[IndexStatsDemo]" Statistics="[IX_Category]" ModificationCount="0" SamplingPercent="100" LastUpdate="2025-04-16T12:03:11.17" /> 
              <StatisticsInfo Database="[MYDB]" Schema="[dbo]" Table="[IndexStatsDemo]" Statistics="[PK__IndexSta__3214EC078BDE4D74]" ModificationCount="0" SamplingPercent="9.9416" LastUpdate="2025-04-16T12:03:17.59" /> 
              <StatisticsInfo Database="[MYDB]" Schema="[dbo]" Table="[IndexStatsDemo2]" Statistics="[PK__IndexSta__3214EC077CF43024]" ModificationCount="0" SamplingPercent="10.115" LastUpdate="2025-04-16T12:03:17.63" /> 
            </OptimizerStatsUsage>

This shows that the IX_Category index is being used. More specifically, the optimiser is using the statistic to estimate the number of rows that will be returned, which it then uses to influence its choice of execution plan.  
 
Armed with these accurate estimates, SQL Server chooses an efficient Merge join. The query overall performs just under 30,000 page reads. 

What if we drop the “unused” index?

Thankfully, in most cases, we’ll get exactly the same result. That’s because SQL Server contains a property called AUTO_CREATE_STATISTICS. When turned on (the default), SQL Server will detect that it is missing a statistic, and automatically create the statistic on the fly, allowing it to produce good estimates.  
 
However, if this feature is turned off, the impact of dropping an “unused” index can be significant.  

Example 2

Let’s turn off AUTO_CREATE_STATISTICS, drop the indexes and re-run the query.  

				
					ALTER DATABASE [MYDB] SET AUTO_CREATE_STATISTICS OFF; 
GO 
 
DROP INDEX IX_Category ON IndexStatsDemo; 
GO 
DROP INDEX IX_Category ON IndexStatsDemo2; 
GO 
 
SELECT * 
FROM IndexStatsDemo t1 
    INNER JOIN IndexStatsDemo2 t2 
        ON t1.Id = t2.Id 
WHERE t1.Category = 1 
OPTION (RECOMPILE);
Re-run of query for unused index

Notice that the estimates are now way off! Without the benefit of the statistics, SQL Server is guessing that just 1,000 rows will be returned by each operator. As a result, the optimiser chooses a less-efficient Nested Loop join rather than a Merge join. Our query now performs almost 3,000,000 page reads, and the cost threshold is high enough that SQL Server now chooses a parallel plan. 

Summary

Dropping unused indexes is a good idea. It will speed up your data modifications and reduce the size of your database.  

However, it’s important to be aware that the index could have associated statistics that are being used, and dropping these could negatively affect the performance of your queries.  

This is particularly important if AUTO_CREATE_STATISTICS is turned OFF, in which case, be sure to manually run regular updates of your statistics with full scan to avoid performance problems. 
 

Thanks for reading!