1

I am referring the Unused Index Script blog.

I got to know that if Seek = 0, Scan = 0, Lookup = 0, User Update = 0 then we should delete index as it is not needed.

The unused script gives me many indexes but should I delete all those indexes? Can you please help me to understand to identify actual unused index from the given script.

I would like to know the importance between UserSeek, UserScans, UserLookups, UserUpdates column before I delete.

Unused Index Script - Click here

Dale K
  • 25,246
  • 15
  • 42
  • 71
Yatin
  • 21
  • 5
  • 1
    Given we have no idea which script you are using, nor what the output looks like, its going to be pretty difficult to assist you. – Dale K Feb 17 '20 at 01:18
  • 2
    Please don't use images to post code - post it as text and format it as code so people attempting to assist can copy and paste it out to test with (I'm not going to type that all in again). – Dale K Feb 17 '20 at 01:25
  • Thanks Dale K. I've added script. – Yatin Feb 17 '20 at 01:25
  • The drop index statement (last column of the query) shows you which index is available to be dropped. – Dale K Feb 17 '20 at 01:25
  • In order to better understand the terms you ask about, please google them, there are lots of tutorials and reference documents out there to assist you with that. Ask a question when you read some of the documents and don't understand something specific. – Dale K Feb 17 '20 at 01:26
  • I could see the last column for Drop index syntax. I want to understand that on what basis I should drop the index. I cannot drop all indexes which are coming up in the result. If Seek, Scan, and Update are ZERO then we can drop them. Apart from them How could we make the decision about dropping based on results? – Yatin Feb 17 '20 at 01:33
  • Its very difficult for anyone to advise you on whether you should drop an index or not. The fact that you are asking for help with this suggests to me you don't understand indexes well enough to be dropping any of them. You need to do your own investigation, and determine whether they are beneficial or not. There are plenty of resources out there to help with your understanding e.g. https://www.brentozar.com/archive/2018/10/unused-indexes-are-they-really-unused-or-have-they-just-not-been-used-yet/ – Dale K Feb 17 '20 at 01:36

1 Answers1

1

The seeks, scans and lookups are read operations which are beneficial to query performance. The updates are inserts into or updates to the index. These updates are considered a negative side affect of indexing.

The first and only rule in the black art of indexing is: take a backup of the indexes before you change them. Just script them out and save them for later. Be sure to date the file so you can correlate any database performance degradation.

A few things to understand:

  1. The columns you are referencing (UserSeeks, etc.) are coming from a MSSQL DMV (Dynamic Management View). These metrics are ephemeral, meaning they are reset every time the service is restarted (such as a server reboot). So keep in mind that the seeks, scans, etc will not reflect historical activity.
  2. Even is an index has a lot of updates and few beneficial operations - the beneficial operations may be very important. They (for example) may be responsible for making a query that would otherwise run for an hour execute in a second or so.
  3. Even if you don't see beneficial activity on an index for months, it may still be used by some rarely accessed part of the software that uses the software - like some kind of monthly or yearly maintenance.

So again, backup the indexes before you remove anything.

NTDLS
  • 4,757
  • 4
  • 44
  • 70
  • 1
    Or in the first instance just disable them. – Dale K Feb 17 '20 at 01:33
  • Thanks NTDLS and DaleK. Can I consider to delete index if I dont find any value in Seek but there are value in Scan and Update ? – Yatin Feb 17 '20 at 02:58
  • 2
    @Yatin updating an index happens when a row is inserted/updated which matches the criteria of the index, but thats not considered using the index, thats maintaining the index so its up-to-date. So you can ignore this from the perspective of whether the index is being used. However if a query is using using an index for a scan then performance may change if you remove that index. This is what I mean, you need a better understanding before you even consider dropping indexes because the effects could be drastic. – Dale K Feb 17 '20 at 03:22