What is the Replacement of DBCC DBREINDEX? – Interview Question of the Week #263

Question: What is the Replacement of DBCC DBREINDEX?

Answer: The answer to this question is very simple.

What is the Replacement of DBCC DBREINDEX? - Interview Question of the Week #263 dbcc-reindex-800x298

Here is the syntax of the ALTER INDEX Rebuilding index.

Solarwinds
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;

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 older syntax of DBREINDEX 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.

There are many reasons to switch from DBREINDEX and Use ALTER INDEX. Here are three major limitations of the 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.

Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.

I suggest you go through the blog posts and understand what is parameter sniffing and how you can overcome it by using various different solutions. If you have any questions, reach out to me on twitter.

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

Solarwinds
, , , ,
Previous Post
Personal Technology – Blinking or Flashing Yellow Light in Alexa – Solution

Related Posts

Leave a Reply

Menu