August 10, 2004 at 7:54 am
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
August 10, 2004 at 10:06 am
There are few things to remember, since DBCC REINDEX is not online oeration,
there is possible of timing out from Log reader if you are running that on publisher and
timing out from Distribution agent if you are running that on subscriber. You can set the agent to
retry certain times after the esimated hour for REINDEX and ask to keep retry for those agents.
If you are using SQL2K then, DBCC INDEXDEFRAG might be better solution for you. This is from BOL.
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.
August 11, 2004 at 2:23 am
we have a large database that is fully replicated and log shipped, the dbcc dbreindex never gave us probs with replication but killed the log shipping !! 12 Gb transaction log. The best method is to code the re-index yourself - and put delays in between the steps so you don't do all the tables in one go.
You can also be selective on the amount of fragmentation - bol has a routine under dbcc showcontig - I sometime suse a similar method ( without the cursor )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 11, 2004 at 7:42 am
thanks for the responses. We have t-rep and log shipping as well. One of the DBAs tells me that running dbcc reindex will cause a delete and insert of rows ( he has apparently seen this happen) because of replication and cause a lot of traffic /headaches. Not sure i agree with that as i did not see it happen.
-srini
August 11, 2004 at 7:56 am
Yeah sort of, as I say we don't have any probs with index rebuilding and transactional replication, technically yes a clustered index rebuild can do as suggested, the downside of having severe fragmentation doesn't bear thinking. Write a routine to stagger the rebuilds.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply