DBCC DBReIndex

Being day nine of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC DBREINDEX.

Microsoft recommends avoiding the use of DBCC DBREINDEX.

DBCC DBReIndex has been deprecated meaning that Microsoft has announced that DBCC DBReIndex will be going away in future versions of SQL Server

Over time as you insert, update and delete from tables various clustered and non-clustered indexes will end up getting fragmented. Fragmented means that the contents of the index is scattered over more pages than it could be, and that the pages holding the index are partially full. DBCC ReIndex (or the alternative offered below) reorganizes the index data in those pages so that there is less waste, less fragmentation, and all of the index data is packed into the data pages tighter. Over time the index may become fragmented again.

DBCC DBREINDEX Syntax:

dbcc dbreindex
(
    'table_name'
    [ , 'index_name' [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]

Alternative to DBCC DBReIndex

There is more functionality in the ALTER INDEX than in DBCC DBReIndex.

The following example shows how to use ALTER INDEX insted of DBCC DbReIndex.

</pre>
ALTER INDEX [ClusteredCPU]
 ON [dbo].[CPU]
 REORGANIZE WITH ( LOB_COMPACTION = ON );
<pre>

Detecting Fragmented Indexes

The Index Fragmentation Report in the Database Health Reports project can be used to find fragmented indexes as shown here.

Additionally with the Database Health Reports project, the Fragmented Indexes Advisor can help you de-fragment those indexes, rather than using the obsolete DBCC DBReIndex as shown here

Notes:

Use ALTER INDEX instead of DBCC DBReIndex.

For more information see TSQL Wiki DBCC dbreindex.

DBCC Command month at SteveStedman.com is almost as much fun as creating a quilt.