Post reply

Removing Unused Indexes - Did it cause my performance problem?

  • July 5, 2012 at 10:10 am

    #258848

    So I've used a script in the past found here;

    From Brent Ozar

    http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

    I located several indexes that had 0 reads and over 200000 writes.

    I removed them.

    I almost immediately noticed that things started running slower (a lot slower).

    Users were complaining. After an hour or so I issued the command;

    DBCC freeproccache and things seem to be back to normal - we are now also on our lunch slowdown.

    Can anyone offer some insight into what the issue may be been?

  • July 5, 2012 at 10:19 am

    #1509213

    It is possible that the removal of those indexes caused your slowdown.

    Do you have the indexes scripted so you can re-create them just in case?

    Here is another script that I use for finding these types of indexes. I like to proceed very cautiously whenever dealing with any of these "seldom used" indexes. Read the comments on the article to see why.

    http://jasonbrimhall.info/2012/03/20/seldom-used-indexes/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 5, 2012 at 10:21 am

    #1509215

    Not all "unused" indexes are actually unused. Some may be UNIQUE indexes or enforce other constraints and the optimizer can make certain optimizations just knowing that there are certain constraints on columns.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2012 at 10:39 am

    #1509226

    I do have the indexes scripted so I can recreate them. But it appears that things are now running fine, after running DBCC FREEPROCCACHE. Not sure if its related or not but things now seem to be running fine and I didn't recreate the indexes.

  • July 5, 2012 at 10:58 am

    #1509240

    I think before dropping the indexes it is better to diable index first. It is equivalent to drop index in the terms that index is not maintained ,it is not used by the optimizer.However,its definition remains in the database.

    Thus Disbale the indexes and see and wait for sometime and once everything seems smooth then you can drop the indexes.

    In case things go bad just use the alter index rebuild.

    http://technet.microsoft.com/en-us/library/ms177456.aspx

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 5, 2012 at 11:03 am

    #1509242

    Jeff Moden (7/5/2012)

    Not all "unused" indexes are actually unused. Some may be UNIQUE indexes or enforce other constraints and the optimizer can make certain optimizations just knowing that there are certain constraints on columns.

    I agree with this. I proceed very cautiously when removing any indexes.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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