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. 

image

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

image

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.

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

Leave a Reply

Your email address will not be published.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.