SQL Server Indirect Checkpoint feature an initial look

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.

 

1

 

The Test

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.

 

Analysis

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

 

2

Conclusion

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s