Checkpoint Tracking via Extended Events
Checkpoint tracking can be useful when you’re attempting to understand when automatic checkpoints are occurring. Extended Events in SQL Server offer a powerful way to track many events in SQL Server as they happen. This post shows how to setup an Extended Events session for tracking checkpoints. Both automatic, indirect, and manual checkpoints are covered by the session.
checkpoint in a self portrait by Godfrey Kneller
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
IF NOT EXISTS ( /* only create this session if one doesn't already exist to prevent inadvertant loss of events. */ SELECT 1 FROM sys.server_event_sessions ss WHERE ss.name = N'CheckpointTracking' ) BEGIN CREATE EVENT SESSION CheckpointTracking ON SERVER ADD EVENT sqlserver.checkpoint_begin ( ACTION ( package0.callstack , package0.collect_system_time , package0.event_sequence , sqlserver.client_app_name , sqlserver.client_hostname , sqlserver.database_id , sqlserver.database_name , sqlserver.is_system , sqlserver.session_id , sqlserver.session_nt_username , sqlserver.sql_text ) ) , ADD EVENT sqlserver.checkpoint_end ( ACTION ( package0.callstack , package0.collect_system_time , package0.event_sequence , sqlserver.client_app_name , sqlserver.client_hostname , sqlserver.database_id , sqlserver.database_name , sqlserver.is_system , sqlserver.session_id , sqlserver.session_nt_username , sqlserver.sql_text ) ) ADD TARGET package0.ring_buffer ( SET max_memory = 10240 ) WITH (STARTUP_STATE=OFF); END GO ALTER EVENT SESSION CheckpointTracking ON SERVER STATE = START; |
Use the code in our post on querying the ring buffer to get the data out of this Extended Events session. Alternately, you could simply watch the live data via the Extended Events session monitoring built into SQL Server Management Studio.
Check out the rest of our series on Extended Events.