locked
Online rebuild index RRS feed

  • Question

  • for online rebuild index , I am reading the following links

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

    And see if which part will be using tempdb ? source ? target ?

    Sunday, August 16, 2020 8:06 AM

Answers

  • Hi sakurai_db,

    The Snapshot defined in Preparation phase. That is, row versioning is used to provide transaction-level read consistency.

    The tempdb is used to hold:

    • Temporary user objects that are explicitly created.

    • Internal objects that are created by the database engine, such as: Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified).

    • Version stores. There are two version stores: a common version store and an online-index-build version store. The version stores contain:
      Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
      Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    Please read the tempdb Database for more details.

    Best regards,
    Cris


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 9:05 AM

All replies

  • I believe that the only thing that always will take up space in tempdb is the row versioning for the snapshot taken in the preparation phase.

    If you also specifi SORT_IN_TEMPDB=ON, you will obviously use more tempdb space.


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

    Sunday, August 16, 2020 9:13 AM
  • Hi sakurai_db,

    The Snapshot defined in Preparation phase. That is, row versioning is used to provide transaction-level read consistency.

    The tempdb is used to hold:

    • Temporary user objects that are explicitly created.

    • Internal objects that are created by the database engine, such as: Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified).

    • Version stores. There are two version stores: a common version store and an online-index-build version store. The version stores contain:
      Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
      Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    Please read the tempdb Database for more details.

    Best regards,
    Cris


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 9:05 AM