Questions on Index Rebuilding (not reorganize)

  • Hi Guys,

    Need some information on Index Rebuilding. (sort_in_tempdb = off)

    When performing index rebulding (for indexes > 30% fragmented), will we expect to see an increase in the user transaction log size (LDF)? Or we will expect to see an increase in the database size instead (MDF)?

    Anyway to calculate how much size needed for rebuilding indexes?

    Let's say if i need to rebuild 3 indexes, each about 5 GB.

    After rebuilding the first index, will the temporary space used for rebuilding the first index be reuse for the 2nd index? Or we first need to perform a transaction log backup before the space can be reused?

    thanks!

  • Db file: can grow to accomodate new index before dropping old one, so it need a free space of size the largest index that is rebuild.

    DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less

  • thanks.

    Db file: can grow to accomodate new index before dropping old one, so it need a free space of size the largest index that is rebuild.

    DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less

    I supposed the above is when rebuilding online?

    With the offline option, is it the same as above?

    thanks

  • anyone can advise why it need to much space for transaction log? i thought the additional spaces required in the mdf file to store the additional index during time of rebuild is all that it need.

    DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less

    thanks!

  • chewychewy (6/3/2013)


    anyone can advise why it need to much space for transaction log? i thought the additional spaces required in the mdf file to store the additional index during time of rebuild is all that it need.

    It's a fully logged operation, hence every single aspect of the change has to be logged in full recovery model, that means all the index pages get logged as they are allocated.

    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
  • chewychewy (6/3/2013)...

    I supposed the above is when rebuilding online?

    With the offline option, is it the same as above?

    thanks

    No, in both case space for a new copy of index is required.

    ONLINE = you can perform DML on the underlying table. OFFLINE = you can't perform DML on the underlying table.

    For lowering log space usage (log backup size will be almost the same) you can consider the Bulk-Logged recovery mode, but first read about bulk-logged recovery mode.

  • Thanks all for the help.

    One last question, in this case with rebuild offline, i guess select

    statement wont get impacted since the old index is there when creating the new index? Btw any gd query to find a listing of indexes size?

  • No, OFFLINE = offline.

    "Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation."

    http://msdn.microsoft.com/en-us/library/ms188388%28v=sql.90%29.aspx

  • Thanks. In the url it stated that when rebuilding offline, select statement

    still can be performed on the table. Will it be slower? As in will the old index still

    usable while rebuilding going on for select statement?

  • When you're rebuilding offline, the index will be unavailable, the rebuild process takes a schema modification lock, which blocks all access to the table.

    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
  • It's worth noting that ALTER INDEX........WITH (ONLINE = ON) while it's not a magic button, it is an excellent option though.

    When rebuilding indexes online, short lived, shared locks are still taken at the beginning and end of the rebuild process.

    Regards

  • chewychewy (6/4/2013)


    Thanks. In the url it stated that when rebuilding offline, select statement still can be performed on the table.

    As the url stated, this only applies to NON-clustered indexes. When rebuilding a CLUSTERED index offline, a select on the table will be blocked.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Hi,

    Could you please send me the scripts to how to find fragmentation and removing.

    Regards

    Dileep

  • We have lots of scripts to find fragmentation:

    http://www.sqlservercentral.com/search/?q=fragmentation&t=s

  • thank u all for ur kind help!

Viewing 15 posts - 1 through 15 (of 18 total)

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