With some of our SQL2012 instances we get frequent checkpoint messages during data load
2013-06-25 06:14:04.84 spid16s FlushCache: cleaned up 184496 bufs with 11466 writes in 507527 ms (avoided 125257 new dirty bufs) for db 7:0
2013-06-25 06:14:04.84 spid16s average throughput: 2.84 MB/sec, I/O saturation: 21329, context switches 48504
2013-06-25 06:14:04.84 spid16s last target outstanding: 2400, avgWriteLatency 990
as per CSS blog post http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx
these are supposed to be informational and might point you to investigate further.
the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval.
If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.
At SSC forum system FlushCache happening often SpringTownDBA
added great information with regards to how to investigate the issue and some tips on how to avoid dirty pages.
As I have no windows admin authority
for the concerned instances, I cannot just launch perfmon to investigate the thing.Does someone have a ref that explains the details of it all ?What do these measures mean?
- I/O saturation mean
- last target outstanding
Why is there so much variation in the figures of the different FulshCache messages?
The only thing going on at that load time is a single database being loaded. No other activity is ongoing at that time span.
I think the server raising the posted message is not configured in an optimal way.
Mainly based on its value for average throughput
(even 0.42 MB/sec in some messages)
and the value for avgWriteLatency
(varying from 65 to 990) for a dedicated RAID1 disk ( DASD 15K )
I think adding RAM to this instance might cause the frequency of these message to lower down, but if the issue is the I/O subsystem, the yield for such operation would be low.
Could optimising the NTFS block size to 64K help out ? ( is currently 4K )
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
- How to post Performance Problems
- How to post data/code to get the best help
- How to prevent a sore throat after hours of presenting ppt ?"press F1 for solution", "press shift+F1 for urgent solution"
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me
but most of the time this is me