Script to Disable Non Used SQL Server Indexes

By:   |   Updated: 2017-03-27   |   Comments (6)   |   Related: More > Indexing


You want a T-SQL tool that will check my SQL Server database for non-clustered indexes that are not being used and disable these indexes automatically. The tool will find all of these indexes and document them in an output T-SQL script with an "ALTER INDEX...ON...DISABLE;" statement for each non-used index. The purpose is to eliminate index updates on indexes that are not used and therefore not efficient for overall performance.


The solution I found is a stored procedure that I called dbo.usp_Disable_UnusedIndexes. The stored procedure is based on a query of the sys.dm_db_index_usage_stats dynamic view that is joined with sys.indexes, sys.objects and sys.schemas system tables, in order to find the schema, table and index names. The query finds all the non-used indexes for which user seeks, user scans and user lookups are all equal to zero, but the user_updates column is greater than zero. Other conditions that it checks for are that the index belongs to a non-system table and that it is of type non-clustered. The table names must not begin with either 'sys', 'sql', 'fil' or 'que' prefixes. The procedure will not disable an index on a system table even if it's not used and concentrates only on user tables in SQL Server databases. After finding all this data, the procedure scans the cursor composed of the above described query and executes DISABLE INDEX statements for each of the indexes that meet the criteria.

Things to note about this process:

  • The data comes from the sys.dm_db_index_usage_stats DMV.  Whenever SQL Server is restarted the values in this view are reset, so you need to make sure that SQL Server has been running for a while so that you have a good representation of how indexes are used.
  • The index is not dropped, it is only disabled. So if you want to enable the index after it has been disabled you can issue an ALTER INDEX...REBUILD statement.

Here is the T-SQL script of the stored procedure I used to solve this problem.  I have commented out the command that executes the DISABLE INDEX, so you can first see what the output looks like.

CREATE PROC dbo.usp_Disable_UnusedIndexes

   DECLARE @disableTSQL VARCHAR(300)

   DECLARE c_unused_indexes CURSOR
      ,ind.NAME AS IndexName
      ,sch.NAME AS schemaName
   FROM sys.dm_db_index_usage_stats ius
        INNER JOIN sys.indexes ind on ius.index_id = ind.index_id
        INNER JOIN sys.objects obj on ind.OBJECT_ID = obj.OBJECT_ID
        INNER JOIN sys.schemas sch on obj.schema_id = sch.schema_id
   WHERE  OBJECTPROPERTY(ius.OBJECT_ID, 'IsSystemTable') = 0
      AND LEFT(obj.NAME, 3) NOT IN ('sys','sql','que','fil')
      AND LOWER(ind.type_desc) = 'NONCLUSTERED'
      AND ind.is_primary_key = 0
      AND ind.is_unique_constraint = 0
      AND ius.user_seeks = 0
      AND ius.user_scans = 0
      AND ius.user_lookups = 0
      AND ius.user_updates > 0
      AND ius.database_id = db_id()
      AND <> 'sys'

   OPEN c_unused_indexes

   FROM c_unused_indexes
   INTO @tblName

      SET @disableTSQL = 'ALTER INDEX ' + @indName + ' ON ' + @schName + '.[' + @tblName + '] DISABLE'

      PRINT @disableTSQL

      --EXEC (@disableTSQL)

      FROM c_unused_indexes
      INTO @tblName

   CLOSE c_unused_indexes
   DEALLOCATE c_unused_indexes


Example of the procedure usage

Using the above procedure on my Northwind database:

use Northwind
exec dbo.usp_Disable_UnusedIndexes

Here is the output:

ALTER INDEX CategoryName ON dbo.[Categories] DISABLE
ALTER INDEX City ON dbo.[Customers] DISABLE
ALTER INDEX CompanyName ON dbo.[Customers] DISABLE
ALTER INDEX LastName ON dbo.[Employees] DISABLE
ALTER INDEX PostalCode ON dbo.[Employees] DISABLE
ALTER INDEX OrderID ON dbo.[Order Details] DISABLE
ALTER INDEX OrdersOrder_Details ON dbo.[Order Details] DISABLE
ALTER INDEX CustomerID ON dbo.[Orders] DISABLE
ALTER INDEX CustomersOrders ON dbo.[Orders] DISABLE
ALTER INDEX CategoriesProducts ON dbo.[Products] DISABLE
ALTER INDEX CategoryID ON dbo.[Products] DISABLE
ALTER INDEX CompanyName ON dbo.[Suppliers] DISABLE
ALTER INDEX PostalCode ON dbo.[Suppliers] DISABLE

Note: The procedure was tested on SQL Server 2014 Standard Edition and SQL Server 2016 Developer Edition, but should work on other versions as well.

Next Steps
  • Compile and execute the procedure on your SQL Server user databases and check if there are non-used indexes that exist that can be disabled.
  • Learn more about SQL Server Indexes.

Last Updated: 2017-03-27

get scripts

next tip button

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources

Comments For This Article

Thursday, March 08, 2018 - 3:41:45 PM - Brandon Huber Back To Top

Eli thanks for the script! I did notice something odd when I was playing with it though. I wanted to see the counts for ius.user_updates so I added it to the query when testing the output and it seems that there were multiple rows of user_updates for each index. After adding object_id to the sys.indexes join it seems to be working now.

JOIN sys.indexes ind ON ius.index_id = ind.index_id AND ius.OBJECT_ID = ind.OBJECT_ID


Also the statistics on my db were updated recently so I didn't want to drop potentially useful indexes. Maybe a week is long enough to see if it gets used?

JOIN sys.stats st on ind.OBJECT_ID = st.OBJECT_ID

WHERE 7 < DATEDIFF(DAY, STATS_DATE(st.OBJECT_ID, st.stats_id), @Today) --updated over a week ago

Thursday, January 25, 2018 - 11:19:43 AM - Kerry Hauser Back To Top
-->AND LOWER(ind.type_desc) = 'NONCLUSTERED'<-- shouldn't this statement read -->AND UPPER(ind.type_desc) = 'NONCLUSTERED'<--
It works non-the-less but could be confusing when reading through the code.

Monday, March 27, 2017 - 5:19:48 PM - Derek Wallace Back To Top

 Just to say excellent piece of coding and indeed most useful especially when you are receiving code from 3rd party developers. Derek


Monday, March 27, 2017 - 10:38:51 AM - Tom Groszko Back To Top


 OOPS I missed the unique constraint check in the code.



Monday, March 27, 2017 - 10:35:42 AM - Tom Groszko Back To Top

 I think you should also eliminate from the output indexes that are part of a unique constraint.




Monday, March 27, 2017 - 9:19:27 AM - Howard Rosen Back To Top

 I ran the select part on one of my databases and it retrieved a lot of records of tables and indexes that are being used.

Upon further querying of the sys.dm_db_index_usage_stats I discovered that there are records in there with zero  ius.user_seeks, ius.user_scans, and      ius.user_lookups  but there are also records in there where they are not zero. So I don't believe this will really tell me which indexes are not being used.




Recommended Reading

Difference between SQL Server Unique Indexes and Unique Constraints

Building SQL Server Indexes in Ascending vs Descending Order

Script out all SQL Server Indexes in a Database using T-SQL

Creating Indexes with SQL Server Management Studio

How to get index usage information in SQL Server

get free sql tips
agree to terms

Learn more about SQL Server tools