An XEvent a Day (16 of 31) – How Many Checkpoints are Issued During a Full Backup?

This wasn’t my intended blog post for today, but last night a question came across #SQLHelp on Twitter from Varun (Twitter).

#sqlhelp how many checkpoints are issued during a full backup?

The question was answered by Robert Davis (Blog|Twitter) as:

Just 1, at the very start. RT @1sql: #sqlhelp how many checkpoints are issued during a full backup?

This seemed like a great thing to test out with Extended Events so I ran through the available Events in SQL Server 2008, and the only Event related to Backup is the sqlserver.databases_backup_restore_throughput Event, something which is a topic for another blog post, but that doesn’t matter because we can still do testing of this by using the Events available in Extended Events.  The sqlserver.sql_statement_starting, sqlserver.sql_statement_completed, sqlserver.checkpoint_begin and sqlserver.checkpoint_end Events can be used to test this with appropriate Predicate definitions.

To test this I used a copy of two databases on a development server.  One is a source database and the second is a reporting database.  I also duplicated the ETL process that extracts data from a source database and transforms it into the reporting schema so that I could test this under a workload that would be changing data and should cause checkpoints to occur inside of the reporting database.  Then I queried sys.databases (ok I actually used DB_ID(‘Sample_Reporting’)) to get the database_id for the Sample_Reporting database to use in the Predicate for the sqlserver.checkpoint_begin and sqlserver.checkpoint_end Events. 

Then I opened a new Query Window in SSMS and used that connections session_id in the Predicate for the sqlserver.sql_statement_starting and sqlserver.sql_statement_completed Events in the Event Session.  The result was the following Session definition.

-- Create the Event Session
CREATE EVENT SESSION BackupCheckPoints
ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.session_id = 113)),
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.session_id = 113)),
ADD EVENT sqlserver.checkpoint_begin
(    WHERE (database_id= 41)),
ADD EVENT sqlserver.checkpoint_end
(    WHERE (database_id = 41))
ADD TARGET package0.ring_buffer
GO
-- Alter the Session to Start it
ALTER EVENT SESSION BackupCheckpoints
ON SERVER
STATE=START
GO

With the Event Session started, I then started a FULL backup of the Sample Reporting database, followed by starting the ETL processes.  When the FULL backup completed I dropped the Events from the Event Session so that no further Event collection occurred.

-- Drop Events to halt Event collection
ALTER EVENT SESSION BackupCheckPoints
ON SERVER
DROP EVENT sqlserver.sql_statement_starting,
DROP EVENT sqlserver.sql_statement_completed,
DROP EVENT sqlserver.checkpoint_begin,
DROP EVENT sqlserver.checkpoint_end

Now we can query the ring_buffer Target and see what has occurred during the FULL backup of the Sample_Reporting database.

-- Query the XML to get the Target Data
SELECT
    n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh,
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
    ISNULL(n.value('(event/data[@name="database_id"]/value)[1]', 'int'),
            n.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS [database_id],
    n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
FROM
(    SELECT td.query('.') AS n
    FROM
    (
        SELECT CAST(target_data AS XML) AS target_data
        FROM sys.dm_xe_sessions AS s
        JOIN sys.dm_xe_session_targets AS t
            ON t.event_session_address = s.address
        WHERE s.name = 'BackupCheckpoints'
          AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) AS tab
GO

As you can see in the above screenshot, multiple checkpoints can occur during a FULL backup of a database in SQL Server 2008.  According to Paul Randal, “Checkpoints exist for two reasons—to batch up write I/Os to improve performance and to reduce the time required for crash recovery” (http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx).  Since we are continuing to make changes to the data inside of the system while the FULL backup occurs, there is a continued need for CHECKPOINT’s to occur for the database.