Nisarg Upadhyay

Understanding SQL Server DBCC DBREINDEX command and its usage

August 2, 2021 by

In this article, I am going to explain the DBCC DBREINDEX statement, its syntax, and its usage. Additionally, I am also going to cover the differences between the DBCC REINDEX and ALTER INDEX statements.

The DBCC REINDEX statement is used to rebuild one or multiple indexes of the table.

Syntax

The syntax of the DBREINDEX statement is the following:

In the syntax,

  1. tbl_name: Specify the name of the table that contains one or multiple indexes that you want to rebuild
  2. Index_name: Specify the name of the index that you want to rebuild. If you specify the index, then you must specify the table name in the tbl_name parameter. If you do not specify the index name, then all indexes of the table will be rebuild
  3. fill_factor: Specify the fillfactor for the index. The fillfactor is the percentage space that you want to use to store the data on the index page
    1. When we specify the fillfactor in the DBREINDEX statement, it replaces the older value of the fillfactor of the index
    2. When you want to specify the fillfactor, you must specify the table name and index name
    3. If you do not specify the value of the fillfactor, the DBREINDEX statement uses the default value of fillfactor, which is 100

Required Permission

To execute the dbcc dbreindex command, the user must be a member of the sysadmin (Fixed server role), db_owner (Fixed database role), or the db_ddladmin role (Fixed database role).

Important Notes:

  1. The dbcc dbreindex statement is deprecated and will be discontinued in upcoming versions of the SQL Server. It is replaced with the ALTER INDEX statement, which provides more flexibility and configuration options. You can read ALTER INDEX (Transact-SQL) to learn about ALTER INDEX statement. You might want to read Maintaining SQL Server indexes to learn to manage the indexes of the table
  2. The index rebuild operation performed by the DBREINDEX statement is offline. If you are rebuilding a non-clustered index, it places the shared lock on the table to restrict the execution of update statements. If you are rebuilding the clustered index, it places the exclusive lock to restrict the access (SELECT statement) to the table. You can use the ALTER INDEX statement to perform an online index rebuild, configure the MAXDOP for index rebuild operation
  3. The DBREINDEX statement updates the statistics associated with the index, user-created statistics, and auto-created statistics. To update the statistics, it uses the default sampling rate
  4. We can not rebuild the indexes of the following objects using DBREINDEX
    1. System tables
    2. Memory-optimized columnstore index
    3. Spatial index

Examples

To demonstrate the usage of the DBCC DBREINDEX, I am using the wideworldimportors database, so I have downloaded it from here and restored it on my workstation. Let us explore the various examples of the DBREINDEX statement. First, let us populate the list of indexes that has fragmentation higher than 30%. The following query is used to populate fragmented indexes.

List of fragmented indexes

Example 1: Rebuild all indexes of the Sales.OrderLines table

The following command rebuilds all indexes of the Sales.OrderLines table:

Rebuild all index of a specific table

Example 2: Rebuild a specific index of the table with fillfactor

The following command rebuilds the IX_Application_People_IsEmployee index of the Application.People table with fillfactor 70.

Rebuild specific index of a table

Difference between DBCC DBREINDEX statement and ALTER INDEX REBUILD

Following are the differences between the DBCC DBREINDEX statement and ALTER INDEX REBUILD statement:

DBCC DBREINDEX

ALTER INDEX REBUILD

It does not support online index rebuild.

It supports the online index rebuild. This feature is available in the enterprise edition only.

It does not support resumable indexes.

It supports the resumable indexes.

It does not provide an option to use MAXDOP in index rebuild operation.

We can use MAXDOP to rebuild the index.

The statement Updates user-defined, auto-created statistics and it also updates the statistics associated with the index.

It updates the statistics associated with the index. We must create a separate maintenance job to update the user-defined and auto-generated statistics.

Conclusion

As I mentioned, it is a deprecated statement, so in the future version of the SQL Server, it will be discontinued. As a proactive step, we must change all the maintenance plans and the customized script used to reorganize and rebuild the indexes. The statement is a lot simpler to code. You can rebuild all indexes in one statement, so it was very easy to create an index rebuild script using it. If you look at the syntax of the command, you do not get any option to tune the index rebuild task, and the worst part is you cannot use the max degree of parallelism (MAXDOP) option or rebuild the index while the system is online.

Summary

In this article, we explored the DBCC DBREINDEX statement and its usage by demonstrating practical examples. Additionally, we also learned the differences between the DBCC DBREINDEX statement and ALTER INDEX REBUILD statement.

Nisarg Upadhyay
DBAtools, Maintenance, SQL commands, Utilities

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

767 Views