Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joshb
Yak Posting Veteran
52 Posts |
 Posted - 2002-06-12 : 14:36:31
|
I have done some searching for information (and found some) on these two DBCC statements but am unable to find the answer to my question. Are these two commands mutually exclusive? If I use INDEXDEFRAG on a regular basis does that mean I never have to use DBREINDEX? I know there are some signifigant functional differences in these two commands but is the end result for each exactly the same. If anyone has some insight or wants to share the practice they follow I would appreciate it.Thanks |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-13 : 09:12:36
|
| Personally, I run both. INDEXDEFRAG deals only with the leaf level of the index. Since leaf level is the level, which suffers fragmentation the most, I run INDEXDEFRAG often (daily). But every once in a while I run DBREINDEX to take care of the intermediate index levels as well. |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-06-14 : 16:10:43
|
| Found this under Transact SQL HELP in Query Analyzer. Look up DBCC REINDEX.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. |
|
|
|
|
|
|
DBREINDEX vs INDEXDEFRAG