By: Svetlana Golovko | Comments (3) | Related: > Indexing
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:
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:
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.
About the author
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
