Monitor System Activity Using Extended Events

Monitor System Activity Using Extended Events
------------------------------------------------------------


CREATE EVENT SESSION test0
ON SERVER
ADD EVENT sqlserver.checkpoint_begin,
ADD EVENT sqlserver.checkpoint_end
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
go

--Add the bucketing target with 32 buckets to count the number of checkpoints based on the database --ID.

ALTER EVENT SESSION test0
ON SERVER
ADD TARGET package0.histogram
(
      SET slots = 32, filtering_event_name = 'sqlserver.checkpoint_end', source_type = 0, source = 'database_id'
)
go
--Issue the following statements to add the ETW target. This will enable you to see the begin and end --events, which is used to determine how long the checkpoint takes.


ALTER EVENT SESSION test0
ON SERVER
ADD TARGET package0.etw_classic_sync_target
go

--Issue the following statements to start the session and begin event collection.
ALTER EVENT SESSION test0
ON SERVER
STATE = start
go

USE tempdb
      checkpoint
go

--Issue the following statements to cause three events to fire.
USE master
      checkpoint
      checkpoint
go
--Issue the following statements to view the event counts.
SELECT CAST(xest.target_data AS xml) Bucketizer_Target_Data_in_XML
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'histogram' AND xes.name = 'test0'
go


I  have executed checkpoint 21 times, hence output is like
You will notice Slot count = 21. Means this capture how may times checkpoint has run on the sql ser ver.

Thanks for Reading..