Extended Events

Question 1

How do you create an Extended Event?

Answer 1

create event session log_growth on server
ADD EVENT sqlserver.databases_log_growth
( action (sqlserver.database_id))
add target package0.asynchronous_file_target
(SET filename=N’C:\xevent\log_growth.xel’, metadatafile=N’C:\xevent\log_growth.xem’);

Question 2

TSQL to start Extended Event session?

Answer 2

alter event session log_growth on server state = start;

Question 3

TSQL to stop Extended Event session?

Answer 3

alter event session log_growth on server state = stop;

Question 4

TSQL to read Extended Event file?

Answer 4

select *
from sys.fn_xe_file_target_read_file
(N’C:\xevent\log_growth*.xel’, N’C:\xevent\log_growth*.xem’, null, null)

Question 5

TSQL to view all created Extended Events sessions?

Answer 5

SELECT *
FROM sys.server_event_sessions

Question 6

TSQL to view all running Extended Events sessions?

Answer 6

SELECT *
FROM sys.dm_xe_sessions

Question 7

TSQL to drop Extended Event session?

Answer 7

drop event session <session_name> on server

Question 8

TSQL to view registered packages?

Answer 8

SELECT *
FROM sys.dm_xe_packages

Question 9

TSQL to view available Extended Events objects?

Answer 9

SELECT *
FROM sys.dm_xe_objects

Question 10

TSQL to view schema information for all the Extended Events objects?

Answer 10

SELECT *
FROM sys.dm_xe_object_columns

Question 11

TSQL to view map values?

Answer 11

SELECT *
FROM sys.dm_xe_map_values

Question 12

TSQL to view information about session targets?

Answer 12

SELECT *
FROM sys.dm_xe_session_targets

Question 13

TSQL to view information about session events?

Answer 13

SELECT *
FROM sys.dm_xe_session_events

Question 14

TSQL to view information about event session actions?

Answer 14

SELECT *
FROM sys.dm_xe_session_event_actions

Question 15

TSQL to view configuration values for objects that are bound to a session?

Answer 15

SELECT *
FROM sys.dm_xe_session_object_columns

Question 16

TSQL to view each event in a event session?

Answer 16

SELECT *
FROM sys.server_event_session_events

Question 17

TSQL to view each action in a event session?

Answer 17

SELECT *
FROM sys.server_event_session_actions

Question 18

TSQL to view each event target in a event session?

Answer 18

SELECT *
FROM sys.server_event_session_targets

Question 19

TSQL to view each customizable column that was explicitly set on events and targets?

Answer 19

SELECT *
FROM sys.server_event_session_fields