April 22, 2018 at 11:20 pm
We have setup push transactional replication, with the same server acting a both the subcriber and distributer.
There are 28 Publications and +-6 of the 28 databases are really busy and the others not so much.
For the past week I have been experiencing intermitent high CPU usage on the publisher - it usually runs at 50 - 55% but is now intermitently maxing out at 100%
for a period of 10-15 minutes at a time.
During this time I see SQL current processes report wait type "REPL_SCHEMA_ACCESS" and the elapsed time can be up to 5 minutes or more.
I never see these waits unless there is CPU pressure on the publisher.
Is this wait type the cause of my CPU pressure or is it the result of the CPU pressure?
I have checked the queries running - they are good on terms of stats and indexes. The same queries that run when the CPU is high, are the same
queries that run when the CPU is normal, so I am at a loss....
Can Push replication cause performance issues on the publisher and how to resolve this?
April 23, 2018 at 6:39 am
Hi,
how many publisher (Server) do you use, and how many distribution databases do you have?
If you only got 1 distribution database, then there maybe a lot of blockings. Therefor, you can create one distribution database per publisher.
Best regards,
Andreas
April 23, 2018 at 6:51 am
Hi,
One publisher server, 27 publications, One distribution database.
I think you could be right , because I have also noticed that when the CPU spikes on the Publisher, the value of UndelivCmdsInDistDB column in distribution.dbo.MSdistribution_status is higher than normal.
I do not know how to setup replication with numerous distribution databases - any advise/URL you can recommend?
This will probably mean I will have to setup replication from scratch? So in the meantime, do you think indexes on the distribution database tables could help?
April 23, 2018 at 7:58 am
Casper101 - Monday, April 23, 2018 6:51 AMHi,One publisher server, 27 publications, One distribution database.
I think you could be right , because I have also noticed that when the CPU spikes on the Publisher, the value of UndelivCmdsInDistDB column in distribution.dbo.MSdistribution_status is higher than normal.I do not know how to setup replication with numerous distribution databases - any advise/URL you can recommend?
This will probably mean I will have to setup replication from scratch? So in the meantime, do you think indexes on the distribution database tables could help?
How many articles do you have in those 27 publications?
Have you made any changes to the subscriptions, such as changing the indexing?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 23, 2018 at 11:57 pm
Casper101 - Monday, April 23, 2018 6:51 AMHi,One publisher server, 27 publications, One distribution database.
I think you could be right , because I have also noticed that when the CPU spikes on the Publisher, the value of UndelivCmdsInDistDB column in distribution.dbo.MSdistribution_status is higher than normal.I do not know how to setup replication with numerous distribution databases - any advise/URL you can recommend?
This will probably mean I will have to setup replication from scratch? So in the meantime, do you think indexes on the distribution database tables could help?
Hi,
you are able to create one distribution database per publisher. But if all publications are from the same sql server, no chance. Then you have to upgrade your distirbution server, and avoid to put subscriber, publisher and distributor together on a sql server.
best regards,
Andreas
April 24, 2018 at 1:12 am
ChrisM@Work - Monday, April 23, 2018 7:58 AMCasper101 - Monday, April 23, 2018 6:51 AMHi,One publisher server, 27 publications, One distribution database.
I think you could be right , because I have also noticed that when the CPU spikes on the Publisher, the value of UndelivCmdsInDistDB column in distribution.dbo.MSdistribution_status is higher than normal.I do not know how to setup replication with numerous distribution databases - any advise/URL you can recommend?
This will probably mean I will have to setup replication from scratch? So in the meantime, do you think indexes on the distribution database tables could help?How many articles do you have in those 27 publications?
Have you made any changes to the subscriptions, such as changing the indexing?
I have an average of 500 articles per publication.
I have not made any changes to indexing on the subscriber/distributor - I only add/remove/maintain indexes on the publisher
April 24, 2018 at 1:42 am
I have not made any changes to indexing on the subscriber/distributor - I only add/remove/maintain indexes on the publisher
Hm,
but if you rebuild an index, the new index will be published. I knew index with a size about 200 GB. Please check the size of the index. How to you made index maintenance, with scripts vom ola hallengren, or do you rebuild every index every night?
Best regards,
Andreas
April 24, 2018 at 2:28 am
andreas.kreuzberg - Tuesday, April 24, 2018 1:42 AMI have not made any changes to indexing on the subscriber/distributor - I only add/remove/maintain indexes on the publisher
Hm,
but if you rebuild an index, the new index will be published. I knew index with a size about 200 GB. Please check the size of the index. How to you made index maintenance, with scripts vom ola hallengren, or do you rebuild every index every night?
Best regards,
Andreas
Of the 27 databases, the biggest index is 25 GB. Other databases's biggest index values varies from 10 GB or smaller
I have not seen the script from hallengren you are referring to, but I maintain my indexes each night with a script that checks the fragmentation - it rebuilds or reorganizes based on the fragmentation value. If it is not fragmented I do nothing to it
April 25, 2018 at 3:32 pm
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply