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.

• Two Advantages of Sort in TempDB Options
• When to Use sort_in_tempdb for Rebuilding Indexes?

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.