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.

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;

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.

• SQL SERVER – Parameter Sniffing Simplest Example
  In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
• SQL SERVER – Parameter Sniffing and Local Variable in SP
  It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
  You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
  This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
  The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
• Performance and Recompiling Query – Summary
  This post summarizes the entire series of parameter sniffing, performance and recompiling query.

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)