DBCC DBREINDEX and Transactional Replication

I have transactional replication going. The database size is about 27 gigs
and we purge data every week to keep it around that size. Obviously i want to
run dbcc dbredindex after that. What are the performance implications ?
Any gothcas ?
Regards
-srini
Srini,
the choice is between DBCC DBREINDEX and DBCC INDEXDEFRAG. Unlike DBCC
DBREINDEX , DBCC INDEXDEFRAG is an online operation - it doesn't hold locks
long term and thus will not block running queries or updates, however a very
fragmented index might take considerably longer to defragment than to
rebuild. So, if you have a definite window then I'd recommend DBCC DBREINDEX
otherwise DBCC INDEXDEFRAG.
Regards,
Paul Ibison
|||Paul Ibison wrote:
> Srini,
> the choice is between DBCC DBREINDEX and DBCC INDEXDEFRAG. Unlike DBCC
> DBREINDEX , DBCC INDEXDEFRAG is an online operation - it doesn't hold locks
> long term and thus will not block running queries or updates, however a very
> fragmented index might take considerably longer to defragment than to
> rebuild. So, if you have a definite window then I'd recommend DBCC DBREINDEX
> otherwise DBCC INDEXDEFRAG.
> Regards,
> Paul Ibison
On SQLServer 7 you have only DBCC DBREINDEX, so this reduces your
options. But if there are a lot of indices on your tables, you can
reindex them one by one ( or only some of them ) which can be much faster
|||Olivier,
this is a useful caveat, but there is no mention of SQL 7.0 in the original
post.
Regards,
Paul Ibison
|||I guess my question was more like what impact will it have on replication.
The resident DBA tells me that doing dbcc dbreindex will delete an insert
rows on the subscriber(s) obviously causing a lot of headache. Apparently he
has seen this happen. Anyone else see this ?
-sk
"srini" wrote:

> I have transactional replication going. The database size is about 27 gigs
> and we purge data every week to keep it around that size. Obviously i want to
> run dbcc dbredindex after that. What are the performance implications ?
> Any gothcas ?
> Regards
> -srini
|||Srini,
I'm only aware of blocking issues - I've never heard of DBCC DBREINDEX
removing rows. If possible, please try to get more information and post back
because this would be very important if it is reproducible.
Regards,
Paul Ibison
|||Paul Ibison wrote:
> Srini,
> I'm only aware of blocking issues - I've never heard of DBCC DBREINDEX
> removing rows. If possible, please try to get more information and post back
> because this would be very important if it is reproducible.
> Regards,
> Paul Ibison
>
In my experience with sqlserver 7 as primary server ( I hope that stated
this way I won't offend Paul )
dbcc reindex does not produce any insert/delete or log consumption.
But beware you datafiles: typically the table is physically duplicated.
For example, if your datafile has only 100meg left, do not reindex a 1GB
table.
|||> In my experience with sqlserver 7 as primary server ( I hope that stated
> this way I won't offend Paul )
Not at all :-)

> dbcc reindex does not produce any insert/delete or log consumption.
In SQL 2000 I've seen the log impact of DBCC DBREINDEX as high, but it can
minimal if the bulk-logged recovery model is used. This may be another
reason, apart from locking, to select DBCC IndexDefrag as the logging is
much less.
Regards,
Paul Ibison
|||Thanks for the responses. Am aware of the log impact. Was puzzled myself
about the delete/insert rows impact. I have still not been able to duplicate
this
thanks again
-srini
"Paul Ibison" wrote:

> Not at all :-)
>
> In SQL 2000 I've seen the log impact of DBCC DBREINDEX as high, but it can
> minimal if the bulk-logged recovery model is used. This may be another
> reason, apart from locking, to select DBCC IndexDefrag as the logging is
> much less.
> Regards,
> Paul Ibison
>
>