2

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?

  • 1
    Are you saying your main purpose of rebuilding index is to reclaim space not reduce fragmentation ? Its not guaranteed you can reclaim space I am not sure whether it will do it – Shanky Jan 12 '15 at 17:16
  • Yes, that is the main purpose. It is working. For now, I am testing this on local databases, and I want to reduce the execution time before doing this on produciton. – gotqn Jan 12 '15 at 21:24
  • You could consider rebuilding index by index instead of all at once. (Or dropping a lot of those 20 indexes and re-creating them one by one.) Are 20 non-clustered indexes really necessary? While that is well below the enforced limit, it seems excessive to me. Also I assume you have chosen to not sort in tempdb for a reason, and to perform the rebuild offline as well? – Aaron Bertrand Jan 12 '15 at 21:52
  • It's very old design. I have reduced the indexes in fact(originally over 30). Is PARTITION = ALL option rebuilding all of them? If I rebuild only the clustered index aren't the non clustered indexes updated only? I can try this - disable or drop the non-clustered indexes, rebuild the clustered index and then recreated the non-clustered indexes. – gotqn Jan 12 '15 at 21:59
  • @gotqn sorry, I didn't look closely enough at the actual statement, I saw ALL and assumed you were rebuilding all indexes. In any case, my question is still valid - just because it's an old design does not mean all those 20 indexes are still being used in today's workloads. – Aaron Bertrand Jan 12 '15 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.