Post reply

Help with non-clustered index fragmentation

  • March 15, 2021 at 1:36 pm

    #3857997

    I'm trying to rebuild indexes to clear some heavy fragmentation in the nonclustered indexes.  I've tried

    ALTER TABLE dbo.POHeader REBUILD

    and

    ALTER INDEX idxPOHeader_EntryDate ON dbo.POHeader REBUILD

    Neither affects the index fragmentation.

    What is the correct method to resolve this.

     

    Attachments:
    You must be logged in to view attached files.

  • March 15, 2021 at 3:15 pm

    #3858035

    You may not be rebuilding that specific index.

    The easiest way to rebuild all indexes on a table is:

    ALTER INDEX ALL ON dbo.POHeader REBUILD

    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".

  • March 15, 2021 at 4:11 pm

    #3858065

    That seems to have done the trick.  Thanks!

  • March 15, 2021 at 5:21 pm

    #3858111

    rando wrote:

    That seems to have done the trick.  Thanks!

    Be careful of "the trick".  I may be causing unnecessary rebuild of the clustered index and a wad of non-clustered indexes, to boot!  The "ALL" option also means that ALL of the indexes will be rebuilt in a single transaction and that means that if one of the rebuilds fail, ALL of the completed rebuild will be rolled back.

    Also, the "ALTER INDEX idxPOHeader_EntryDate ON dbo.POHeader REBUILD" you posted should have done a rebuild UNLESS the index is actually too small (which varies).  How many pages do each of those indexes actually occupy at the leaf level (Level 0 of the index)?

    Last but not least, doing any index maintenance on indexes that fragment but have a "0" or "100" Fill Factor leads to several heaps (no pun intended) of trouble.

    It's normally page splits that cause fragmentation... doing index maintenance at 0 or 100 or using REORGANIZE removes nearly all free space from the index and so the will refragment on the very next out of order insert or ExpAnsive Update leading to rather severe page splits on the proverbial "morning after".  Consider figuring out what indexes that fragment are actually doing behind the scenes and either pick a correct Fill Factor or fix the columns the index uses so they don't suffer ExpAnsive Updates.

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

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

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