By: Eli Leiba | Updated: 2017-03-27 | Comments (6) | 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 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.
- 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
About the author
View all my tips