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.

SQL SERVER - Improve Index Rebuild Performance by Enabling Sort In TempDB sortintempdb-800x208

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]

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

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

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)

SQL Index, SQL Scripts, SQL Server, SQL TempDB
Previous Post
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
Next Post
[Exclusive] Practical Real World Performance Tuning – Live Training Session for Limited Time

Related Posts

1 Comment. Leave new

  • I’m testing the SORT_IN_TEMPDB option but in our first tests we can see that user database logs (it’s in recoverymode=full because using log shipping) grows at the same speed as if we set SORT_IN_TEMPDB = Off.


Leave a Reply