About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

Extended Events query to get DeadLocks/ blocked_process/SQL_QueryIOStats/Error_reported in case severity greater than 10 & then Alter the Event.


  1. Extended Events query to get DeadLocks
  2. Extended Events query to get blocked_process
  3. Extended Events query to get LongRunningQuery
  4. Extended Events query to get SQL_QueryIOStats
  5. Extended Events query to get error_trap
  6. Use the system_health Session
  7. Viewing the Session Data
  8. Restoring the system_health Session 
  9. Find the Objects That Have the Most Locks Taken on Them
  10. Determine Which Queries Are Holding Locks


Extended Events query to get  DeadLocks
====================================


 CREATE EVENT SESSION DeadLocks
ON SERVER
ADD EVENT
   sqlserver.lock_deadlock
    (
     ACTION
       (
       sqlserver.database_id,
       sqlserver.client_app_name,
       sqlserver.sql_text
      )
   )
ADD TARGET package0.asynchronous_file_target
( SET filename = 'c:\ffxfer\DeadLocks_Capture.xel',
metadatafile = 'c:\ffxfer\DeadLocks_Capture.mta',
max_file_size = 10,
max_rollover_files = 10);

GO


ALTER EVENT SESSION DeadLocks
ON SERVER
STATE = START
GO



 How to collect  data to temp table
---------------------------------------



DECLARE @xel_filename varchar(256) = ''D:\ffxfer\DeadLocks_Capture.xel '
DECLARE @mta_filename varchar(256) = ' D:\ffxfer\DeadLocks_Capture.mta'

SELECT CONVERT(xml, event_data) as Event_Data
INTO #deadlockdata
FROM sys.fn_xe_file_target_read_file(@xel_filename, @mta_filename, NULL, NULL)


4. how to retrieve data.


select * from #deadlockdata

=========================================================
Extended Events query to get  blocked_process
====================================

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\ffxfer\blocked_process.xel',
     metadatafile = N'c:\ffxfer\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO





EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;


=========================================================
Extended Events query to get  LongRunningQuery
====================================


-- Create Event
CREATE EVENT SESSION LongRunningQuery
ON SERVER
-- Add event to capture event
ADD EVENT sqlserver.sql_statement_completed
(
-- Add action - event property
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
-- Predicate - time 1000 milisecond
WHERE sqlserver.sql_statement_completed.duration > 1000
)
-- Add target for capturing the data - XML File
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\ffxfer\LongRunningQuery.xel', metadatafile='c:\ffxfer\LongRunningQuery.xem'),
-- Add target for capturing the data - Ring Buffer
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
-- Enable Event
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=START
GO
===========================================================

Extended Events query to get  SQL_QueryIOStats
====================================


CREATE EVENT SESSION SQL_QueryIOStats
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
  (ACTION (sqlserver.database_id,
           sqlserver.sql_text,
                sqlserver.nt_username,
                sqlserver.session_id,
                sqlserver.query_hash,
                sqlserver.query_plan_hash)
    )
ADD TARGET package0.asynchronous_file_target
(SET filename = 'c:\ffxfer\SQLQueryIOStats.xel')
WITH (MAX_DISPATCH_LATENCY= 5 SECONDS);


ALTER EVENT SESSION SQL_QueryIOStats ON SERVER
STATE=START
GO

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  1. Extended Events query to get error_trap



DROP EVENT SESSION [error_trap] ON SERVER
GO
====================================================



CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
    (
        ACTION    (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
        WHERE    ([severity]>10)
    )
ADD TARGET package0.event_file
    (
        SET filename=N'E:\BigDatabase\xEvents\error_trap.xel'
    )
WITH
    (     
        STARTUP_STATE=OFF
    )
GO
ALTER EVENT SESSION [error_trap] ON SERVER
STATE = START;
GO


ALTER EVENT SESSION [error_trap] ON SERVER
STATE = START;
GO

in case if you want to change the severity to 15

----------------------------------------------------------

ALTER EVENT SESSION [error_trap] ON SERVER
DROP EVENT sqlserver.error_reported
ALTER EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION(package0.collect_system_time,package0.last_error,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.tsql_frame,
sqlserver.tsql_stack,
sqlserver.username)
    WHERE ([package0].[greater_than_int64]([severity],(15))))
GO
---------------------------------------------------------------------------------

if you want to see the data in tabular format


IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e
go
WITH cte AS
(
    SELECT
        CAST(event_data AS XML) AS event_data
    FROM
        sys.fn_xe_file_target_read_file('E:\BigDatabase\xEvents\error_trap*.xel', NULL, NULL, NULL)
),
cte2 AS
(
    SELECT
        event_number = ROW_NUMBER() OVER (ORDER BY T.x)
    ,    event_name = T.x.value('@name', 'varchar(100)')
    ,    event_timestamp = T.x.value('@timestamp', 'datetimeoffset')
    ,    event_data
    FROM
        cte 
    CROSS APPLY
        event_data.nodes('/event') T(x)
)
SELECT * INTO #e FROM cte2
go
WITH cte3 AS
(
    SELECT
        c.event_number,
        c.event_timestamp,
        --data_field = T2.x.value('local-name(.)', 'varchar(100)'),
        data_name = T2.x.value('@name', 'varchar(100)'),
        data_value = T2.x.value('value[1]', 'varchar(max)'),
        data_text = T2.x.value('text[1]', 'varchar(max)')
    FROM
        #e c
    CROSS APPLY
        c.event_data.nodes('event/*') T2(x)
),
cte4 AS
(
    SELECT
        *
    FROM
        cte3
    WHERE
        data_name IN ('error_number', 'severity', 'message', 'database_name', 'database_id', 'client_hostname', 'client_app_name', 'collect_system_time', 'username')
)
SELECT
    *
