SQL Server Performance Forum – Threads Archive

DBCC DBREINDEX = drop + create index?

I see in BOL that does the same thing than drop + recreate an index. On the other hand, in the description for DBCC SHOWCONTIG, it says: When an index is heavily fragmented, there are two choices for reducing fragmentation: – Drop and re-create a clustered index.
Re-creating a clustered index reorganizes the data, and results in full data pages. The level of fullness can be configured using the FILLFACTOR option. The drawbacks of this method are that the index is offline during the drop/re-create cycle and that the operation is atomic. If the index creation is interrupted, the index is not re-created. – Use DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. QUESTION:
I would like to defrag the table and its clustered index at the same time. What is the best method that you would advise? Thanks very much in advance.

yes, basically, dbcc dbreindex() does a drop and recreate, is my understanding. it’ll lock the table too while it does it. exclusively. but it’s throrough. indexdefrag does as much defragging as it can in an online fashion, so doesn’t block users. not as thorough, more lightweight in terms of impact, and supposedly won’t block users. if you have a maintenance window, i’d go dbcc dbreindex if i were you. otherwise try indexdefrag and see what the results are like. Tom Pullen
DBA, Oxfam GB

Yeap, means drop and recreate.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for your confirmation, I’ll go with DBCC DBREINDEX

Just a little note to tuck away. DBCC DBREINDEX by default will recreate the index with the original fill value you created it with. If you were to get a wild idea and decide to do this yourself with DROP and CREATE index statements, you would need to find out what that original value is everytime you recreate the index, in addition to just running the drop/create statements. Just some useless trivia to tuck away. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Also, sp_MShelpindex will give you the original information and a bunch of other info if you’re interested. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>