Performance Monitor of Database Server

Before I pose my question, please allow me to describe the situation. I have a database server, with a number of tables. Two of the biggest tables contain over 800k rows each. The majority of rows are less than 10k in size, though roughly 1 in 100 rows will be > 1 MB but <4 MB. So out of the 1.6 million rows, about 16000 of them will be these large rows. The reason they are this big is because we're storing zip files binary blobs in the database, but I'm digressing.

We have a service that runs constantly in the background, trimming 10 rows from each of these 2 tables. In the performance monitor graph above, these are the little bumps (red for CPU, green for disk queue). Once ever minute we get a large spike of CPU activity together with a jump in disk activity, indicated by the red arrow in the screenshot.

I've run the SQL Server profiler, and there is nothing that jumps out as a candidate that would explain this spike. My suspicion is that this spike occurs when one of the large rows gets deleted. I've fed the results of the profiler into the tuning wizard, and I get no optimisation recommendations (i.e. I assume this means my database is indexed correctly for my current workload).

I'm not overly worried as the server is coping fine in all circumstances, even under peak load. However, I would like to know if there is anything else I can do to find out what is causing this spike?


After investigating this some more, the CPU and disk usage spike was down to SQL server's automatic checkpoint. The database uses the simple recovery model, and this truncates the log file at each checkpoint. We can see this demonstrated in the following graph. Checkpoints/sec and percent of log used As described on MSDN, the checkpoints will occur when the transaction log becomes 70% full and we are using the simple recovery model.

This has been enlightening and I've definitely learned something!

  • Are you tracing checkpoints in your trace? – Martin Smith Oct 26 '11 at 20:06
  • There isn't an option to trace checkpoints in the profiler. Perhaps this is only available on a more expensive version of SQL server (we're using SQL Server Standard)? – CadentOrange Oct 26 '11 at 21:18
  • It's entirely possible I remembered wrong. You should be able to trace them through perfmon though? – Martin Smith Oct 26 '11 at 21:21
  • You can most definitely do this in PerfMon. The relevant counters are the Checpoint pages/sec and Percent Log Used, and these can be found under MSSQL$DBINSTANCE:Buffer performance object in PerfMon. – CadentOrange Oct 27 '11 at 13:23

Checkpointing, writing out changed database pages. This does not happen all the time under many circumstances.


| improve this answer | |
  • I'm not familiar with checkpoints, so I'll look into that when I'm back at work tomorrow. – CadentOrange Oct 26 '11 at 21:25
  • It was checkpoints. – CadentOrange Oct 27 '11 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.