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 10
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senior IT System EngineerIT ProfessionalAuthor Commented:
Hi MohammedU,

Does that affects just one instance or the whole cluster servers ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.