SQL Server cluster merge 100% CPU utilization during the merge replication

Hi,

Can someone shed some light to my problem here with the SQL Server cluster merge 100% CPU utilization during the merge replication for about 2-3 days consecutively ?

Here is the specification for the 2x Clustered VM in each sites:
2x Database Servers
Windows Server 2008 R2 Enterprise SP1
SQL Server 2008 R2 Enterprise SP1
4x vCPU
16 GB vRAM
OS C:\ 60 GB HDD RAID 1
DATABASE D:\ 800 GB HDD RAID 5
LOGS L:\ 400 GB HDD RAID 5
QUORUM Q:\ 4 GB RAID 5
DTC T:\ 4 GB RAID 5
SNAPSHOT S:\ 500GB
2x vNIC (LAN and Heart Beat)

My question is that do I need to upgrade the above spec to lower down the CPU usage or change the replication schedule to be as often as possible like every 2 hours rather than weekly over the weekend ?
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
 
MohammedUCommented:
First run the SQL Trace with CPU filter (>=2000) and see exactly what query is causing the problem.
One more thing, by default parallelism is enabled and disable it for few minutes and see if the CPU drops...it doesn't require a restat.

exec sp_configure 'max degree of parallelism', 1
go
reconfigure with override
go

-- rollback... the above change if the CPU not dropped...

exec sp_configure 'max degree of parallelism', 0
go
reconfigure with override
go

0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Hi MohammedU,

Does that affects just one instance or the whole cluster servers ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.