Post reply

Best way to drop indexes in SQL2014

  • July 8, 2015 at 9:27 am

    #302215

    Hi,

    I ran Brent Ozars SQLblitz to check for duplicate indexes. It found quite a few unused unneeded indexes.

    I would like to remove them. What is the best way to remove the unused indexes in 2014?

    I assume I will need to do a full rebuild after I drop?

  • July 8, 2015 at 9:34 am

    #1811012

    Just use DROP INDEX. Since you're dropping them, there's nothing to rebuild.

    John

  • July 8, 2015 at 9:53 am

    #1811029

    John Mitchell-245523 (7/8/2015)

    Just use DROP INDEX. Since you're dropping them, there's nothing to rebuild.

    John

    I thought that was bad...to just drop them. Plus I thought there was a new preferred way in SQL2014.

  • July 8, 2015 at 9:57 am

    #1811030

    Well, you say they're unused and unneeded, so I'd say that dropping them would be the kindest thing. Unless they're clustered indexes, that is, but then they wouldn't be unused and unneeded. I'm not aware of any new way of doing it, but I'd be interested to hear if there is.

    John

  • July 8, 2015 at 10:00 am

    #1811032

    Nope, that is indeed how to remove them.

    You might want to consider renaming them, and letting them sit there for a period of time (say, 3-6 months), to ensure that they aren't actually needed by code that isn't run very frequently. If you have a query with a query hint directive specifying this index, the query will fail. This allows you to quickly restore the index (just change the name back).

    Additionally, some index maintenance routines can fail if they scan for indexes needing maintenance and store it in a table, and then before it can work on the index you rename/drop the index. You'll just need to check that table and delete the unprocessed rows referencing those indexes to insure that it doesn't fail.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 8, 2015 at 10:14 am

    #1811039

    Be sure that they are not used.

    Remember that the data being used by sp_blitzindex is re-set after every restart of SQL server.

    So, if the server was re-started recently, these simply may not have been used yet.

    As a example, on my system, 34 indexes appear as unused from sp_BlitzIndex.

    The server was re-started on June 27th.

    Some of these are on tables that are only queried once or twice a month, or worse, per quarter.

    Some of these are on tables that are not being used yet. We put a new set of tables in place to support new code. Operations will not start doing this until 8/1

    sp_BlitzIndex is a great tool. But it is a starting point for index analysis, not the ending point.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 6 posts - 1 through 5 (of 5 total)

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