SQL SERVER – Improve Index Rebuild Performance by Enabling Sort In TempDB

During recent Comprehensive Database Performance Health Check, the customer put forward a compelling request. They wanted to improve their index rebuild performance as their index rebuilding process is taking forever to complete. When they asked me this, I exactly knew what we needed to do for this customer. We ended up fixing the issue of index rebuild by enabling sort in TempDB.

During my previous consulting engagement with the same customer, we had detected that their TempDB was under significant pressure and we had fixed the issue by applying various performance tuning fixes. Now we knew their TempDB was working great in performance; hence we had an alternative solution in that area.

To improve their index rebuilding performance, we decided to rebuild their indexes in the TempDB.

Here is the script which we used to rebuild the indexes in the TempDB. The script which they were using to rebuild indexes, they had to add only keywords WITH (SORT_IN_TEMPDB = ON) at the end of the same.

Here is the syntax for the same

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

Here is the sample script for one of the index in the AdventureWorks database.

USE [AdventureWorks2014]
GO
ALTER INDEX [AK_Department_Name] ON [HumanResources].[Department] REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON)
GO

When we rebuild our indexes, it uses lots of buffer memory due to a requirement of sorting. When buffer memory is filled up, the index has to use a disk to store the database. If you have your TempDB on a separate disk drive, by enabling the soft in TempDB, you effectively reduce the disk pressure from our database disk and move it to TempDB.

Please note that this method indeed improved performance for rebuilding indexes if your TempDB is on a different disk and adequately optimized. You will see no improvement if your TempDB is also struggling with the performance. Additionally, remember to check that there is enough space in TempDB or you would walk into an entirely different problem.

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