Unused Indexes – Are they really unused or have they just not been used YET?

During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?

Rarely does anyone have enough index usage data to make a decision of whether or not to drop the index. If you are on SQL Server 2012 or higher, index usage data is wiped out when you rebuild the respective index. In earlier versions, index usage data is wiped out when the SQL Server service stops.

What could possibly go wrong?

About 10 years ago, I decided to drop an unused index on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues. I thought I could help the system out if I were to drop the index.

4 days after I dropped the index, I got a call from our NOC that CPU utilization was pegged at 100% for an hour so they were reaching out to the on-call DBA to check it out. I logged in and saw a query using A LOT of CPU. Its WHERE clause matched the index I had dropped. The execution plan showed it was scanning the table.

It turned out that I only had 2 weeks of uptime, which didn’t include the 1st of the month. The query that was causing the high CPU utilization was a report that ran on the 1st of each month.

Don’t be me. Learn from my mistake. Well, that should be plural. I’ve made lots of mistakes in production, including these two.

Saving the data

As the index usage gets wiped out with service restarts and index rebuilds, you should save the data on a periodic basis, such as daily. I’d also recommend saving the data prior to doing index maintenance.

Utilizing the Output option of sp_BlitzIndex when using @Mode=2, I wrote a script to track this data.

Making a decision

It is important to know your workload when deciding to drop an unused index. Are there any monthly, quarterly or annual reports? Is there any large task that doesn’t run very often? When was the last SQL Server restart? When was the index last rebuilt?

If I had known there was a monthly report and that the index usage data only went back 2 weeks, I would have waited until I had more than 31 days of index usage data.

Once you’ve collected enough index usage data for your system’s workload, consider dropping the indexes that haven’t been used in that time. Check the BlitzIndex_Mode2 table for the last time the index had any writes.

You could get a lot fancier with the script, but this gets you started.

Brent says: SQL Server Agent has a way to kick off jobs when SQL Server starts. Wouldn’t it be cool to have a way to kick off jobs when the server was about to shut down? I’d totally add this script to catch index details before someone shuts the server down.

Previous Post
The New Lightweight Query Plan Profile Hint
Next Post
First Responder Kit Release: Fingers Crossed!

9 Comments. Leave new

  • I’m not sure if you guys are in my office, listening to my current issues or if we’re just all in the same boat 🙂 I’ve been having some discussions on tracking exactly this, because we have some tables with huge unused indexes, and I’ve been told to get 12 months worth of data before deleting it. Stealing this, and claiming credit in 5… 4…

    Reply
  • […] Tara Kizer has a warning for people eager to drop “unused” indexes: […]

    Reply
  • alen teplitsky
    October 4, 2018 2:26 pm

    i’ve been dropping unused indexes for years and when i did my searches i always searched by user_seeks=0 and made sure the server had been up for at least a month and the table the index was on had been created longer than 90 days or so ago. We had replicated databases with different indexes and a replication resync would recreate the table and deleting an index on those could be trouble

    indexes with low usage, i’d delete after 180 days or so

    Reply
  • Tibor Karaszi
    October 5, 2018 4:19 am

    FWIW Microsoft fixed the wiping out index usage details when doing rebuild. It doesn’t happen in 2016 or 2017. And in 2012, it is fixed in CU3 of sp3. In 2014 in CU 8 of sp1 and cu 14 of RTM. (I didn’t verify those CU/SP levels myself for 2012 and 2014, from documentation. 🙂 )

    Reply
  • “…on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues.”
    So … how long did it take to put the index back on a 7TB database, a table with 2 billion rows and existing storage/performance issues? And did you work there after the index was re-created? 🙂

    Reply
    • We didn’t end up adding that index back to the system. Instead, we moved the report to another server where there was an automated restore from prod. Didn’t make sense for that report to run on production if it was only monthly, and it didn’t need realtime data.

      Reply
  • PETROS ANDRIKOPOULOS
    November 9, 2018 4:30 am

    Some years ago, a problem occured when I dropped a unique non-clustered index which was not used.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}