Day 14–Trace Flag 3505–Control SQL Server Checkpoint Behavior

What’s is checkpoint? Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from buffer cache to disk. It helps in recovery time of the database so that when the database start’s up there is no need to do this for all dirty pages, so checkpoint happens in an regular interval. For more information on checkpoint refer the KB article http://msdn.microsoft.com/en-us/library/ms188748.aspx.

Checkpoint might causes disk IO to get increased based on size of dirty pages. Checkpoint happens automatically on some situations, above KB article will give you those situations. Now your questions is why do we need to turn off. As I said earlier Checkpoint increases disk IO so to avoid this we might need to change checkpoint behavior in some scenario’s. So lets try to do a test on this

--Turning on trace flag 3505
DBCC TRACEON(3505)
GO
--Create a temp database
CREATE DATABASE CheckpointTest
GO
--Creating a test table and populating values
USE CheckpointTest
GO
CREATE TABLE Test (ID INT IDENTITY(1,1), Name CHAR(4000))
GO
INSERT INTO Test VALUES('SQL-Articles')
INSERT INTO Test VALUES('SQL-Articles.com')
GO
--Checking dirty pages in buffer pool for Checkpoint db
SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('CheckpointTest') and is_modified = 1
ORDER BY page_id DESC
GO
--Now manually run the checkpoint to flush dirty pages to disk
--as we have turned off automatic checkpoint
CHECKPOINT
GO
--Checking dirty pages in buffer pool for Checkpoint db after checkpoint
SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('CheckpointTest') and is_modified = 1
ORDER BY page_id DESC

From the image below you can find that check point is not done and dirty pages exists in buffer pool (as modified page) as we have turned off automatic checkpoint using the trace flag 3505

Now we have manually ran checkpoint command to flush all the dirty pages, after that when we run the command you can find no modified pages exists.

Once you have done with your testing use the command below to clean up the db created.

--Drop CheckpointTest database
DROP DATABASE CheckpointTest
GO
--Turning off trace flag 3505
DBCC TRACEOFF(3505)