The indirect checkpoint feature introduced in SQL Server 2012 can deliver more reliable and faster recovery times. It can also reduce the I/O spikes often observed with automatic checkpoints. However for OLTP workloads indirect checkpoints can effect system performance as they sometimes increase the amount of write I/O, in this blog post we are going to explore this in more detail.
Understanding business requirements and how they translate into technical decision making is a key part of any IT professionals job. In this example we have an OLTP workload part of which contains a hot table that is frequently updated by the business. The table is small and easily fits in buffer pool memory. Since the tables data pages are frequently accessed they are less lightly to get evicted by the Lazywriter process however they will still need to be flushed to disk as part of a checkpoint operation.
In a test environment to monitor how often these pages are getting flushed to disk I am going to setup an Extended Events Trace that tracks physical writes and stores then in a histogram. I applied a filter to the trace definition to only track data pages from the hot table in our database.
To compare automatic and indirect checkpoints I completed 2 test runs of 10 minutes each. In the first run I used automatic checkpoints and in the second run I switched to indirect checkpoints setting the target recovery time for the database to 60 seconds. I took the data from the 2 histograms and analysed it in Excel.
With Automatic Checkpoint data pages are written an average of 9 times over 10 minutes
For Indirect Checkpoint data pages are written an average of 29 times over 10 minutes
In our example above the indirect checkpoint feature significantly increased the number of times our hot table gets written to disk. This of course has to be taken into account with overall system performance and potential gains from indirect checkpoints.
In a future post we will look at some of the advantages of indirect checkpoints