CHECKPOINT is one of the most misunderstood topic among developers as well as DBAs. One of the misconception is that CHECKPOINT writes only the committed data and that is NOT true. Paul Randal wrote about this topic extensively (here) and busted this myth (here)

Today, I am going to bust this myth using Extended Events as they are going to be the future of monitoring and troubleshooting in SQL Server going forward. If you are NOT familiar with Extended Events then I encourage you to read this MUST read excellent series on Extended events by Jonathan Kehayias.

First we have to check if there is any support in Extended Events for tracking physical page writes? The below shows we are lucky.

--Find if there is any support for tracking connection pooling information
SELECT
	name
	, type_name
	, description
FROM sys.dm_xe_object_columns
WHERE object_name = 'physical_page_write'

physical page write

Now, since we can track the file id and page id from the above event whenever a physical write happens, that information along with the database id is more than enough for us to figure out what we need here. Let’s go ahead and create an Extended event and some extra relevant fields too. We will use the asynchronous file target here as well for now.

--Now, create a session and captures relevant information
--Use asynchronous file target
CREATE EVENT SESSION Track_physical_page_write ON SERVER
ADD EVENT sqlserver.physical_page_write(
   ACTION (sqlserver.sql_text, sqlserver.database_id, sqlserver.client_hostname, sqlserver.username
			, sqlserver.tsql_stack, sqlserver.server_instance_name, sqlserver.session_id)
   )
ADD TARGET Package0.asynchronous_file_target
( SET filename = 'C:\SQLServer\XEvents\Track_physical_page_write.xel'
		, metadatafile = 'C:\SQLServer\XEvents\Track_physical_page_write.mta')
WITH (MAX_MEMORY=4096 KB
		, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY=30 SECONDS
		, MAX_EVENT_SIZE=0 KB
		, MEMORY_PARTITION_MODE=NONE
		, TRACK_CAUSALITY=OFF
		, STARTUP_STATE=OFF)
GO

--Now, that the event session for tracking connection pooling
ALTER EVENT SESSION Track_physical_page_write
ON SERVER
STATE=START
GO

Now, let’s create a table and within a transaction add some data to that table but we won’t commit the data. A manual CHECKPOINT is issued to force the physical write of the uncommitted data and we will check if the data is written to the disk.

IF OBJECT_ID('dbo.TestPhysicalWrite') IS NOT NULL
	DROP TABLE dbo.TestPhysicalWrite
GO

--Create a new table
CREATE TABLE dbo.TestPhysicalWrite
(
	c1		BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, c2	DATETIME2 NOT NULL
)
GO

--Start a transaction and add few rows
SET NOCOUNT ON;
BEGIN TRAN
INSERT dbo.TestPhysicalWrite (c2)
	SELECT getdate()
GO 100
CHECKPOINT
GO

We haven’t issued a commit yet and the data is still uncommitted. Using DBCC EXTENTINFO, we can figure out the page_id and file_id numbers associated with this table. From the below, we have our page_id as 262 and we will check the results from the Extended events tracking.

--Lets use DBCC EXTENTINFO to look at the pages allocated to the table
DECLARE
	@DBID		INT
	, @ObjectID INT
SELECT
	@DBID = DB_ID()
	, @ObjectID = OBJECT_ID('dbo.TestPhysicalWrite')
DBCC EXTENTINFO (@DBID, @ObjectID, -1)
GO

file_id page_id information from DBCC EXTENTINFO

Once the CHECKPOINT is written then we can parse the data from the Extended events file using the code below. And the screenshot listed below, page_id 262 in file_id 1 has been physically written. There you have it, even though the transaction is NOT committed, with a manual CHECKPOINT the uncommitted data is written to the disk.

SELECT
	FinalData.R.value ('@name', 'nvarchar(50)') AS EventName
	, FinalData.R.value ('@timestamp', 'datetime2') AS [EventDateTime]
	, FinalData.R.value ('(data[@name = "file_id"]/value)[1]', 'INT') AS [file id]
	, FinalData.R.value ('(data[@name = "page_id"]/value)[1]', 'INT') AS [page id]
	, FinalData.R.value ('(action[@name="database_id"]/value)[1]', 'NVARCHAR(50)') AS [Database ID]
FROM (
SELECT
    CAST(event_data AS XML) AS xmldata
 FROM sys.fn_xe_file_target_read_file
 (
	'C:\SQLserver\XEvents\Track_physical_page_write*.xel'
	, 'C:\SQLserver\XEvents\Track_physical_page_write*mta'
	, null
	, null
)) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
ORDER BY 2 ASC

physical page write results showing file_id page_id and database_id

Extended events are really awesome technology that helps dive into the internals and see what’s happening inside SQL Server. I encourage readers to at-least try them out using examples like this and the more technical ones from Jonathan Kehayias.

Before we leave, let’s be a good boy and clean up.

ROLLBACK TRAN
GO
--Wait for few minutes to capture some activity and stop the session
ALTER EVENT SESSION Track_physical_page_write
ON SERVER
STATE=STOP
GO

--Go, clean up
DROP EVENT SESSION Track_physical_page_write ON SERVER
GO
--Drop the table
IF OBJECT_ID('dbo.TestPhysicalWrite') IS NOT NULL
	DROP TABLE dbo.TestPhysicalWrite
GO