We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are experiencing a peculiar trend whilst processing.
SQL Server process requests at a blistering rate for 1 minute, followed by ~20 seconds of increased disk write activity. The following graph illustrates the problem.
Yellow = Transactions per second Blue = Total CPU usage Red = Sqlsrv Disk Write Bytes/s Green = Sqlsrv Disk Read Bytes/s
During troubleshooting, we tried the following without any significant change in the pattern:
- Stopped SQL Server Agent.
- Killed almost every other running process (No A/V, SSMS, VS, Windows Explorer, etc.)
- Removed all other databases.
- Disabled all conversation timers (we don't use any triggers).
- Moved away from a message queue driven approach to a simple/crude table monitoring design.
- Used different loads from light to heavy.
- Fixed all deadlocks.
It seems as if SQL Server might be building up its cache and writing it to disk at specific time-based intervals, but I can't find anything online to support this theory.
Next, I plan to move the solution over to our dedicated test environment to see if I can replicate the problem. Any help in the interim would be greatly appreciated.
Update 1 As requested, herewith a graph that includes the Checkpoint Pages/Sec, Page Life Expectancy, and some disk latency counters.
It appears as if the Checkpoint (light blue line) is the cause of the reduced performance (yellow line) we're observing.^
The disk latency remains relatively consistent during processing and the page life expectancy does not seem to have any noticeable effect. We also adjusted the amount of ram available for SQL Server, which also did not have a big effect. Changing the recovery model from
FULL also made little difference.
Update 2 By changing the "Recovery Interval" as follows, we've managed to reduce the interval at which checkpoints occur:
EXEC sp_configure 'show advanced options',1 GO RECONFIGURE GO EXEC sp_configure 'recovery interval', '30' GO RECONFIGURE GO EXEC sp_configure 'show advanced options',0 GO RECONFIGURE
I am unsure whether this is bad practice though?