Rebuild index on a 5TB database

Chaitanya Kiran 101 Reputation points
2020-08-27T00:56:02.557+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-08-27T03:06:39.633+00:00

    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.

    20695-annotation-2020-08-27-110412.jpg

    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.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. begin 26 Reputation points
    2020-08-27T07:32:42.117+00:00

    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

    1 person found this answer helpful.
    0 comments