Post reply

Reorganize Very Large Index

  • July 8, 2011 at 7:54 am

    #253414

    Hi,

    We have a clustered index which is very large on a huge table.

    We only have a 6 hour window to reorganize \ rebuild the index which is not long enough.

    If we start the process and kill it after a few hours would the reorganize reduce the fragmentation up to the point of us killing it?

    The idea is that we can chip away at the fragmentation over a number of nights to get it resolved.

    MCITP SQL 2005, MCSA SQL 2012

  • July 8, 2011 at 7:56 am

    #1350364

    Reorganise, yes, rebuild, no

    However if it's very fragmented reorganise may not work well, may take huge amounts of time or log. Can you do an online rebuild?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 8, 2011 at 8:33 am

    #1350382

    Thanks Gail, online rebuild isnt possible as that table has BLOB data. Log is another issue too.

    What we are looking at developing a SSIS package that starts the reorganize. Then whilst that is running another thread within the package will monitor the drive space, log space and execution time and then kill the SPID of the Alter Index when a threshold is met.

    What do you think?

    MCITP SQL 2005, MCSA SQL 2012

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

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