Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Safely Dropping Unused SQL Server Indexes


By:   |   Read Comments (3)   |   Related Tips: More > Indexing

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

There are many resources online about finding and dropping unused SQL Server indexes, but there a number of issues related to unused indexes removal. How can we make sure that we are dropping unused SQL Server indexes safely? What are the potential issues?

Solution

Some DBAs probably have an unused indexes review as part of their routine.  Experienced DBAs understand the consequences of index removal and the potential issues. In this tip, we will review some of these potential issues and we will provide you with a couple of examples when you should just leave the indexes as is (even if they are unused).

Understanding SQL Server index usage statistics

Index operations statistics (for example "last seek", "last update") are returned by the sys.dm_db_index_usage_stats dynamic management view (DMV). These statistics are reset when SQL Server is rebooted, a database is taken offline, an index is dropped or re-created. Read this article about what resets sys.dm_db_index_usage_stats DMV. The article includes references to the bug in specific versions of SQL Server when index usage statistics are reset during index rebuild operations.

Keep in mind that sys.dm_db_index_usage_stats DMV doesn't have information about memory-optimized indexes.

First Problem - Incomplete Queries to Find Unused Indexes

Most of the queries provided from different internet resources look similar to this one:

SELECT 
   DB_NAME() AS [database_name], 
   DB_ID() AS database_id,
   OBJECT_SCHEMA_NAME(i.[object_id]) AS [schema_name], 
   OBJECT_NAME(i.[object_id]) AS [object_name], iu.[object_id],
   i.[name], i.index_id, i.[type_desc],
   iu.user_seeks, iu.user_scans, iu.user_lookups, iu.user_updates
FROM sys.dm_db_index_usage_stats iu
RIGHT JOIN sys.indexes i ON iu.index_id = i.index_id AND iu.[object_id] = i.[object_id]
WHERE 
   OBJECTPROPERTY(iu.[object_id], 'IsUserTable') = 1
   AND iu.database_id = DB_ID() 

Some of them have additional filters and others don't. We will review some of these filters and we will see what are the advantages and disadvantages of others.

What's missing in our query above? It doesn't have an important filter which can prevent you from making a wrong decision and from breaking your application:

... WHERE ...
... AND i.type = 2 AND i.is_primary_key = 0 -- type = 2 is nonclustered index
    AND i.is_unique = 0 AND i.is_unique_constraint = 0 ...  
  

The query above is missing Unique and Primary Key constraints exclusions and you can break your application if you don't filter out "unused" indexes that are also Unique and Primary Key constraints.

Make sure you always use the filter above when you check unused indexes.

You have to understand that many scripts on the internet are provided only as a guideline and you have to do due diligence and a lot of testing before you start using them in a Production environment.

Second Problem - Query to Find Unused Indexes has too Specific Recommendations

The query above is a basic script that you can utilize later (once you understand how your indexes are used) for your specific case. It doesn't have a recommendation, for example, to display indexes that are updated more frequently than they are queried. Here is an example of such a filter:

... WHERE ...
... AND iu.user_updates > (iu.user_scans + iu.user_seeks + iu.user_lookups) 
   

The result won't really display unused indexes. These are indexes with update overhead (therefore we are going to refer to these indexes as "unused" going forward).

Here are a couple of scenarios when this filter is not very useful:

  • If we have user_updates = 2 and user_scans + user_seeks + user_lookups = 1 for an index then this index will be returned when the filter above is used. But it doesn't really mean that we have a huge index update overhead. There is just a too small difference between updates and index usage in this case.
  • In the second scenario, we are going to review a report that is critical for the business. Let's assume that this report runs once a month and it's using our index (with overhead). The report without index runs 2.5 hours, with the index in about 15 minutes. Multiply this by a number of months in a year and you will get almost 3.5 working days a year saved. There could be many updates to this index throughout a month, so the condition above will put this index as a candidate for deletion too, but the benefit of having this index might be much higher that then updates overhead.

You need to understand how each "unused" index is used and make a right decision based on more evidence and testing than just querying the DMV.