FROM
    cte4
PIVOT
    (MAX(data_value) FOR data_name IN ([error_number], [severity], [message], database_name, database_id, username, client_hostname, client_app_name, collect_system_time)) T
WHERE
    [severity] > 10
ORDER BY
    event_timestamp DESC
go

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Converting SQL Trace to Extended Events in SQL Server 2012



IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'XE_Default_Trace')
 DROP EVENT SESSION [XE_Default_Trace] ON SERVER;
GO
CREATE EVENT SESSION [XE_Default_Trace]
ON SERVER
/* Audit Login Failed is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Database Scope GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Addlogin Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login Change Property Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Login to Server Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add DB User Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Member to DB Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Backup/Restore Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit DBCC Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Audit Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Database Owner is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object Take Ownership Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Server Alter Trace Event is not implemented in Extended Events it may be a Server Audit Event */
ADD EVENT sqlserver.database_file_size_change(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
 )
),
/* Log File Auto Grow is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Data File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Log File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
ADD EVENT sqlserver.database_mirroring_state_change(
 ACTION 
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
 )
),
ADD EVENT sqlserver.errorlog_written(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — Severity not implemented in XE for this event
   — State not implemented in XE for this event
   — Error not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_started(
 ACTION 
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_stopped(
 ACTION 
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.hash_warning(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_column_statistics(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_join_predicate(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.object_altered(
 ACTION 
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_created(
 ACTION 
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_deleted(
 ACTION 
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.plan_guide_unsuccessful(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — TextData not implemented in XE for this event
 )
),
ADD EVENT sqlserver.server_memory_change(
 ACTION 
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.server_start_stop(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
 )
),
ADD EVENT sqlserver.sort_warning(
 ACTION 
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
)
ADD TARGET package0.event_file
(
 SET filename = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XE_Default_Trace.xel',
  max_file_size = 20,
  max_rollover_files = 5
)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


How to read the data and collect  data to temp table


DECLARE @xel_filename varchar(256) = 'D:\ffxfer\DeadLocks_Capture.xel '
DECLARE @mta_filename varchar(256) = 'D:\ffxfer\DeadLocks_Capture.mta'

SELECT CONVERT(xml, event_data) as Event_Data
INTO #File_Data
FROM sys.fn_xe_file_target_read_file(@xel_filename, @mta_filename, NULL, NULL)


 how to retrieve data.

select * from #File_Data


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Use the system_health Session

The system_health session is an Extended Events session that is included by default with SQL Server. This session starts automatically when the SQL Server Database Engine starts, and runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues in the Database Engine. Therefore, we recommend that you do not stop or delete the session.
The session collects information that includes the following:
·         The sql_text and session_id for any sessions that encounter an error that has a severity >=20.
·         The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.
·         A record of any non-yielding scheduler problems. (These appear in the SQL Server error log as error 17883.)
·         Any deadlocks that are detected.
·         The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds.
·         The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.
·         The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.
·         The callstack and session_id for CLR allocation and virtual allocation failures.
·         The ring_buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
·         System component results from sp_server_diagnostics.
·         Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
·         CLR Allocation failures.
·         Connectivity errors using connectivity_ring_buffer_recorded.
·         Security errors using security_error_ring_buffer_recorded.

Viewing the Session Data

The session uses the ring buffer target to store the data. To view the session data, use the following query:

SELECT CAST(xet.target_data as xml) FROM sys.dm_xe_session_targets xet  
JOIN sys.dm_xe_sessions xe  
ON (xe.address = xet.event_session_address)  
WHERE xe.name = 'system_health' 
To view the session data from the event file, use the Extended Events user interface available in Management Studio. For more information, see Advanced Viewing of Target Data from Extended Events in SQL Server.

Restoring the system_health Session

If you delete the system_health session, you can restore it by executing the u_tables.sql file in Query Editor. This file is located in the following folder, where C: represents the drive where you installed the SQL Server program files:
C:\Program Files\Microsoft SQL Server\MSSQL13.<instanceid>\MSSQL\Install
Be aware that after you restore the session, you must start the session by using the ALTER EVENT SESSION statement or by using the Extended Events node in Object Explorer. Otherwise, the session starts automatically the next time that you restart the SQL Server service.

Extracting Deadlock information without keying the path:-
If System_health is  is up and running on a system and you want to retrive deadlock graph then below query will help you in retrieving the details..



CREATE TABLE #errorlog (
                                                                                                LogDate DATETIME
                                                                                                , ProcessInfo VARCHAR(100)
                                                                                                , [Text] VARCHAR(MAX)
                                                                                                );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT
                CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
                CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime')
                AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)

WHERE OBJECT_NAME like 'xml_deadlock_report';

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Find the Objects That Have the Most Locks Taken on Them

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/find-the-objects-that-have-the-most-locks-taken-on-them?view=sql-server-2017

Determine Which Queries Are Holding Locks

 https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/determine-which-queries-are-holding-locks?view=sql-server-2017