SQL SERVER – Speed Up Index Rebuild with SORT IN TEMPDB

During the recent Comprehensive Database Performance Health Check my client asked me how to speed up index rebuilding. Well, here are few blog posts that I have written about SORT IN TEMPDB which can be started on this topic.

SQL SERVER - Speed Up Index Rebuild with SORT IN TEMPDB SORT-IN-TEMPDB-800x252

If your index rebuilding taking a huge amount of time and it is running very slow you can for sure consider using the keyword SORT_IN_TEMPDB. However, we must remember that you will get maximum advantage only when the log file of your database and log file of the temp DB is not competing for the disk speed and place. There should not be any conflict or contest between them.

If you have a TempDB on the same drive as the user database, it is quite possible even though you have used the keyword while rebuilding your index, you will not get the necessary performance improvement.

Here is who you can use the Sort In TempDB keyword while you are rebuilding your index.

ALTER INDEX [NameOfTheIndex] ON [SchemaName].[TableName]
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON)
GO

Are you using this keyword when you are rebuilding your index? If yes, please share your thoughts. I would like to know how much of the performance gain one is gaining using this function. You can also reach out to me with your comments on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts, SQL Server, SQL TempDB
Previous Post
SQL SERVER – Finding Fragmentation in Forwarded Records
Next Post
SQL SERVER – RESOURCE SEMAPHORE and THREADPOOL

Related Posts

2 Comments. Leave new

  • Thanks for share this, just a question, this option could increase the size of the tempdb with big indexes?

    Reply
  • Good! This will increase the tempdb size like it does with the log file?

    Reply

Leave a Reply