update statistics on replicated databases

Question

update statistics on replicated databases

TRACEY-320982

SSChampion

Points: 13653
More actions
February 9, 2009 at 11:36 am

#126217

I replicate database A to SERVER B database B
Would it be recommened to run the udpate statistics on the database B.
I normally don't run on the replicated database. Do you think best to put the auto update statistics asycn..
Also the distribution database.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

TRACEY (2/9/2009)
I replicate database A to SERVER B database B
Would it be recommened to run the udpate statistics on the database B.
I normally don't run on the replicated database. Do you think best to put the auto update statistics asycn..
Also the distribution database.

Yes you must update statistics and do re-indexing as you would on the primary.

No need for the async update stats but you could. You should rather consider RCSI isolation on the DB.

Distribution does stats updates automatically and unless your retention period is very long there is no need for it.

HTH

* Noel

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Yes you must update statistics and do re-indexing as you would on the primary.

(Thanks i thought i should too thanks for clarifying)

No need for the async update stats but you could. You should rather consider RCSI isolation on the DB. What is the RCSI?

Distribution does stats updates automatically and unless your retention period is very long there is no need for it. (Wasn't sure on this db so that good to hear i don't have to do anything).

Cheers

noeld SSC Guru Points: 96590 More actions · Answer 3

Sorry:

RCSI = READ-COMMITED-SNAPSHOT ISOLATION.

You can enable it as:

ALTER DATABASE < dbname > SET READ_COMMITTED_SNAPSHOT ON;

Note: You need exclusive access to do that!

Hope it helps.

* Noel

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Thanks i will set that up on a test system and see what the ramifications are when transactional replication is updating the reporting database.

Thanks for you time.

noeld SSC Guru Points: 96590 More actions · Answer 5

TRACEY (2/9/2009)
Thanks i will set that up on a test system and see what the ramifications are when transactional replication is updating the reporting database.
Thanks for you time.

There are occasions in which reports must scan tables and a deadlocks occur while replication is hammering them.

To minimize those this option is *very* useful!

* Noel

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

RCSI .

Today i am experience a problem that BI Group reading the data select statements and Replication is being held up with a hugh lag cause their apps are hanging but maintaining the record locked.

So if the replication db is set to RCSI will this still allow the replication inserts/deletes to be updated on the DB and also not prevent BI locking the tables whislt inserts /deletes etc are being ran from Replication.