Monitoring checkpoints in Profiler

Back in the days when I was still in the stresstest-team of our application, I tried to monitor as much as possible from within the SQL Server Profiler. Ok, you still need to use Perfmon of course to capture the most common Windows performance counters, but I find it very handy to have an indication of when exactly checkpoints occur along with the statements that are less performant because of it.

In SQL Server 2000, I was forced to write a little routine to trigger my own (user defined) events for the Profiler as there are no standard events that can be monitored. I used the sysperfinfo view to provide me of an indication of when a checkpoint was in progress. I guess that reading the transaction log of the monitored database is a valid alternative, but I haven't actually tried that one (ah... the greatly undocumented ::fn_dblog()).
Either way, both methods require custom scripting and are not accurate.

Now, in SQL Server 2005, you get of course the option to correlate performance counters with events logged in Profiler, which is a very neat feature, but I like to store all the info in one SQL Trace file.
Luckily, you also get the Security Audit Database Operation Event which is raised when a checkpoint begins. It's a pity that there's no counterpart for the end of a checkpoint :(.

Performance tuning guide for SQL Server

I'm no Google-guru, so I actually had a lot of trouble finding the following documents. But because this is my long term memory, I couldn't do without them.
So here it is, some indispensable old school performance tuning stuff: Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing. And the even more unfindable document version.

And it also has a nice equivalent for 2005: Troubleshooting Performance Problems in SQL Server 2005.