February 9, 2007 at 8:40 am
Hi All,
I am wondering if anyone would know which of the following re-indexing strategies would be faster.
I have tried Create with drop existing, and that currently takes 10hrs, which also means 10 hours of not being able to use is i.e for blocking reasons.
I was wondering whether it was possible to get an estimate of how long dbcc reindex would take or dbcc indexdefrag. Please assume that the table would have the same level of fragmentation
Many Thanks
February 11, 2007 at 6:10 am
Depends on how high the fragmentation level is. One advantage of dbcc indexdefrag is that you can continue to work. A disadvantage is that all it operations are logged, resulting is a large transaction log file.
February 12, 2007 at 5:45 am
is that 10hrs for a table ? Then you need faster disks, more spindles, no raid 5 , and/or data partitioning.
In tests I found indexdefrag to be pretty much useless, I'd only use this where I had absolutley no available window to rebuild an index - then I'd be carefully evaluating how the tables/indexes were structured to avoid fragmentation.
SQL 2005 ( ent ) has some much better index rebuilding, but the main key always is to have lots of fast disks in raid 10 ( absolutley no raid 5 )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply