Starting SQL 2012 we have got something new added to the SQL error logs which was logged earlier when a TF was enabled. Below is the message which is new in the SQL error logs :
2013-09-20 03:01:48.10 spid14s FlushCache: cleaned up 12773 bufs with 2061 writes in 96736 ms (avoided 11 new dirty bufs) for db 5:02013-09-20 03:01:48.10 spid14s average throughput: 1.03 MB/sec, I/O saturation: 3196, context switches 29132013-09-20 03:01:48.10 spid14s last target outstanding: 14, avgWriteLatency 82013-09-20 03:03:01.98 spid14s FlushCache: cleaned up 16758 bufs with 7720 writes in 73764 ms (avoided 3642 new dirty bufs) for db 9:02013-09-20 03:03:01.98 spid14s average throughput: 1.77 MB/sec, I/O saturation: 6115, context switches 44092013-09-20 03:03:01.98 spid14s last target outstanding: 52, avgWriteLatency 02013-09-20 03:05:16.27 spid14s FlushCache: cleaned up 18250 bufs with 8391 writes in 65152 ms (avoided 4587 new dirty bufs) for db 9:02013-09-20 03:05:16.27 spid14s average throughput: 2.19 MB/sec, I/O saturation: 4791, context switches 35112013-09-20 03:05:16.27 spid14s last target outstanding: 54, avgWriteLatency 68
Troubleshooting steps :
1. Make sure that the SAN performance is optimal and the sec\read and sec\write are under the acceptable limits. Capture PerfMon with a 2 sec. interval on the server.
2. Use the start-up trace flag –k ( -k enables us to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal ceckpoints.)
3.Start using Indirect Checkpoint mechanism which is a new feature in SQL 12. This is something that would require testing and we would not be able to comment on this.
• Indirect checkpoints can reduce overall database recovery time.
A database that is configured for indirect checkpoints could experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.
3. Change the recovery interval (http://technet.microsoft.com/en-us/library/ms191154.aspx)
4. Use different mount points to have the data and log files for these two databases.
Disclaimer: I work at Microsoft. Everything here, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.