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.
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019
