I need to rebuild the clustered index of the table that has more then 1.6 millions rows, more then 20 non-clustered indexes and 80 columns in order to reclaim space after two `NVARCHAR(MAX)` columns were deleted. I have try using the following command: ALTER INDEX [PK_A] ON [dbo].[B] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) GO and using `DBCC CLEANTABLE` command. In both cases it takes more then 19 hours. Is there a way to optimize the clustered index rebuild operation? Is it possible when a clustered index is rebuild and the size table is reduced (from 40 GB to 10 GB), the RID to be updated, too and this to cause the non-clustered indexes to be rebuild either.
I have rebuilt much larger tables in much less time, so I am concerned why this is taking so long. I recommend checking Performance Monitor or similar while running. Is there potentially an underlying storage system issue? Rebuilding just the clustered index should not affect the nonclustered indexes because the key is not changing. How many partitions are there? I don't have experience with DBCC CLEANTABLE, but MSDN claims "Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation." The default is to run for the entire table. Perhaps it would be better for you to set a batch size. Since you have 1.6 million rows, you could maybe try batches of 200,000 rows. DBCC CLEANTABLE (DatabaseName, 'SchemaName.TableName', 200000); Just in case you are dealing with multiple index issues at once, you could try recreating the clustered index. If you do that, it is very important that you specify the "DROP_EXISTING=ON" option rather than separately drop and create the clustered index.