merge replication causing cpu spikes?

Hi ... having a problem with an application that relies on merge replication
among three SQL Servers. Two of the servers are public, the other's behind a
firewall. Periodically the CPUs on the public servers spikes, and
sqlserver.exe is consuming the whole thing. Running "select * from
master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
records pointing to NETWORKIO as the source. It appears port 1433 is open
between these servers, but none of the Microsoft networking ports seem to be.
Replication has been suggested as the prime suspect, but sqlagent.exe doesn't
seem to be consuming many CPU cycles by comparison.
Aside from the NIC and basic SQL Server counters, what performance counters
should I monitor to get to the source of the problem? Any other suggestions
to determine exactly why this is happening to SQL?
Thanks in advance,
Dan
This is a tough one. First of all don't get too excited by CPU spikes. It
just means that your processor is busy. A while loop is able to throw your
CPU into a tizzy. for example open up a large log file and do a global
search and replace.
CPU spikes become problematic when they last a long time. How long does the
cpu spike last for?
What happens when you shut down SQL Server Agent? Do you still get these
spikes?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> Hi ... having a problem with an application that relies on merge
replication
> among three SQL Servers. Two of the servers are public, the other's behind
a
> firewall. Periodically the CPUs on the public servers spikes, and
> sqlserver.exe is consuming the whole thing. Running "select * from
> master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
> records pointing to NETWORKIO as the source. It appears port 1433 is open
> between these servers, but none of the Microsoft networking ports seem to
be.
> Replication has been suggested as the prime suspect, but sqlagent.exe
doesn't
> seem to be consuming many CPU cycles by comparison.
> Aside from the NIC and basic SQL Server counters, what performance
counters
> should I monitor to get to the source of the problem? Any other
suggestions
> to determine exactly why this is happening to SQL?
> Thanks in advance,
> Dan
|||I should have been more specific ... these spikes are pretty devastating,
with the application that relies on the database becoming completely
unresponsive ... forcing us to restart the application.
We didn't shut down SQLAgent, but we did shut down the application that
makes database changes, so the agent had a lot less work to do ... the
assumption is that it catches up on its backlogged replication duties by the
time the app is brought back online (usually within a half hour or so). We're
putting some performance counters in place to prove/disprove that theory.
Is shutting down the agent relatively safe with merge replication? I've done
that using transactional replication with no negative results, but I wanted
to err on the side of caution.
Thanks *very* much for your help.
Best Regards,
Dan
"Hilary Cotter" wrote:

> This is a tough one. First of all don't get too excited by CPU spikes. It
> just means that your processor is busy. A while loop is able to throw your
> CPU into a tizzy. for example open up a large log file and do a global
> search and replace.
> CPU spikes become problematic when they last a long time. How long does the
> cpu spike last for?
> What happens when you shut down SQL Server Agent? Do you still get these
> spikes?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
> news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> replication
> a
> be.
> doesn't
> counters
> suggestions
>
>
|||Yes, shutting down the merge agent is safe to do and has little impact on a
server. The longer you have the merge agent shut down for the more impact it
will have on the system.
The reason I say this is because every time there is some update activity on
the published tables, this transaction is logged in msmerge_contents and
msmerge_tombstone. As these tables get large, the logging takes longer and
performance is degraded, although normally not signficantly.
Ideally you want your merge agent to run frequently so it can process small
chunks of data, as opposed to run hourly and process large chunks of data.
Perhaps change your polling interval to every 10 seconds, and set your merge
agent to run every 5 minutes and see if this has an impact on performance.
One more thing - are you using filtering?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> I should have been more specific ... these spikes are pretty devastating,
> with the application that relies on the database becoming completely
> unresponsive ... forcing us to restart the application.
> We didn't shut down SQLAgent, but we did shut down the application that
> makes database changes, so the agent had a lot less work to do ... the
> assumption is that it catches up on its backlogged replication duties by
the
> time the app is brought back online (usually within a half hour or so).
We're
> putting some performance counters in place to prove/disprove that theory.
> Is shutting down the agent relatively safe with merge replication? I've
done
> that using transactional replication with no negative results, but I
wanted[vbcol=seagreen]
> to err on the side of caution.
> Thanks *very* much for your help.
> Best Regards,
> Dan
> "Hilary Cotter" wrote:
t[vbcol=seagreen]
your[vbcol=seagreen]
the[vbcol=seagreen]
behind[vbcol=seagreen]
"lastwaittype"[vbcol=seagreen]
open[vbcol=seagreen]
to[vbcol=seagreen]
|||We're not using any filtering at all, and the merge agent is set to run every
minute.
Learned something more about the network setup: *all* ports are blocked
upstream from the subscribers to the publisher: the publisher's behind a
firewall and the subscribers are in front of it. The public subscribers' data
is updated much more frequently than the publisher's, but it's incumbent on
the publisher to initiate replication.
My uninformed impression was that merge replication would require two-way
communication, but at least one of our admins believes the replication
connections are initiated by the publisher, so this is a non-issue.
And just to make things really interesting, sqlagent isn't logging on
the publisher (no sqlagent.out to check for errors). Gah.
Thanks again,
Dan
"Hilary Cotter" wrote:

> Yes, shutting down the merge agent is safe to do and has little impact on a
> server. The longer you have the merge agent shut down for the more impact it
> will have on the system.
> The reason I say this is because every time there is some update activity on
> the published tables, this transaction is logged in msmerge_contents and
> msmerge_tombstone. As these tables get large, the logging takes longer and
> performance is degraded, although normally not signficantly.
> Ideally you want your merge agent to run frequently so it can process small
> chunks of data, as opposed to run hourly and process large chunks of data.
> Perhaps change your polling interval to every 10 seconds, and set your merge
> agent to run every 5 minutes and see if this has an impact on performance.
> One more thing - are you using filtering?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
> news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> the
> We're
> done
> wanted
> t
> your
> the
> behind
> "lastwaittype"
> open
> to
>
>