What can make a SQL Server DB exhibit a cyclic CPU usage behavior?

I have a system that is processing messages from an MSMQ queue (using multiple processes to do the processing).

Each message processing implies reading some rows, and making 3 updates and 1 insert. I'm processing around 60 messages per sec with this system.

What is puzzling me is that when for whatever reason, the queue has 开发者_JAVA百科a buildup of messages, and the system is working as fast as it can to process messages, the CPU usage exhibits a cycle with peaks of 95%-100% and valleys of 50%-45%. This behavior holds even when I add more processes to do the processing.

Is it expected of SQL Server to show that behavior when the workload implies row insertion and updates (I'm thinking cache flushing, etc.)? Maybe it's got something to do with the host (this is running on Hyper-V, instead of on real hardware)?

Here's a link to a perfmon run: http://dl.dropbox.com/u/2200654/sql_perf_000001.rar

---

This is an example how to start logman counter trace for some relevant counters, with 10s collection interval, for a default SQL Server instance:

@echo off
del %temp%\sql_perf*.blg
logman delete sql_perf
logman create counter sql_perf -f bin -si 10 -o %temp%\sql_perf.blg -c "\Processor(_Total)\*" "\Physical Disk(*)\*" "\Process(*)\*" "\SQLServer:Access Methods\*" "\SQLServer:Databases(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Wait Statistics\*" "\SQLServer:Transactions\*"
logman start sql_perf

Named instances change the counter category name from "SQLServer:..." to ":..."

The uploaded performance counters are missing the Phsyical Disk counters so the IO analysis is incomplete, but one think standing out is that the % CPU usage is an exact match of the SQL Server transaction/sec and batches/sec:

Note how the red line (% CPU) follows exactly the shape of the green line (Transactions/sec) and also the blue line (Batches/sec). This indicates that the spikes are entirely driven by the application behavior. Simply said, the SQL Server spikes in CPU (and also on IO write read, the purple line) simply because your application spikes requests every 3 minutes or so.

---

Check the rest - could it be you hit acheckpoint and thus processing slows down until the dirty pages are written to the database?

---

I've found the reason of the strange behavior: lack of memory. When I added the 'Memory: Page Reads/sec' counter, the valleys coincided with times of high page faulting.

Thanks for your answers (now the question seems silly, I'll ask for more memory :).