Using DBCC DBReindex

Increasingly, you can count on most DBCC commands as being deprecated in future versions of SQL. This is one of the ones that will be removed later.

It still servers a purpose. However it can be replaced using the ALTER INDEX command.

The great thing about the DBCC DBReindex command is that it will completely rebuild a totally fragmented or corrupted index. This is likely to happen on a busy production system.

The most basic syntax is:
[cc lang=”sql”]
DBCC DBREINDEX (‘Person.Address’)
[/cc]
This will rebuild all indexes on the Person.Address table with the default fill factor. Optionally, you can also re-index only one index on the table and specify a specific fill factor. To do that, simply add the following two parameters:
[cc lang=”sql”]
DBCC DBREINDEX (‘Person.Address’, ‘PK_Address_AddressID’, 90)
[/cc]
This will only re-index one index, in this case the primary key, with a fill factor of 90%. Meaning 90% of the data pages will be full, leaving 10% for splits.

Something to note with DBCC DBReindex is that it will take your operation offline. Meaning it will block any operations that occur against the table during the duration of the rebuild. There is a way around this. It is to use the ALTER INDEX command.

Here is the alternative.

[cc lang=”sql”]
ALTER INDEX ALL ON Person.Address
REBUILD WITH (ONLINE = ON, FILLFACTOR = 90)
[/cc]

This command will rebuild all the indexes on the Person.Address table with a fill factor of 90%. This will all happen online, meaning that the rebuild will not block existing processes.

This is the best way to rebuild an index using SQL 2005+