Share via

Rebuild INDEX sort in tempdb

sakuraime 2,321 Reputation points
2021-02-26T03:24:30.703+00:00

I am using the following to rebuild the index

ALTER INDEX index1 ON dbo.table1 REBUILD WITH (SORT_IN_TEMPDB = ON);

however I find the datafile still grow up many on the current filegroup

and even

ALTER INDEX index1 ON dbo.table1 REBUILD WITH (ONLINE=ON ,SORT_IN_TEMPDB = ON);

I am expected it will only group my tempdb in the above situation .

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,331 Reputation points
    2021-02-26T06:54:30.067+00:00

    however I find the datafile still grow up many on the current filegroup

    That's quite normal. The sorting of data is done in TempDB, but in the data file additional space is required to create the new index structure and in case of clustered index to re-arrange data pages.

  2. tibor_karaszi@hotmail.com 4,306 Reputation points
    2021-02-26T08:29:03.333+00:00

    A rebuild creates a new index. After that was done, the old is droppen. I.e., it is a combination of CREATE INDEX followed by DROP INDEX (internally). So SQL Server need space for both the old and the new index during this operation. If you don't like that, then do REORGANIZE instead.

  3. tibor_karaszi@hotmail.com 4,306 Reputation points
    2021-02-27T17:36:50.543+00:00

    If sort_in_temp is OFF then you need about double space for the index and also space for sorting, while the operation is running. In the same filegroup.

    If sort_in_temp is ON, then the space for sorting is in tempdb instead of the filegroup that the index exist in.

  4. David Browne - msft 3,771 Reputation points
    2021-02-27T18:16:54.37+00:00

    If you want to save some space you can DISABLE the index before you REBUILD it. DISABLE will deallocate all the storage for a nonclustered index.

    0 comments
  5. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-03-01T08:02:26.543+00:00

    Hi sakuraime,
    Whenever an index is rebuilt, besides disk space for both the old (source) and new (target) structures required in their appropriate files and filegroups, temporary disk space is required for sorting, unless the query optimizer finds an execution plan that does not require sorting. When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index.
    Please refer to Disk Space Requirements for Index DDL Operations and Index Disk Space Example for more details.

    Best Regards,
    Amelia