0

We are running a mirror on SQL Server 2005 SP2 Enterprise edition for a few databases. These are running on VMWare with Server 2008 R2. On the mirror server, I see the CPU go up to 50% for about a minute every 10 minutes (on the tens). Here's what it looks like:

CPU Usage on Mirror Partner

From what I have seen so far, this is definitely the sqlserv.exe process going up to 50% (using up 1 of its 2 CPU cores). I have done a SQL profile, and not seen anything running during those times. This server is only used for mirroring the database, so my theory is that it's something related to that. Does anyone have any ideas, or other things I can do to investigate?

Improve this question

1 Answer 1

Reset to default
3

Given that is at exact 10 minute marks (7:30, 7:40, 7:50) I would first suspect a periodic job of some sort. An internal 10 min. timer (eg. a checkpoint interval) would run every 10 mins, but unlikely at exactly 10 minute marks. It could be either a job on the mirror, or a job on the principal that generates log that requires processing on the mirror. So first thing I would look would be SQL Agent history on both machines.

If that yields no results, capture SQL Server counters and see what correlates with the spikes.

Updated

Some counters worthy of monitoring to understand the origin of the load spikes:

Improve this answer
4
  • Good analysis. A checkpoint would be really hard to time. This really looks like some job running all 10 minutes. Commented Feb 9, 2011 at 18:30
  • My first thought was a job, but the only ones that would match up run at 1 and 15 minute intervals, so I should see it more often. I've looked at a few counters, and I can see Transactions/sec go up during that time, but do you have any particular counters in mind that would help see exactly what might be running? Commented Feb 9, 2011 at 18:54
  • 1
    Then perhaps is an application pattern. Anything that the application using the DB might be doing every 10 minutes, on the mark? Commented Feb 9, 2011 at 19:04
  • Thanks for the counters, there were some I wasn't using. I watched both the mirror and the principal, and found the same increase in activity, so I ruled out the mirror. However, there were no jobs running that corresponded with the spikes. But, this did make me realize that there were jobs on other servers that also used this database, and I manged to find a heavy-hitter that ran at the 10 minute intervals. Thanks for the help! Commented Feb 9, 2011 at 20:26

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.