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

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

trace_flag_3505_1

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.

trace_flag_3505_2

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)

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Indirect Checkpoint - SQL Server 2012
I noticed an interesting feature that’s released with SQL Server 2012. In this version now you can set recovery time at database level. In other ...
SQL 2012 DTA Engine Crashes on Windows 8
Question: Some time back I wrote an article on the new feature that’s added to SQL Server 2012 DTA (database tuning advisor). I have installed S...
Day 13–Trace Flag 7806–Enable Dedicated Administrator connection in SQL Express edition
Dedicated administrator is one of the new feature introduced from SQL Server 2005. This is a wonderful feature for DBA’s to troubleshoot SQL Serv...
powered by RelatedPosts