Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to Disable Non Used SQL Server Indexes


By:   |   Updated: 2017-03-27   |   Comments (6)   |   Related: More > 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.


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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.

 

 


Learn more about SQL Server tools