Rebuild index on a 5TB database

Question

We have 5 TB database and the rebuild index job is taking days to complete. Is there a way to rebuild indexes on chunks of tables/indexes. For eg, rebuild index on first chunk, then on the next chunk and so on. Please let me know

Answer 1

Hi @Chaitanya Kiran ,

We do not rebuild all index, only Rebuild or Reorganize the necessary Indexes, this depends on fragment percentage as below screenshot. We can using sys.dm_db_index_physical_stats to detect fragmentation. Refer to Detecting the amount of fragmentation.

For rebuild all indexes associated with the table, please refer to Rebuilding all indexes on a table and specifying options.

Please refer to blog Reorganize and Rebuild Indexes in the Database to get more details information.

Best regards,
Cathy

===============================================

If the response helped, do "Accept Answer" and upvote it.

Answer 2

Hi @Chaitanya Kiran ,

Starting with sqlserver 2017 online index rebuilds are resumable. You can set the max_duration option in the alter command to stop it after the delay of your choice, then resume the operation.

See : https://www.mssqltips.com/sqlservertip/4987/sql-server-2017-resumable-online-index-rebuilds/

Regards,

--
Begin