Understanding SQL Server DBCC DBREINDEX command and its usage

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:

DBCC DBREINDEX

(

tbl_name

[ , indx_name [ , fill_factor ] ]

)

In the syntax,

tbl_name: Specify the name of the table that contains one or multiple indexes that you want to rebuild
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
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:

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
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
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
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.

use [WideWorldImporters]

SELECT Sch.name as 'Schema',

Tbls.name as 'Table',

Indx.name as 'Index',

indexstat.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstat

INNER JOIN sys.tables Tbls on Tbls.object_id = indexstat.object_id

INNER JOIN sys.schemas Sch on Tbls.schema_id = Sch.schema_id

INNER JOIN sys.indexes Indx ON Indx.object_id = indexstat.object_id

AND indexstat.index_id = Indx.index_id

WHERE indexstat.database_id = DB_ID()

and Indx.name is not null

AND indexstat.avg_fragmentation_in_percent > 30

AND Indx.name not like 'FK%'

ORDER BY indexstat.avg_fragmentation_in_percent desc

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

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

use WideWorldImporters

dbcc dbreindex ('Sales.OrderLines')

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.

use WideWorldImporters

dbcc dbreindex ('Application.People','IX_Application_People_IsEmployee',70)

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.

Author
Recent Posts

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

Latest posts by Nisarg Upadhyay (see all)

Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022