The filter above isn't good for every scenario, but you can modify it to something like this:

... AND ((iu.user_seeks + iu.user_scans + iu.user_lookups) > 0 
    AND iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups ) > 5)
    OR (iu.user_seeks + iu.user_scans + iu.user_lookups) = 0 ) ... 
   

This will show you "truly" unused indexes (remember that they are unused since last DMV reset, so it doesn't mean the index has not been used before). It will also display indexes where the updates-to-usage ratio is more than 5.

But our recommendation is to skip these filters completely as each index's usage is unique and you can't have one query that fits all indexes usage scenarios.

A good start is a query like this:

SELECT 
   DB_NAME() AS [database_name], 
   DB_ID() AS database_id,
   OBJECT_SCHEMA_NAME(i.[object_id]) AS [schema_name], 
   OBJECT_NAME(i.[object_id]) AS [object_name], 
   iu.[object_id],
   i.[name], 
   i.index_id, 
   i.[type_desc],
   i.is_primary_key,
   i.is_unique,
   i.is_unique_constraint,
   iu.user_seeks, 
   iu.user_scans, 
   iu.user_lookups, 
   iu.user_updates,
   iu.user_seeks + iu.user_scans + iu.user_lookups AS total_uses,
   CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0
        THEN iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups )
        ELSE iu.user_updates END AS update_to_use_ratio
FROM sys.dm_db_index_usage_stats iu
RIGHT JOIN sys.indexes i ON iu.index_id = i.index_id AND iu.[object_id] = i.[object_id]
WHERE 
   OBJECTPROPERTY(iu.[object_id], 'IsUserTable') = 1
   AND iu.database_id = DB_ID()
ORDER BY 
   CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0
        THEN iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups )
        ELSE iu.user_updates END DESC 

Third Problem - Unused Indexes Recreated Over and Over

Here is a real-life example.

One of the applications we had was re-creating database indexes on every application restart. So, even though you dropped the "unused" index it was re-created over and over.

Changing the application indexes (adding included columns, etc.) caused application issues too. So, this is one of the examples where you should keep the vendor's indexes as is.

Forth Problem - "Unused" Indexes are Hard Coded with Index Hints

Here is another real-life example when deleting or disabling "unused" indexes can cause application issues.

If a vendor's application queries have table/query hints to force a specific index usage you will break the application by dropping this index. Here is an error the application will raise:

Dropped index error:msg 308, Level 16, State 1, Line 35 Index
Msg 308, Level 16, State 1, Line 35
Index 'NCIX_TestIndexHint' on table 'BUSINESS_IDS' (specified in the FROM clause) does not exist.
   

If you disable an index you will get the following error:

Disabled index error: Msg 315, Level 16, State 1, Line 35
Msg 315, Level 16, State 1, Line 35
Index "NCIX_TestIndexHint" on table "PWSQL.BUSINESS_ASSOCIATES" (specified in the FROM clause) is disabled or resides in a filegroup which is not online 
   

Here is an example of an index hint:

SELECT e.ManagerID, c.LastName, c.FirstName, e.Title  
FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))  
JOIN Person.Contact AS c ON e.ContactID = c.ContactID  
WHERE e.ManagerID = 3; 

Read this tip about using index hints.

This blog has very good points on why you should use index hints with caution.

Microsoft has this caution for using any hints:

"Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced developers and database administrators."

To summarize - index hints are acceptable on rare occasions, but they have to be monitored and reviewed.

Make sure you don't have index hints before you make any changes to an index. You can find the query to find index hints here or start with this script:

SELECT OBJECT_NAME([object_id]) objectName,
SUBSTRING([definition], CHARINDEX('WITH', [definition], 1), 50) IndexHint
FROM sys.sql_modules
WHERE REPLACE(REPLACE(SUBSTRING([definition], CHARINDEX('WITH', [definition], 1), 50)  , ' ', ''),' ', '') LIKE '%WITH(INDEX%'

Fifth Problem - Not Enough Evidence that Dropping Unused Indexes will Improve Performance

