Post reply

Dropping Unused indices - User_updates?

  • February 10, 2012 at 2:26 pm

    #264368

    I have custom script which pulls data for unused indices.Now i usually try to be extra cautious and drop all the indices which satisfies this condition (user_seek =0 and user_scan=0 and user_lookups=0 and user_updates=0). Now there are tons of indices who value is zero for user_seeks,user_scan and user_lookups but very high on user_updates, would it be safe enough to drop these also? I think it is safe because it is doing extra work by updating these indices, any other thoughts on this?

    Thanks

  • February 10, 2012 at 5:58 pm

    #1445842

    Indexes don't have to be used to be valuable. Having, for example, a unique index where that index is never used might tempt you to drop it. Sometimes SQL Server will make a better execution plan just because it knows rows are unique because of the presence of a unique index.

    I'd be very careful about dropping apparently unused unique indexes.

    --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)

  • February 10, 2012 at 8:53 pm

    #1445857

    Jeff Moden (2/10/2012)

    Indexes don't have to be used to be valuable. Having, for example, a unique index where that index is never used might tempt you to drop it. Sometimes SQL Server will make a better execution plan just because it knows rows are unique because of the presence of a unique index.

    I'd be very careful about dropping apparently unused unique indexes.

    Thanks..Interesting without even doing a seek at the index it would know?

Viewing 3 posts - 1 through 2 (of 2 total)

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