Extended Events via T-SQL


IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
DROP EVENT SESSION LongRunningQuery ON SERVER
GO

-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER

-- Add event to capture event
ADD EVENT sqlserver.rpc_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000 
AND sqlserver.client_hostname <> 'A' 
)
),
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000
AND sqlserver.client_hostname <> 'A'
)
),

ADD EVENT sqlserver.module_end
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000000
AND sqlserver.client_hostname <> 'A'
)
)
ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\LongRunningQuery.xet', metadatafile='C:\New folder\LongRunningQuery.xem'),
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
-- Enable Event,
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO
 

DECLARE	@XMLLongRunning XML
SELECT	@XMLLongRunning = CAST(dt.target_data AS XML)
FROM sys.dm_xe_session_targets dt
JOIN	sys.dm_xe_sessions ds
		ON ds.Address = dt.event_session_address
JOIN	sys.server_event_sessions ss
		ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'LongRunningQuery'
 
select T.N.value('local-name(.)', 'varchar(max)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @XMLLongRunning.nodes('/*/@*') as T(N) 


-- Stop the event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=STOP
GO
 
-- Clean up. Drop the event
DROP EVENT SESSION LongRunningQuery
ON SERVER
GO
 
------------------------------
--Shred XML for easy reading--
------------------------------
 
--pull into temp table for speed and to make sure the ID works right
if object_id('tempdb..#myxml') is not null
DROP TABLE #myxml
CREATE TABLE #myxml (id INT IDENTITY, actual_xml XML)
INSERT INTO #myxml
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file
('C:\New folder\LongRunningQuery*.xet',
'C:\New folder\LongRunningQuery*.xem',
NULL, NULL)
 
 
--Now toss into temp table, generically shredded
if object_id('tempdb..#ParsedData') is not null
DROP TABLE #ParsedData
CREATE TABLE #ParsedData (id INT, Actual_Time DATETIME, EventType sysname, ParsedName sysname, NodeValue VARCHAR(MAX))
INSERT INTO #ParsedData 
SELECT id,
DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
EventType,
ParsedName,
NodeValue
FROM (
SELECT id,
A.B.value('@name[1]', 'varchar(128)') AS EventType,
A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
FROM [#myxml]
CROSS APPLY actual_xml.nodes('/*') AS A (B)
CROSS APPLY actual_xml.nodes('//*') AS X (N)
) T
WHERE NodeName = 'value'
DECLARE @SQL AS VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)
SELECT @Columns=
COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
FROM
(
SELECT DISTINCT ParsedName
FROM #ParsedData
		
WHERE ParsedName <> 'tsql_stack'
) AS B
SET @SQL='
SELECT Actual_Time, EventType,' + @Columns + ' FROM
(
SELECT id, EventType, Actual_Time, ParsedName, NodeValue FROM
#ParsedData ) AS source
PIVOT
(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
)AS pvt order by actual_time, attach_activity_id'
EXEC (@sql) 


--The following example shows how to drop an event session.

DROP EVENT SESSION evt_spin_lock_diagnosis
ON SERVER

--Lists all the event session definitions that exist in SQL Server.

SELECT  * FROM sys.server_event_sessions 

--Returns a row for each action on each event of an event session.

SELECT  * FROM sys.server_event_session_actions 

--Returns a row for each event in an event session

SELECT  * FROM sys.server_event_session_events 

--Returns a row for each customizable column that was explicitly set on events and targets

SELECT  * FROM sys.server_event_session_fields 

--Returns a row for each event target for an event session.

SELECT  * FROM sys.server_event_session_targets 


--view the Extended Events equivalents to SQL Trace events using Query Editor

USE MASTER;
GO

SELECT DISTINCT
tb.trace_event_id,
te.name AS 'Event Class',
em.package_name AS 'Package',
em.xe_event_name AS 'XEvent Name',
tb.trace_column_id,
tc.name AS 'SQL Trace Column',
am.xe_action_name as 'Extended Events action'
FROM (sys.trace_events te LEFT OUTER JOIN sys.trace_xe_event_map em
ON te.trace_event_id = em.trace_event_id) LEFT OUTER JOIN sys.trace_event_bindings tb
ON em.trace_event_id = tb.trace_event_id LEFT OUTER JOIN sys.trace_columns tc
ON tb.trace_column_id = tc.trace_column_id LEFT OUTER JOIN sys.trace_xe_action_map am
ON tc.trace_column_id = am.trace_column_id
where tc.name like '%completed%'
ORDER BY te.name, tc.name

select * from sys.trace_Events


--To get the fields for all events 

select p.name package_name, o.name event_name, c.name event_field, c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on o.package_guid = p.guid
join sys.dm_xe_object_columns c
on o.name = c.object_name
where o.object_type = 'event'
order by package_name, event_name

 --WHEN USING TSQL TO CREATE AN EXTENDED EVENT, THESE ARE THE 'STEPS' TO FOLLOW:

 --1:  EXECUTE THE SCRIPT TO DROP ANY EXISTING SESSIONS

IF EXISTS(SELECT * FROM sys.server_event_sessions 
WHERE name='FindProblemQueries')
DROP EVENT SESSION FindProblemQueries ON SERVER
GO

--2: CREATE AN EVENT SESSION

CREATE EVENT SESSION FindProblemQueries
ON SERVER

--3: SELECT THE EVENTS YOU WANT TO CAPUTRE WITH THE ACTIONS (GLOBAL FIELDS) AND PREDICATES (FILTERS)

ADD EVENT sqlserver.sql_statement_completed
(ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (duration > 1000
AND sqlserver.client_hostname = 'DESKTOP-QMOOH4U'))

--4 ADD THE TARGET (WHERE YOU WANT TO SAVE THE FILES)

ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\FindProblemQueries.xet', metadatafile='C:\New folder\FindProblemQueries.xem'),

--SAVE IN MEMORY

ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
 --STEPS 1 THROUGH 4 SHOULD BE EXECUTED AT ONCE
 
--5: START THE SESSION

ALTER EVENT SESSION FindProblemQueries ON SERVER
STATE=START
GO
 
 --INSERT DATA

 SELECT TOP 1000 [Fname]
      ,[Lname]
      ,[Phone]
  FROM [SQL2].[dbo].[People2]
 
--7: START THE SESSION

ALTER EVENT SESSION FindProblemQueries ON SERVER
STATE=STOP
GO
 
--8: DROP THE SESSION (DROPPING THE SESSION DOES NOT DELETE THE FILES!!!)

DROP EVENT SESSION FindProblemQueries
ON SERVER
GO

 
--9: CREATE A TEMP TABLE FOR ANALYSIS

if object_id('tempdb..#myxml') is not null
DROP TABLE #myxml
CREATE TABLE #myxml (id INT IDENTITY, actual_xml XML)
INSERT INTO #myxml
SELECT CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file
('C:\New folder\FindProblemQueries*.xet',
'C:\New folder\FindProblemQueries*.xem',
NULL, NULL)
 
 
--10: INSERT INTO TEMP TABLE TO VIEW DATA COLLECTED

if object_id('tempdb..#ParsedData') is not null
DROP TABLE #ParsedData
CREATE TABLE #ParsedData (id INT, Actual_Time DATETIME, EventType sysname, ParsedName sysname, NodeValue VARCHAR(MAX))
INSERT INTO #ParsedData 
SELECT id,
DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
EventType,
ParsedName,
NodeValue
FROM (
SELECT id,
A.B.value('@name[1]', 'varchar(128)') AS EventType,
A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
FROM [#myxml]
CROSS APPLY actual_xml.nodes('/*') AS A (B)
CROSS APPLY actual_xml.nodes('//*') AS X (N)
) T
WHERE NodeName = 'value'
DECLARE @SQL AS VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)
SELECT @Columns=
COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
FROM
(
SELECT DISTINCT ParsedName
FROM #ParsedData
		
WHERE ParsedName <> 'tsql_stack'
) AS B
SET @SQL='
SELECT Actual_Time, EventType,' + @Columns + ' FROM
(
SELECT id, EventType, Actual_Time, ParsedName, NodeValue FROM
#ParsedData ) AS source
PIVOT
(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
)AS pvt order by actual_time, attach_activity_id'
EXEC (@sql) 




--Lists all the event session definitions that exist in SQL Server.

SELECT  * FROM sys.server_event_sessions 

--Returns a row for each action on each event of an event session.

SELECT  * FROM sys.server_event_session_actions 

--Returns a row for each event in an event session

SELECT  * FROM sys.server_event_session_events 

--Returns a row for each customizable column that was explicitly set on events and targets

SELECT  * FROM sys.server_event_session_fields 

--Returns a row for each event target for an event session.

SELECT  * FROM sys.server_event_session_targets 


--view the Extended Events equivalents to SQL Trace events using Query Editor

USE MASTER;
GO

SELECT DISTINCT
tb.trace_event_id,
te.name AS 'Event Class',
em.package_name AS 'Package',
em.xe_event_name AS 'XEvent Name',
tb.trace_column_id,
tc.name AS 'SQL Trace Column',
am.xe_action_name as 'Extended Events action'
FROM (sys.trace_events te LEFT OUTER JOIN sys.trace_xe_event_map em
ON te.trace_event_id = em.trace_event_id) LEFT OUTER JOIN sys.trace_event_bindings tb
ON em.trace_event_id = tb.trace_event_id LEFT OUTER JOIN sys.trace_columns tc
ON tb.trace_column_id = tc.trace_column_id LEFT OUTER JOIN sys.trace_xe_action_map am
ON tc.trace_column_id = am.trace_column_id
where tc.name like '%completed%'
ORDER BY te.name, tc.name

select * from sys.trace_Events


--To get the fields for all events 

select p.name package_name, o.name event_name, c.name event_field, 
c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on o.package_guid = p.guid
join sys.dm_xe_object_columns c
on o.name = c.object_name
where o.object_type = 'event'
order by package_name, event_name

All the events that are available


--all the events that are available

SELECT p.name, c.event, k.keyword, c.channel, c.description FROM
   (
   SELECT event_package = o.package_guid, o.description, 
   event=c.object_name, channel = v.map_value
   FROM sys.dm_xe_objects o
   LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
   INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name 
   AND c.column_value = cast(v.map_key AS nvarchar)
   WHERE object_type = 'event' AND (c.name = 'CHANNEL' or c.name IS NULL)
   ) c LEFT JOIN 
   (
   SELECT event_package = c.object_package_guid, event = c.object_name, 
   keyword = v.map_value
   FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v 
   ON c.type_name = v.name AND c.column_value = v.map_key 
   AND c.type_package_guid = v.object_package_guid
   INNER JOIN sys.dm_xe_objects o ON o.name = c.object_name 
   AND o.package_guid = c.object_package_guid
   WHERE object_type = 'event' AND c.name = 'KEYWORD' 
   ) k
   ON
   k.event_package = c.event_package AND (k.event=c.event or k.event IS NULL)
   INNER JOIN sys.dm_xe_packages p ON p.guid = c.event_package
ORDER BY 
--keyword , channel, event,
c.description

--To view which actions are available, use the following query:

SELECT p.name AS 'package_name', xo.name AS 'action_name', xo.description, xo.object_type
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
WHERE 
--xo.object_type = 'action'
--AND 
(xo.capabilities & 1 = 0 
OR xo.capabilities IS NULL)
ORDER BY p.name, xo.name



--To view which predicates are available for an event, use the following query, 
--replacing event_name with the name of the event for which you want to add a predicate:

SELECT *
FROM sys.dm_xe_object_columns
--WHERE object_name = 'event_name'
--AND column_type = 'data'

--view which global predicate sources are available, use the following query:

SELECT p.name AS package_name, xo.name AS predicate_name
   , xo.description, xo.object_type
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
--WHERE xo.object_type = 'pred_source'
--ORDER BY p.name, xo.name


--To view the list of available targets, use the following query:

SELECT p.name AS 'package_name', xo.name AS 'target_name'
   , xo.description, xo.object_type 
FROM sys.dm_xe_objects AS xo
JOIN sys.dm_xe_packages AS p
   ON xo.package_guid = p.guid
--WHERE xo.object_type = 'target'
--AND (xo.capabilities & 1 = 0
--OR xo.capabilities IS NULL)
--ORDER BY p.name, xo.name


--The following example creates an Extended Events session named IOActivity that captures the following information:

--Event data for completed file reads, including the associated Transact-SQL text for file reads where the file ID is equal to 1.

--Event data for completed file writes.

--Event data for when data is written from the log cache to the physical log file.

--The session sends the output to a file target.

CREATE EVENT SESSION IOActivity
ON SERVER

ADD EVENT sqlserver.file_read_completed
   (
   ACTION (sqlserver.sql_text)
   WHERE file_id = 1),
ADD EVENT sqlserver.file_write_completed,
ADD EVENT sqlserver.databases_log_flush

ADD TARGET package0.asynchronous_file_target 
   (SET filename = 'c:\temp\xelog.xel', metadatafile = 'c:\temp\xelog.xem')

Create the Event Session

-- Create the Event Session --
------------------------------
 
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
DROP EVENT SESSION LongRunningQuery ON SERVER
GO
-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.rpc_completed
(
-- Add action - event property ; can't add query_hash in R2
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
-- Predicate - time 1000 milisecond
WHERE (
duration > 1000 --by leaving off the event name, you can easily change to capture diff events
AND sqlserver.client_hostname <> 'A' --cant use NOT LIKE prior to 2012
)
--by leaving off the event name, you can easily change to capture diff events
),
ADD EVENT sqlserver.sql_statement_completed
-- or do sqlserver.rpc_completed, though getting the actual SP name seems overly difficult
(
-- Add action - event property ; can't add query_hash in R2
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
-- Predicate - time 1000 milisecond
WHERE (
duration > 1000
AND sqlserver.client_hostname <> 'A'
)
),
--adding Module_End. Gives us the various SPs called.
ADD EVENT sqlserver.module_end
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,
sqlserver.username, sqlserver.client_hostname, sqlserver.session_nt_username)
WHERE (
duration > 1000000
--note that 1 second duration is 1million, and we still need to match it up via the causality
AND sqlserver.client_hostname <> 'A'
)
)
-- Add target for capturing the data - XML File
-- You don't need this (pull the ring buffer into temp table),
-- but allows us to capture more events (without allocating more memory to the buffer)
--!!! Remember the files will be left there when done!!!
ADD TARGET package0.asynchronous_file_target(
SET filename='C:\New folder\LongRunningQuery.xet', metadatafile='C:\New folder\LongRunningQuery.xem'),
-- Add target for capturing the data - Ring Buffer. Can query while live, or just see how chatty it is
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 SECONDS, TRACK_CAUSALITY = ON)
GO
 
-- Enable Event, aka Turn It On
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO