DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.
Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.
|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>