CODEWITHSUNDEEP
sql-serverdatabaseindexing
crazyavengers
crazyavengers

Reputation: 81

Index rebuild on a partitioned table taking long time in SQL Server

I have a table with 300M records. This table is partitioned on a week column. All the indexes on that table are partitioned and having a clustered columnstore index along with 5 non clustered indexes.

Every time we do an index rebuild, it's taking 6 hours and more, but not completing any one index at least and we will need to kill at the end. We don’t have any load in server at that point of time.

We use dbcc reindex with fillfactor of 80.

Please help how to debug further. Thanks in advance.

Upvotes: 0

Views: 1523

Answers (1)

SQLApostle
SQLApostle

Reputation: 590

If the older date partitions are pretty static , you might benefit from partition level index rebuild / reorganize, depending on sql server version.

Link To find index fragmentation at partition level

Ideally , microsoft suggests that reorganize should be used for index fragmentation between 5 and 30 % and rebuild for > 30%

Upvotes: 0

Related Questions