Post reply

deleting INdexes

  • March 17, 2014 at 9:02 am

    #305230

    Hi Experts,

    What are precautions and factors need to consider while deleting indexes. We have found more than 500 unused indexes in one of our db and planning to get rid of those.

    Please advise.

  • March 17, 2014 at 9:49 am

    #1697709

    first be sure they are not being used, so remove in a non-prod environment first and test thoroughly.

    Avoid removing non-clustered indexes defined as unique. They may not show up as having being used to satisfy queries, but they may be being used instead to enforce a unique constraint.

    I presume you are not removing any clustered indexes?

    ---------------------------------------------------------------------

  • March 17, 2014 at 10:18 am

    #1697726

    Also make sure your script those indexes \ backup the database before dropping them. If you have issues later bcs of dropping, then they will come handy.

    --

    SQLBuddy

  • March 17, 2014 at 11:41 am

    #1697741

    I think SQLBuddy hit the most important point. Backup all the indexes as scripts first. Dropping or adding an index is a pretty trivial event, but knowing exactly which index to add, was it unique, did it have a fill factor or a filter or INCLUDE columns... Backups are the way to go with that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 17, 2014 at 11:51 am

    #1697744

    And here is a pain-free way to script them all out and store them into a table (for safe keeping) 🙂

    http://www.sqlservercentral.com/scripts/Indexes/101725/

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • March 17, 2014 at 6:50 pm

    #1697829

    Thanks George,sqlbuddy,Grant jessie

  • March 17, 2014 at 6:51 pm

    #1697830

    Thanks George,sqlbuddy,Grant & Jessie

  • March 18, 2014 at 7:31 am

    #1697995

    I would check the uptime of the server. Lot's of indexes might seem unused shortly after a reboot. I'm usually hesitant to do any index changes like this until business communicated cycles have completed.

    Depending on what you are looking at (0 reads vs. just a few reads), I also check if there are any uses in the plan cache.

    If the index is under source control...sweet. If not, get it in there (or script it out), I prefer using Red Gate SQL Source Control and Git

    I would then disable the index and eventually by some business approved length of time, drop.

    I mention these steps here http://www.codepimp.org/2014/02/source-controlling-indexes/[/url] In order to get SQL Source Control to detect the an index being disabled I had to rename it first.

    Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale

  • March 18, 2014 at 7:43 am

    #1698004

    Thanks SQL4GNT

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply