October 21, 2004 at 2:45 pm
What's the difference? I have a developer requesting a job doing both. According to BOL,
INDEXDEFRAG...defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance
This is not accomplished by rebuilding the index?
-------
at us, very deafly, a most stares
collosal hoax of clocks and calendars
eecummings
October 21, 2004 at 4:45 pm
The main difference is that DBCC INDEXDEFRAG is an online operation whereas DBCC DBREINDEX is an offline opertaion i.e. while re indexing you cannot access the table.
However you invariably have to rebuild indexes at appropriate intervals to improve the clustering of pages.
So if you are not a 24/7 operation i would just schedule a dbcc dbreindex at appropriate intervals during the middle of the night if however you are i would use dbcc indexdefrag during the quiet times to keep the indexes in reasonable order until an appropriate windwo presented itself to rebuild the indexes
hth
Dave
October 22, 2004 at 12:04 am
DBCC DBREINDEX is similar to CREATE INDEX with DROP_EXISTING, but it will rebuild the index physically allowing SQL Server to assign new pages to the index and reduce both internal and external fragmentation. DBCC DBREINDEX also has the ability to recreate indexes with constraints dynamically, unlike CREATE INDEX with DROP_EXISTING.
The disadvantages of DBCC DBREINDEX is that it faces the problem of causing/facing blocking and DBCC DBREINDEX is executed within a transaction so if it is stopped before completion, you lose all the defragmentation that had been performed.
DBCC INDEXDEFRAG (available in SQL2k) reduces external fragmentation by rearranging the existing leaf pages of an index to the logical order of the index key and internal fragmentation by compacting the rows within index pages then discarding unneeded pages. It does not face the blocking problems of the other techniques but the results of DBCC INDEXDEFRAG are not as complete as the other techniques. This is because DBCC INDEXDEFRAG skips locked pages due to its dynamic nature and does not use any new pages to reorder the index. You may also discover that the time needed by DBCC INDEXDEFRAG is longer than recreating an index if the amount of fragmentation is large. DBCC INDEXDEFRAG does have the advantage over the other techniques due to its ability to work on defragmenting an index while other processes are accessing the index, eliminating the blocking problems of the other techniques.
October 22, 2004 at 7:25 am
We use DBCC DBREINDEX and DBCC INDEXDEFRAG.. we run DBCC INDEXDEFRAG about once a month and DBCC DBREINDEX every 4 months. If we don't run DBCC DBREINDEX periodically, we have problems with bad/poor statistics and as a result very very poor execution plans. The reason is DBCC DBREINDEX also recalculates statistics since it recreates the indexes and DBCC INDEXDEFRAG does not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply