none
Offline vs Online index operation RRS feed

  • Question

  • As I know, while online index rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index. I would like to know if these operation same for offline index rebuild. Will SQL Server create virtually an index and then swap it with the existing one?
    Tuesday, December 24, 2019 10:29 AM

All replies

  • Hello RaudDBA,

    The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. When you perform data definition language (DDL) operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.

    Source : https://docs.microsoft.com/en-us/sql/relational-databases/indexes/perform-index-operations-online?view=sql-server-ver15

    Mark me as answer if my post helps you.

    Br

    ChetanV


    Tuesday, December 24, 2019 11:17 AM
  • Hi Chetan Vishwakarma. Thank you for your response but that is not what I am looking for. 
    Tuesday, December 24, 2019 11:20 AM
  • OFFLINE operation index rebuild simply drops and recreates the index which means that index rebuild will solve both the internal and external fragmentation

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 24, 2019 11:28 AM
    Moderator
  • As I know, while online index rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index. I would like to know if these operation same for offline index rebuild. Will SQL Server create virtually an index and then swap it with the existing one?

    I don't know the exact physical implementation, but most likely SQL Server builds an entirely new index elsewhere on the disk, and when completed it deallocates the old structure. Since it keeps a schema-modification lock on the table while the rebuild is running, it does need to consider updates to the index while the operation is running. This is the important difference an online rebuild.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 24, 2019 1:32 PM
  • Hi RaufDBA,

    >>I would like to know if these operation same for offline index rebuild

    Index rebuilding works by recreating the index internally,when the index rebuilding is complete,the old index will be replaced and dropped.

    Information about their differences ,you can refer to this article or this thread.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 6:03 AM
  • Hi RaufDBA,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 30, 2019 2:18 AM