Script to Disable Non Used SQL Server Indexes

By: Eli Leiba | Comments (6) | Related: > Indexing

Problem

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.

Solution

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
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @tblName SYSNAME
   DECLARE @indName SYSNAME
   DECLARE @schName SYSNAME
   DECLARE @disableTSQL VARCHAR(300)

   DECLARE c_unused_indexes CURSOR
   FOR
   SELECT DISTINCT obj.NAME AS ObjectName
      ,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 sch.name <> 'sys'

   OPEN c_unused_indexes

   FETCH NEXT
   FROM c_unused_indexes
   INTO @tblName
       ,@indName
       ,@schName

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

      PRINT @disableTSQL

      --EXEC (@disableTSQL)

      FETCH NEXT
      FROM c_unused_indexes
      INTO @tblName
          ,@indName
          ,@schName
   END

   CLOSE c_unused_indexes
   DEALLOCATE c_unused_indexes

   SET NOCOUNT OFF
END
GO

Example of the procedure usage

Using the above procedure on my Northwind database:

use Northwind
go
exec dbo.usp_Disable_UnusedIndexes
go

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.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips