Post reply

Index Reorg Vs. Index Rebuild Online

  • May 28, 2014 at 10:57 am

    #291742

    I am seeing the Sch-S + Sch-M deadlocks on our system. They occur because the maintenance online index rebuilds runs into a user/batch process. It seems like this should be pretty rare being that the Sch-M is only held briefly but I guess the high level of activity combined with lots of indexes causes issues.

    Do you think I would have better luck with index reorganize? I profiled both and index reorg does a lot less locking. I could just save rebuilds for the weekend and such.

    Thank You,

    Scott

  • May 28, 2014 at 12:07 pm

    #1717116

    Reorganizing indexes will generate a lot more transaction log activity, as each row moved will need an entry. For highly fragmented tables, it will also take more time.

  • May 28, 2014 at 1:19 pm

    #1717137

    Ugh. Thanks, will keep this in mind.

  • May 28, 2014 at 7:33 pm

    #1717210

    @scott,

    How big is the table?

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

  • May 28, 2014 at 8:02 pm

    #1717215

    Here is an interesting article on the effects of a rebuild vs. reorg. The effects of the article seem pretty consistent whether clustering on a GUID or Int and with or without compression enabled.

    http://blogs.msdn.com/b/timchapman/archive/2012/09/28/index-rebuild-vs-reorganize-the-transaction-log-edition.aspx

    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 5 posts - 1 through 4 (of 4 total)

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