Category: sql server replication
vuadapass on Thu, 01 Dec 2016 15:51:00
I have setup a merge publication and a subscriber with push subscription mode.
On the subscriber, I notice the CPU usage is going high periodically.
It may take about 40 seconds for the CPU Usage come back to 0%. And it occurs at same time as long as the Synchronization Status display : "Uploading data changes to the Publisher" . This only occurs at subscriber, the publisher is normally.
I've go to Reports > Top Queries by Total CPU time at the subscriber to see which queries make the CPU usage go high and below is the result:
I don't know what to do next to fix this issue. Anyone please help me. Thank you all very much.
Lin Leng on Fri, 02 Dec 2016 07:41:39
I’m not quite sure what is causing the issue here, but it looks like one of the replication stored procedure is involved. To find out, I would suggest you use the query below to find out:
use DATABASENAME; select * from sys.objects where object_id = 643983114
And could you please provide more information such as CPU speed/Number of Cores and system memory size so we can have a better understanding about the issue? If the server is not powerful enough and you have a lot of data to be replicated, I’d say high CPU utilization is expected.
If you have any other questions, please let me know.
vuadapass on Sat, 03 Dec 2016 07:52:35
Hi Lin Leng,
Thank you very much for trying to help me,
I ran that query but returned no result:
I'm using a dedicated server - CPU E3-1220 , clock speed 3.1 GHZ , 4 cores . I installed 20 GB memory ram but 50% is still free (as you can see in Task manager) .
Yesterday, I take a look at the Synchronization History to see how many datas is being replicated in about 1 hour and below is the result (uploaded : 4804 / downloaded: 1019):
That amount of data seems small enough and I don't think it can earn up to 40% CPU usage of a CPU E3-1220.
And also i think the Publisher received more changes from the Subscriber and it should take more CPU usages. But why its CPU usage is still normally while the CPU usage is too high on Subscriber ? ( Both subscriber and publisher have the same CPU speed )
I have tried some articles on the internet that tell me to check the Transaction Retention (0-48 hours) / History Retention (24 hours) and Subscription Expiration (14 days) to be configured correctly and run sp_mergemetadataretentioncleanup to clean up the merge metadata but no lucky.
Today, in a final effort to fix this issue, I've right clicked on the Subscriber and select Reinitialize (as I think it will make everything become fresh). And the magic happened , the issue disappear.
Now the issue gone but I just worry if it come back again in the future.
vuadapass on Sat, 28 Jan 2017 03:58:07
After a few dozen days, this issue come back again. I must reinitialize the subscription to workaround each time it occur. Please tell me if anyone have any idea to fix this issue. Thank you very much!