Test. Test. Test.

  • Test how long a query using "unused" index is running with the index.
  • Test how long a query using "unused" index is running without the index.
  • Test the index updates overhead and have numbers that justify performance degradation.
  • Weigh... benefits of having the index and disadvantages related to the updates overhead.

One of the reasons provided to drop "unused" indexes is to save a disk space. Storage these days becomes cheaper and cheaper, so it's not as big of a deal as it was before. Also, with data compression, you can save disk space in some cases. Read about effects of using data compression here. Data compression was a SQL Server Enterprise Edition feature up to SQL Server 2016 SP1. Now you can compress your data on SQL Server 2016 SP1 Standard Edition. Find supported features here for different editions of SQL Server 2016.

So, storage shouldn't be the main reason for index removal (unless you have disk space constraints). You may need to concentrate on CPU and memory impact during index updates.

Conclusion

We have provided just a couple of examples of non-standard index usage by the vendors, but I am pretty sure there are other scenarios that we don't know about.

Be very careful before you decide to drop indexes (especially on vendor's databases). Make sure that application is still supported if you drop them. Some vendors won't support an application after any change has been made to the database (including indexes modification).

Based on all we have discussed, here are recommendations for "unused" index removal planning:

  • Drop only indexes that were created by you or your team.
  • Always document indexes you create (especially indexes with high impact).
  • Make sure indexes are not re-created by an application or an automated process.
  • Validate that there are no index hints (see the query reference above).
  • Make sure you scripted indexes including all properties (fill-factor, compression, etc.) before dropping them.
  • You may try to disable indexes first before dropping them:
ALTER INDEX [NCIX_TestIndexHint] ON [PWSQL].[BUSINESS_ASSOCIATES] DISABLE
GO

An easy way to generate a rollback query for your change will be a query like this (assuming there were no other disabled indexes before):

SELECT 'ALTER INDEX [' + [name] + ']
ON [' + OBJECT_SCHEMA_NAME ([object_id]) + '].[' + OBJECT_NAME([object_id]) +'] REBUILD'
FROM sys.indexes
WHERE is_disabled = 1

Rebuild indexes before disabling them to make sure there is no fragmentation. If there is a fragmentation then indexes might be enabled again during index maintenance task. A good idea is to include a check for disabled indexes to the index rebuild task.

  • Track index usage for at least 6 months (better 12 months - to cover year-end reports, etc.). Create an index usage history table in your DBA database.
  • Don't drop indexes just because they are unused or have overhead. Have a solid performance degradation case. Start from a bad performing process, not with index dropping. Commence with a slow query that has large overhead on index updates and then find out what the benefits are of an index with overhead.
Next Steps
  • Read about using the INDEX and FORCESEEK Query Hints in Plan Guides here.
  • Read this article about query hints and this article about table hints.
  • Check this article to find out about unused index vs. an un-indexed foreign key.
  • Here are some other DMV tips.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, December 11, 2017 - 10:15:15 PM - Svetlana Golovko Back To Top

That's a good point, Jeff. 

Thanks for your comments.


Monday, December 11, 2017 - 9:26:56 AM - Jeff Moden Back To Top

Great article.  You've covered a lot of the problems that a lot of folks aren't aware of.

 

Shifting gears a bit and having to do with the "Not enough evidence" section near the end of the article, saving disk space isn't the only thing to be concerned with... there are actually "hidden" peformance issues to content with.  Every index has it's own set of statistics and those need to be maintained.  Having unused indexes may (usually) lead to unused statistics being maintained and those can take a heck of a long time on larger tables.  There's also the problem with the fact that most people don't exclude supposedly unused indexes from their defrag routines and that can cause a lot of wasted time, as well.


Thursday, December 07, 2017 - 4:00:05 PM - Alen Back To Top

 

 I've had good results dropping unused indexes on subscribers. I've also done it on BI servers where some tables were created with dozens of indexes. It's an easy way to clear up space in a DB in a hurry and when you can't just add more storage on demand


Learn more about SQL Server tools