allmhuran (3/8/2013)
GilaMonster (3/8/2013)
allmhuran (3/8/2013)
GilaMonster (3/8/2013)
sys.dm_os_wait_stats
Most of those are meaningless waits, background processes that are supposed to be waiting. That list says nothing of any value.
Yep, that's kinda the point I'm making by noting that iolatch is down at 12th under all of this. 🙂
You're missing my point. Those waits are ones you ignore completely. Saying that IOLatch waits are 12th under that list means they're the second top useful wait in the system. Those background waits are normal and expected to be high, they're from processes that spend most of their time doing nothing, so you just filter them out of any wait analysis.
No, I get your point, I'm just making it explicit that there's nothing particularly unusual at the top end of the list.
You haven't made that clear because all you've posted are expected, normal waits that will have high wait times on any normal system and are absolutely irrelevant to anything. To draw any useful conclusions we need the list of top *relevant* waits.
Those waits you've posted are to be completely ignored when doing wait analysis, they will be high, that's expected, that's normal, that's of no interest to anyone. If you want to make a point about the top waits on your system, you have to ignore the useless waits.
Regarding log entries: Yes, there are lots of cache flush messages, I understand this is now on by default for the checkpoint process to log without a trace flag.
- "FlushCache: cleaned up 2037 bufs with 1673 writes in 86339 ms (avoided 1002 new dirty bufs) for db 7:0"
- "average throughput: 0.18 MB/sec, I/O saturation: 3196, context switches 7021"
Not what I was talking about. Wondering if there's any explicit clears of the buffer pool, though thinking about it, that might not be logged, might have to check the default trace. Can't recall if SQL 2012 logs buffer pool clearing.
Edit: SQL 2008 doesn't log when the buffer pool is manually cleared, just plan cache.
But I'm curious where you might be going... are you thinking a recovery interval issue, or perhaps this?:
Err... no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability