DBCC commands are some of the most misunderstood, yet essential to anyone maintaining a MSSQL database.
I work with MSSQL databases daily and found http://blogs.msdn.com/b/teamcenteronsql/ to be indispensable when trying to determine to use DBCC INDEXDEFRAG or DBCC DBREINDEX.
There are some main points to this MSDN blog...
DBREINDEX works to resolve "extent" or physical fragmentation
DBREINDEX uses shared locking, so only SELECT statements can be used
INDEXDEFRAG works to resolve logical fragmentation, but does nothing for physical or "extents"
Physical disk, i.e. the hard drive, defragmentation can cause "extent" fragmentation in the database, so if we are to scan a disk for fragments and decide to defragment that disk, be sure to run DBCC SHOWCONTIG afterwards to see how the "extent" fragmentation holds up and if you will then need to run DBREINDEX to resolve the issues cause by defragmenting the hard disk.
This information helped me and I hope someone else finds it equally useful.
