Post reply

Fragmented Index - Zero Usage - Zero Updates

  • December 14, 2014 at 8:44 pm

    #293572

    Howdy,

    This is a question more out of curiosity than anything else.

    I have produced a report on several Indexes in regards to their fragmentation levels and several have no seeks, scans lookups or updates but are fragmented. Just wondering how an index could become fragmented it it's never used, including updates.

    Yes I know there is no point having these indexes but I can't remove them as it would void warranty as it's a vendor supplied DB. I'm just interested to know how them became fragmented with zero usage. Internal operations on the datafile maybe? I have not shrunk the datafile btw, as I know this causes indexes to become fragmented.

    Thanks,

  • December 14, 2014 at 10:34 pm

    #1764645

    ReamerXXVI (12/14/2014)

    Howdy,

    This is a question more out of curiosity than anything else.

    I have produced a report on several Indexes in regards to their fragmentation levels and several have no seeks, scans lookups or updates but are fragmented. Just wondering how an index could become fragmented it it's never used, including updates.

    Yes I know there is no point having these indexes but I can't remove them as it would void warranty as it's a vendor supplied DB. I'm just interested to know how them became fragmented with zero usage. Internal operations on the datafile maybe? I have not shrunk the datafile btw, as I know this causes indexes to become fragmented.

    Thanks,

    In such cases, the indexes were fragmented prior to the last bounce of the SQL Server service for whatever reason because all of those counters are reset when the service starts back up.

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

  • December 14, 2014 at 10:46 pm

    #1764646

    Thanks very much for you reply, that makes sense.

  • December 15, 2014 at 5:01 pm

    #1764828

    Maybe you could DISABLE the indexes if they are truly never used?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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