-1

I want to understand space requirements for index maintenance in SQL Server. Basically, what I am trying to find is below:

  • Offline index operation with SORT_IN_TEMPDB = ON

    1. Space required in User DB data file
    2. Space required in User DB log file
      ​ 3. Space required in TempDB data file
    3. Space required in TempDB log file
  • Offline index operation with SORT_IN_TEMPDB = OFF

    1. Space required in User DB data file
    2. Space required in User DB log file ​3. Space required in TempDB data file
    3. Space required in TempDB log file​
  • ​Online index operation with SORT_IN_TEMPDB = ON

    1. Space required in User DB data file
    2. Space required in User DB log file ​3. Space required in TempDB data file
    3. Space required in TempDB log file​
  • Online index operation with SORT_IN_TEMPDB = OFF​ ​1. Space required in User DB data file

    1. Space required in User DB log file ​3. Space required in TempDB data file
    2. Space required in TempDB log file​​
  • ​Index Reorg

    1. Space required in User DB data file
    2. Space required in User DB log file ​3. Space required in TempDB data file
    3. Space required in TempDB log file​
0

That depends on a type of index operation.

If it is REORGANIZE the impact is minimal, but it can be tremendous amount of I/O associated to a process and enormous amount of time.

If you plan to do a REBUILD then you need at least the same space in your data file and in a log file as the size of your index. In other words just triple size of your index.

For instance: You have 1TB database with only one table which is also 1TB in size. If you want to build/rebuild clustered index on it you will need additional 1TB+ in data file and 1TB+ in a log file. Impact on TempDB is minimal.

Be aware that there might be completely different results depending on current average page usage and on a new fillfactor. For instance if you have 60% average page usage and planning to fill pages 100% then for 1TB table you'll need only about 600GB of new space.

Also, can have significantly lower impact on Log file when you use Simple logging with 0% fragmentation.

If you don't like my answer, why wouldn't you test all your scenarios with at least 100GB table and post results here?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.