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'
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
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)', 'INT') AS [file id] , FinalData.R.value ('(data[@name = "page_id"]/value)', 'INT') AS [page id] , FinalData.R.value ('(action[@name="database_id"]/value)', '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
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