Checkpoint

Overview

The checkpoint process writes dirty pages from the buffer pool area of memory to physical disk. It does not remove the pages from the buffer pool instead it marks them as clean again.Its purpose is to reduce the active portion of the log necessary to preform recovery so the frequency at which the checkpoint process is run determines the length of time it will take to recover the database when it is started up.  This needs to be balanced against the overhead required to run the checkpoint process. Similarly to the lazy writer process, checkpoint runs as a background task. We can query the sysprocesses system table to view its status:

SELECT cmd, spid, status, dbid, login_time, waittime, lastwaittype,
loginame,cpu, memusage, physical_io
FROM sysprocesses
WHERE  cmd IN ('LAZY WRITER', 'CHECKPOINT')

image

By default SQL Server determines the interval at which the checkpoint process runs. This is usually every 1 minute. This behaviour can be changed by setting the recovery interval which is an advanced configuration option.

Generally I don’t change the default value of 0 which is to allow SQL Server control the checkpoint process.

Checkpoint and the buffer pool

I want to see exactly what the checkpoint process does and how this impacts on the buffer pool. I have setup a test database called DBA (DB_ID = 7)with a table called tblTest. This is a small table with a couple of rows. I have already determined that it is using page 222. The first thing I want to do is clear the page from cache. TO do this I am going to call the checkpoint statement to clean the page and then I am going to call DBCC DROPCLEANBUFFERS  to remove all clean pages form the buffer pool.  Since I have set the recovery interval to 1000 minutes the automatic checkpoint process should not kick in. Just to be sure I am also going to turn on Trace flag  3502. This will log all checkpoint activity in the SQL Server Log.

DBCC TRACEON (3502, -1);
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;

I can verify that the page is not in the buffer pool by using the dm_os_buffer_descriptors  dmv.

select * from sys.dm_os_buffer_descriptors 
where page_id = 222 and database_id = 7

image

Next I am going to bring the tblTest into the buffer pool and dirty it., I am going to do this by updating all the existing rows in the table.

image

I can verify that the page is now in the buffer pool and that the page has been modified (dirty). The is_modiifed column in sys.dm_os_buffer_discriptors indicates the status of the page (clean / dirty as 1/0)

select * from sys.dm_os_buffer_descriptors
where page_id = 222 and database_id = 7

image

When we run a checkpoint again the page should remain in the buffer pool but it should now be clean

CHECKPOINT;
GO
select * from sys.dm_os_buffer_descriptors
where page_id = 222 and database_id = 7

image

This shows that the checkpoint process cleaned the page and left it in the buffer pool

Checkpoint and the transaction log

I am not going to get into Transaction Log management. For the purposes of the demo I setup the following

  • The database is in the simple recovery mode
  • There were no outstanding active transactions
  • No other transaction log dependant features such as replication or DB mirroring were setup on the box.

I have a database setup and I have run a number of transactions against it. The recovery interval has been set to 1000 minutes. I am going to use DBCC LOGINFO and the undocumented

DBCC LOGINFO; 
SELECT [Current LSN],[previous LSN], 
[Operation], context, [Checkpoint Begin], [checkpoint end], 
[page id], [minimum lsn], [Dirty Pages] [spid], [Begin Time], 
[Transaction Name], [End Time], [Lock Information], [Description]
 FROM ::fn_dblog(NULL, NULL) ;
 image
CHECKPOINT; 
GO 
DBCC LOGINFO; 
SELECT [Current LSN],[previous LSN], 
[Operation], context, [Checkpoint Begin], [checkpoint end], 
[page id], [minimum lsn], [Dirty Pages] [spid], [Begin Time], 
[Transaction Name], [End Time], [Lock Information], 
[Description] FROM ::fn_dblog(NULL, NULL) ;

image

Finally when does checkpoint run

When the database is shutdown a checkpoint is run against each database. The only exception to this is when shutdown is called with the no wait option or the server crashes.

The checkpoint process is run before each backup

The transaction exceeds maximum’s for the active portion

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