Extended Events Quick Test Code

Below is an example of creating, starting, viewing, stopping and dropping an extended event in SQL Server 2008.

-- Code to create an extended event
CREATE EVENT SESSION sqlkevin
ON SERVER
-- obtain events using SELECT * FROM sys.dm_xe_objects WHERE object_type = 'event' ORDER BY name
-- sqlserver below is used by comparing package_guid in sys.dm_xe_objects with name in SELECT * FROM sys.dm_xe_packages
ADD EVENT sqlserver.checkpoint_begin
-- obtain target for results by using SELECT * FROM sys.dm_xe_objects WHERE object_type = 'target'
-- package0 below is used by comparing package_guid in sys.dm_xe_objects with name in sys.dm_xe_packages
ADD TARGET package0.asynchronous_file_target
-- obtain SET permissions for this target from SELECT * FROM sys.dm_xe_object_columns WHERE object_name = asynchronous_file_target'
-- re below, look in the description column in sys.dm_xe_object_columns for more info
(SET filename = N'C:\sqlkevin_testlog.xel', metadatafile = N'C:\sqlkevin_testmetadata.xem')

-- Code to start an extended event
ALTER EVENT SESSION sqlkevin
ON SERVER
STATE = START

-- Code to see extended event
SELECT * FROM sys.server_event_sessions
SELECT * FROM sys.server_event_session_actions
SELECT * FROM sys.server_event_session_events
SELECT * FROM sys.server_event_session_fields
SELECT * FROM sys.server_event_session_targets
SELECT * FROM sys.dm_xe_sessions
SELECT * FROM sys.dm_xe_session_targets
SELECT * FROM sys.dm_xe_session_events
SELECT * FROM sys.dm_xe_session_object_columns

-- Create an Event
CHECKPOINT

-- Read the output file
-- Note that if there is no data to check point you may have to retry the below select statement until a checkpoint occurs
SELECT * FROM sys.fn_xe_file_target_read_file
('C:\sqlkevin_testlog*.xel', 'C:\sqlkevin_testmetadata*.xem', null, null)

-- Code to stop an extended event
ALTER EVENT SESSION sqlkevin
ON SERVER
STATE = STOP

-- Code to remove an extended event
DROP EVENT SESSION sqlkevin
ON SERVER