Post reply

Sql Server - Index ReBuild

  • July 11, 2013 at 11:39 pm

    #277272

    I will be planning to do the Rebuild/ Reorg Indexes as my client never had done this and never run update statics.

    But my problem is that we have replication DB and it's runs very fw minutes as we are subscriber.

    If I do the Rebuild Index and it will lock the table during maintenance.

    We are running some evening and nightly jobs too.

    Replication DB is Big around 200 GB, will be a problem if i do the Index maintenance during week end and publication running?

    I have already ran the script to identify the Index fragmentation and know which index needs to be Rebuild or Reorg.

    Thanks,

  • July 12, 2013 at 1:31 am

    #1631826

    Well to cope with this, MS have enabled online index rebuild/reorganize without placing locks on table available only in Enterprise Edition.

  • July 12, 2013 at 5:45 am

    #1631892

    Hi,

    Rebuilding indexes will not affect the replication database. You can do it offline and online as well.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • July 12, 2013 at 3:07 pm

    #1632133

    Thanks both of you.

    Is it Online will not be helpful to prevent locking?

  • July 12, 2013 at 5:26 pm

    #1632151

    It is widley not know that Online Index Rebuild also does takes very short table level locks. A shared lock at the start of the operation(called Preparation Phase) and SCH-M at the end of the operation(called Final Phase). During the build(called Build Phase) it takes xclusive locks on the rows that are being moved to the new Index structure that is created. The batches of rows that are being moved from old to new index are xclusively locked during the build phase. Hence any DMLs that are hitting these rows will be blocked. But it is still better than the default offline operation which locks the entire index structure making it totally unavailable. The below is a link to white paper that expalins in details the online index operation.

    http://technet.microsoft.com/library/Cc966402

    Regards,

    Nawaz.

  • July 15, 2013 at 6:00 am

    #1632425

    Thanks NAvaz.

    We have Sql 2005 - standard so we can't do online option for rebuilding Index.

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

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