In my prior posts, I looked at adding indexes with Database Engine Tuning Advisor. In this post, I will look at the other side of the coin and delete indexes that are unused or that cost more resources than they save.
Indexes primary use is to find records faster. This comes at a cost because the index must be modified whenever an insert or update occurs. This cost can be reduced by putting all of the indexes on a separate file group located on different physical spindles (drives) than the tables (I will show you how to do this in a later post).
I have recommend adding the recommended indexes until your ISV SQL Server database is within 5 percent of the maximum performance that Database Engine Tuning Advisor identifies. Getting the last 5 percent often means adding so many indexes that index maintenance consumes so many resources that you lose performance.
Transact-SQL (TSQL) statements will identify indexes that are expensive or unused. This detection of expensive indexes uses sampling. You want to have a significant sample of data to perform this on, so this is an good task for Friday evening if your server has been up all week. If your sample is very sparse, you might falsely conclude that some indexes are unused simply because your sample failed to include appropriate queries.
Finding Unused Indexes
Open SQL Server Management Studio located on the program menu.
Connect to the database and then paste the TSQL below. This will return the indexes (with associated tables) that are likely candidates for deletion. The TSQL looks for indexes that are updated but never used, the usage is recorded by these three columns:
SELECT TableName = OBJECT_NAME(s.[object_id]), SchemaName=SCHEMA_NAME(o.[schema_id]) ,IndexName = i.name ,user_updates ,i.is_primary_key FROM sys.dm_db_index_usage_stats s JOIN sys.objects O ON s.[object_id] = O.[object_id] JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 -- Only ISV defined. AND user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND i.name IS NOT NULL -- Ignore HEAP indexes. ORDER BY user_updates DESC
This results in something like what is shown below.
There are a couple of important things to observe:
- Indexes that are primary keys [is_primary_key = 1] should not be deleted. Typically these keys determine the physical write order on the hard drives and impacts performance significantly.
- Indexes that contain ‘_dta_’ in their names are indexes that some prior data tuning advisor sessions have added. Some other indexes are now substituting for searches so they are now pure overhead without any benefit.
- Other indexes are either user or ISV created and are likely candidates for deletion. If an index has a small number of updates then I will usually leave it.
Reminder: Do when SQL Server has been up for a long time
With Trace Logs, you need to record each individual query to do the analysis. The above uses a system table, sys.dm_db_index_usage_stats, that keeps a running tally in memory that always happens. The key behaviors are:
- The information is based on the data since SQL Server started. (This determines your sample size)
- Run the above code after SQL Server has at least one week of data.
- If a table is not modified (updated), it will not be listed. To demonstrate this:
- Run the query.
- Stop SQL Server.
- Restart SQL Server.
- Rerun the query.
- You will get no records -- No information means no action.
- Caution: If your server has automatic updates enabled, add "Analysis Monday Evening" to your calendar, just before "Patch Tuesday." (Microsoft updates are typically released on Tuesday.)
Ready to Run Script Deleting Unused Indexes
The reality is that you may have dozens or hundreds of indexes to delete. To avoid carpal tunnel, I created the script below to delete any index identified above that:
- Is not a primary key
- Has user_updates count exceeding @Mincount .
- I suggest 10,000 as a reasonable number to use for a week's activities.
DECLARE @MinCount int DECLARE @TableName nvarchar(max), @SchemaName nvarchar(max), @IndexName nvarchar(max), @Cmd nvarchar(max) SET @MinCount = 10000 -- Change as appropriate DECLARE PK_Cursor CURSOR FOR SELECT TableName = OBJECT_NAME(s.[object_id]), SchemaName=SCHEMA_NAME(o.[schema_id]) ,IndexName = i.name FROM sys.dm_db_index_usage_stats s JOIN sys.objects O ON s.[object_id] = O.[object_id] JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 -- Only ISV defined. AND user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND i.name IS NOT NULL -- Ignore HEAP indexes. AND user_updates > @MinCount AND is_primary_key = 0 ORDER BY user_updates DESC OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @SchemaName,@TableName, @IndexName WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD= 'DROP INDEX ['+@IndexName+'] ON ['+@SchemaName+'].['+@TableName+'] ' BEGIN TRY EXEC (@CMD) END TRY BEGIN CATCH SET @CMD='Error: '+@CMD END CATCH FETCH NEXT FROM PK_Cursor INTO @SchemaName,@TableName, @IndexName END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor;
Summary of Index Tuning
In this series of posts, we added indexes using the Database Engine Tuning Advisor(DTA) and then identified indexes that are not actively being used. In our example, we found that some of the unused indexes were actually created by the DTA.
- Did DTA make a mistake?
- No, the recommendations were made assuming independence between the indexes, but indexes are often interacting (correlated).
- Is tuning a one time event?
- No, it is at least a once-a-year event, perhaps once-a-quarter initially. Workload and usage patterns constantly change and the indexes need to be adjusted for this changing pattern.