FlushCache : New in SQL Server 2012

  • Article

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:0
2013-09-20 03:01:48.10 spid14s                 average throughput:   1.03 MB/sec, I/O saturation: 3196, context switches 2913
2013-09-20 03:01:48.10 spid14s                 last target outstanding: 14, avgWriteLatency 8
2013-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:0
2013-09-20 03:03:01.98 spid14s                 average throughput:   1.77 MB/sec, I/O saturation: 6115, context switches 4409
2013-09-20 03:03:01.98 spid14s                 last target outstanding: 52, avgWriteLatency 0
2013-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:0
2013-09-20 03:05:16.27 spid14s                 average throughput:   2.19 MB/sec, I/O saturation: 4791, context switches 3511
2013-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.

Pros

        • Indirect checkpoints can reduce overall database recovery time.

  • Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. This enables a server instance to stay within an upper-bound on recovery times for a given database (except when a long-running
    transaction causes excessive UNDO times).
  • Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

 Cons

 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 (https://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.