Tables defragmentation

[cctrinh]

Hello,

Can someone tell me which of the following DBCC command I should use to defrag a table in SQL server 2000? DBCC DBREINDEX or DBCC INDEXDEFRAG. I have more then a dozen of tables with an Extent Fragmentation of more then 90%. Also, can I use the two DBCC commands above to defrag table that does not have any index?

Thanks a bunch!

 

[katgirl]

These commands only defrag/rebuild *indexes*, but will also also defrag the table if there's a primary key - because the primary key index and the table rows are the same.

From BOL:

Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.