January 10, 2013 at 8:18 am
Greetings,
I have a server that is replicating four databases using transactional replication and I have a task on the publisher that reorgs the indexes on the published database. I haven't used replication much so I'm no expert. One of the databases on the publisher is also log shipped and when the reorg job runs it creates rather large trn log backups that take time to copy up to the DR site and be applied so that seems to indicate that the reorg is a transactional process (emphasis on it seems). My questions is, does the index reorg task work get replicated to the subscribers? I did a test using a model of the environment but in the monitor I didn't see anything come over for an index reorg or a dbcc check db command.
What I'm doing on the publisher is a 3 step job. First it runs check db then index reorg (only on indexes that meet criteria) and then updates stats. So, should I avoid running this job on the subscriber entirely or are these types of task not replicated and I should run them? If I should run them on the subscriber what concerns should I have?
Cheers
January 10, 2013 at 2:48 pm
jfogel (1/10/2013)
Greetings,I have a server that is replicating four databases using transactional replication and I have a task on the publisher that reorgs the indexes on the published database. I haven't used replication much so I'm no expert. One of the databases on the publisher is also log shipped and when the reorg job runs it creates rather large trn log backups that take time to copy up to the DR site and be applied so that seems to indicate that the reorg is a transactional process (emphasis on it seems).
The index maintenance is logged to the transaction log so that is why you are seeing that increase in the size of the shipped transaction logs. There is no real way around that.
My questions is, does the index reorg task work get replicated to the subscribers? I did a test using a model of the environment but in the monitor I didn't see anything come over for an index reorg or a dbcc check db command.
Index reorgs and dbcc checkdb commands are logged, but are not applied to the Subscribers. The Log Reader Agent is looking for changes to the articles in your Publication and those commands would not qualify.
What I'm doing on the publisher is a 3 step job. First it runs check db then index reorg (only on indexes that meet criteria) and then updates stats.
In updating stats on indexes you have just rebuilt it could mean you are losing some stats information depending on how you specified the stats update command. At best the server is duplicating some effort. It would be best to skip stats updates for indexes that were just rebuilt if possible.
So, should I avoid running this job on the subscriber entirely or are these types of task not replicated and I should run them? If I should run them on the subscriber what concerns should I have?
No issues on the Publisher. I would actually schedule the same job on the Subscriber (once you address the potential stats update issue I mentioned). Given that you can, and many people often do, have different index strategies setup on the Subscriber than on Publisher it is important to run index and stats maintenance there as well. Same goes for checkdb. With a different disk subsystem you could have problems on one and not on the other.
edit: fixed typo
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 10, 2013 at 5:00 pm
Thanks for the info. One thing I want to make clear is that there is no index rebuilding, just reorg. This is why the update stats is the next step. I can't just rebuild indexes because it is a 24/7 platform with some very large tables. We are running Standard Edition so online rebuilds are not going to happen. A few of the tables take three hours alone to rebuild a clustered index and just one other nonclusteted on the table.
I have let the folks know that reorgs aren't the solution and they understand. Right now it's all about money and that is why Standard is in use. They are open to scheduling maint. windows to rebuild large indexed. We are just doing the best we can with what we have right now.
Cheers
January 10, 2013 at 5:22 pm
jfogel (1/10/2013)
Thanks for the info. One thing I want to make clear is that there is no index rebuilding, just reorg. This is why the update stats is the next step. I can't just rebuild indexes because it is a 24/7 platform with some very large tables. We are running Standard Edition so online rebuilds are not going to happen. A few of the tables take three hours alone to rebuild a clustered index and just one other nonclusteted on the table.I have let the folks know that reorgs aren't the solution and they understand. Right now it's all about money and that is why Standard is in use. They are open to scheduling maint. windows to rebuild large indexed. We are just doing the best we can with what we have right now.
Sorry I made the leap to also include rebuilds. I hear you on the Standard Edition limitations but I was lucky enough to have time for rebuilds. Do you not have one night a week when you can run a rebuild on indexes that need it?
Do not forget statistics on non-indexed columns as well. If you can afford the time, go for WITH FULLSCAN on all your stats.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 6:22 am
The SLA is strict so once a week down time is something I can't do at this time.
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply