none
Index rebuild and reorganize

    Question

  • Am going to be rebuild  index on my large table that point of time entire transactions where it will be stored?? in tempdb or in any other location

    Saturday, August 24, 2013 6:45 AM

Answers

  • Dear Ravi,

    This is what interests me and mainly why I am writing this post is.

    Shanky Well said J When both SORT_IN_TEMPDB and ONLINE are set to ON, the index transactions are stored in the tempDB transaction log and the concurrent user transactions are stored in the transaction log of the user database. This allows you to truncate the transaction log of the user database during the index operation if needed. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space

    Some Tips

    First As far as I know, the sorting would take place in the tempdb database and be logged in its transaction log, relieving the admin database of such duties. If all conditions were met, this is ideal for performance but my main concern is not letting disk space get out of hand. The downside is the transference of used space from the admin database to tempdb therefore not really reducing the overall footprint of the instance.

    First Need to change the recovery model to bulk logged just before the rebuild operation and back to full afterwards. This will minimally log the rebuild operation thus reducing the amount of log growth.

    Instead of Doing rebuild index on entire the database, please check the Tables Fragmentation levels and Check the Scan density for Each Index of a Table then go for re indexing. This will save your valuable Time and It avoids more over locks with other processes. After completion of Re indexing you can Run Update Stats on overall the Database.

    --This Query is for Entire Database

    use [DB Name]

    Go

    EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?', ' ', 80)"

    --This Query is for Only Table level Re Indexing

    use [DB Name]

    Go

    DBCC DBREINDEX ('Table Name', ' ', 80)

    --DBCC execution completed. 50 seconds

    GO

    EXEC sp_updatestats

    --Statistics for all tables have been updated. 05 seconds

    GO

    If you have any disk space concerns please take the log backup for reducing Growth of LDF size. [Please let me know if i am wrong]


    • Marked as answer by RaviDuggineni Thursday, August 29, 2013 5:22 AM
    Saturday, August 24, 2013 3:43 PM

All replies

  • Hello,

    When you have the Enterprise Edition you can rebuild the indexes with option ONLINE, then the table + index will be still available, after finishing the rebuild the "old" index will be exchanged by the new one.

    If you don't have the Enterprise Edition, then the table will be locked for the hole time = no one can access the table = no transaction on it.

    You can also reorganize an index, this is done index page by page, so you have only always a lock for a short period, the table will be still accessible.

    In both cases you will have heavy I/O workload, which will slow down your SQL Server.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, August 24, 2013 11:02 AM
  • Hello Ravi,

    To add little bit to what Olaf has said Online Index rebuild also takes lock ,two short term lock is taken.Online index operations need to take two very short-term table locks. An S (Shared) table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification – think of it as an Exclusive) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

    So you might get timeout on index rebuild using online feature if index is being utilized.Its best to rebuild index during off peak hours or during maintenance window.

    If you use SORT_IN_TEMPDB option tempdb will be utilized for sorting operation in place of Disk drive on which Index resides.When index is rebuilt snapshot of original index is created and any transaction coming which will use index will seek/search from this snapshot .Original will be dropped and rebuilt.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, August 24, 2013 1:33 PM
  • Dear Ravi,

    This is what interests me and mainly why I am writing this post is.

    Shanky Well said J When both SORT_IN_TEMPDB and ONLINE are set to ON, the index transactions are stored in the tempDB transaction log and the concurrent user transactions are stored in the transaction log of the user database. This allows you to truncate the transaction log of the user database during the index operation if needed. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space

    Some Tips

    First As far as I know, the sorting would take place in the tempdb database and be logged in its transaction log, relieving the admin database of such duties. If all conditions were met, this is ideal for performance but my main concern is not letting disk space get out of hand. The downside is the transference of used space from the admin database to tempdb therefore not really reducing the overall footprint of the instance.

    First Need to change the recovery model to bulk logged just before the rebuild operation and back to full afterwards. This will minimally log the rebuild operation thus reducing the amount of log growth.

    Instead of Doing rebuild index on entire the database, please check the Tables Fragmentation levels and Check the Scan density for Each Index of a Table then go for re indexing. This will save your valuable Time and It avoids more over locks with other processes. After completion of Re indexing you can Run Update Stats on overall the Database.

    --This Query is for Entire Database

    use [DB Name]

    Go

    EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?', ' ', 80)"

    --This Query is for Only Table level Re Indexing

    use [DB Name]

    Go

    DBCC DBREINDEX ('Table Name', ' ', 80)

    --DBCC execution completed. 50 seconds

    GO

    EXEC sp_updatestats

    --Statistics for all tables have been updated. 05 seconds

    GO

    If you have any disk space concerns please take the log backup for reducing Growth of LDF size. [Please let me know if i am wrong]


    • Marked as answer by RaviDuggineni Thursday, August 29, 2013 5:22 AM
    Saturday, August 24, 2013 3:43 PM