monitor checkpoint by Extended Event-优快云博客

本文介绍如何使用 SQL Server 的扩展事件功能创建检查点监控会话，通过定义特定事件并设置数据捕获参数来跟踪数据库检查点的开始与结束，以便进行性能调优。

CREATE EVENT SESSION Monitor_Checkpoint ON SERVER
ADD EVENT sqlserver.checkpoint_begin, ADD EVENT sqlserver.checkpoint_end
(
ACTION (sqlserver.database_id, sqlserver.sql_text)
)
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
ALTER EVENT SESSION Monitor_Checkpoint ON SERVER
STATE = START;

SELECT
FinalData.x.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.x.value ('@timestamp', 'nvarchar(50)') AS CheckpointStartTime,
db_name(FinalData.x.value ('data(data/value)[1]', 'nvarchar(50)')) AS DatabaseID,
FinalData.x.value ('(action/.)[2]', 'nvarchar(200)') AS SQLText
FROM
(SELECT CAST(st.target_data AS XML) XMLData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON
s.address = st.event_session_address
WHERE st.target_name = 'ring_buffer'
AND s.name = 'Monitor_Checkpoint'
) data
CROSS APPLY XMLData.nodes ('//RingBufferTarget/event') AS FinalData (x);