SQL SERVER – Stop Using DBCC DBREINDEX and Use ALTER INDEX

It has been over a decade since the comment DBCC DBREINDEX has been deprecated, however, once in a while I still encounter them while working with clients on Comprehensive Database Performance Health Check. The other week, while going over the maintenance plan, I noticed that they are still using the older syntax instead of using the new syntax of ALTER INDEX.

Say No to DBCC DBREINDEX

I have been very clear for many years that if any feature is marked as deprecated and the replacement feature surfaces, one should always start planning the transition. It really does not make sense to keep using the feature which will be eventually removed by the product team.

However, I usually get a little resistance when I try to ask users to use a new feature instead of the feature they are using for many years. I totally understand the philosophy of If it ain’t broke, don’t fix it.

There are many reasons to switch from DBCC DBREINDEX and Use ALTER INDEX. Here are three major limitations of the DBCC DBREINDEX.

• It does not support online rebuild option
• No support to resumable indexes
• No support for data compression

It is not that it does not support only the above three options but many other enhancements since SQL Server 2008 released.

Syntax of ALTER INDEX

Here is the syntax of the ALTER INDEX Rebuilding index.

ALTER INDEX IndexName ON TableName REBUILD;

It is a very simple syntax. Here is another syntax for reorganizing the index.

ALTER INDEX IndexName ON TableName REORGANIZE;

Well, that’s it for today. Here is my question to you – do you still use DBCC DBREINDEX for rebuilding your indexes. Is there any particular reason for continuing to use the feature which has been marked as deprecated for so many years? It will be useful to know the reason for everyone and I request you to post your answer as a comment to this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)