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)