Are there any other ways to defrag indexes and does DBCC
IndexDefrag do as good of job as DBCC DBReindex or
Dropping and Recreating the index?

Ways to defrag an index:
1) DBCC IndexDefrag
2) DBCC DBReindex
3) Drop/Create Index

[quoted text, click to view]

Yes, those are the ways. DBCC DBReindex actually recreates the index(es), so
it is equivalent to Drop/Create Index command. DBCC IndexDefrag has
different algorithm, it does the defragmentation using kind of bubble sort,
so it locks only two pages at a time. This way it does not disturb regular
users so much, but on the other hand it does not guarantee a complete
defragmentation.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

You might find this useful:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp

--

Andrew J. Kelly
SQL Server MVP


[quoted text, click to view]