Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes

Artikel
04/15/2011

[Prior Post in Series] [Next Post in Series]

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:

user_seeks
user_scans
user_lookups:

 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:
1. Run the query.
2. Stop SQL Server.
3. Restart SQL Server.
4. Rerun the query.
5. 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.

Comments

Anonymous
April 15, 2011
Nice articles Ken! It's a very succinct analysis of SQL server performance issues, and how to correct them.
Anonymous
May 07, 2011
The comment has been removed
Anonymous
August 23, 2011
A free Community Edition of SQL Parallel Boost can be downloaded at sqlparallelboost.codeplex.com
Anonymous
September 24, 2013
Thank you for the article, i find it extremely useful.

Freigeben über