I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O. I'm running MS SQL Server 2008 R2 SP1
Results in my SQL Log file look something like this (numbers fudged a little):
about to log checkpoint end
last target outstanding 2, avgWriteLatency 40ms
Average Throughput: 0.67 MB/sec, I/O Saturation: 79, Context Switches 201
FlushCache: cleaned up 125 Bufs with 69 writes, in 1447ms (avoided 0 new dirty bufs)
Ckpt dbid 9 phase 1 ended (8)
about to log checkpoint begin.
I don't really know how to read this, or break it down in a way that I get get anything truly meaningful out of it.
What does 'last target outstanding mean?"
Does the average write latency mean the overhead time it takes per write? or the time between writes? 40ms seems high, the physical drive is a 1TB, and it's RAID5 configured.
What is I/O saturation?
What does it have to do with the Context Switches. I'm assuming Context switches have something to do with multi-tasking. Changing between jobs/writes.
FlushCache. I realize this has to do with clearing out the cache. What are the Bufs? Are these pages of data that needed to be written? What are the dirty Bufs? Why would they be avoided?
A detailed breakdown would be appreciated.