SQL Server Extended Events

DROP EVENT SESSION (Transact-SQL)

Drops an event session.

SQL> DROP EVENT SESSION [Collect_Deadlocks_New] ON SERVER

View all the event session that exists in SQL Server

SQL> select * from sys.server_event_sessions

Create a Dead Lock Ex. session in SQL Server 2008

use [master];

CREATE EVENT SESSION [Collect_Deadlocks_New] ON SERVER

    ADD EVENT sqlserver.xml_deadlock_report(

        ACTION (

            package0.collect_system_time,

            sqlos.task_time,

            sqlserver.client_app_name,

            sqlserver.client_hostname,

            sqlserver.database_id,

            sqlserver.is_system,

            sqlserver.username

              )

                                            )

    ADD TARGET package0.asynchronous_file_target(

        SET filename           = N'C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks.xel',

            max_file_size      = (512),

            max_rollover_files = (3)

                                                  )

WITH (

     MAX_MEMORY            = 4096 KB,

     EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,

     MAX_DISPATCH_LATENCY  = 20 SECONDS,

     MAX_EVENT_SIZE        = 0 KB,

     MEMORY_PARTITION_MODE = NONE,

     TRACK_CAUSALITY       = OFF,

     STARTUP_STATE         = ON

);

Create a Dead Lock Ex. session in SQL Server 2012 or higher

use [master];

CREATE EVENT SESSION [Collect-Deadlocks] ON SERVER

    ADD EVENT sqlserver.xml_deadlock_report(

        ACTION (

            package0.collect_system_time,

            sqlos.task_time,

            sqlserver.client_app_name,

            sqlserver.client_hostname,

            sqlserver.database_id,

            sqlserver.is_system,

            sqlserver.username

        )

    )

    ADD TARGET package0.event_file(

        SET filename           = N'C:\Collect-Deadlocks.xel',

            max_file_size      = (512),

            max_rollover_files = (3)

    )

WITH (

     MAX_MEMORY            = 4096 KB,

     EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,

     MAX_DISPATCH_LATENCY  = 20 SECONDS,

     MAX_EVENT_SIZE        = 0 KB,

     MEMORY_PARTITION_MODE = NONE,

     TRACK_CAUSALITY       = OFF,

     STARTUP_STATE         = ON

);

sys.fn_xe_file_target_read_file

Reads files that are created by the Extended Events asynchronous file target. One event, in XML format, is returned per row.  Dead lock info we can get from event data column.

Warning

SQL Server 2008 and SQL Server 2008 R2 accept trace results generated in XEL and XEM format. SQL Server 2012 (11.x) Extended Events only support trace results in XEL format. We recommend that you use SQL Server Management Studio to read trace results in XEL format.

Syntax:

sys.fn_xe_file_target_read_file ( path, mdpath, initial_file_name, initial_offset ) 

Example:

In SQL 2008

SELECT * FROM sys.fn_xe_file_target_read_file('C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks_0_132216503855490000.xel',

 'C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks_0_132216503855500000.xem', null, null);

In SQL 2012 +

SELECT * FROM sys.fn_xe_file_target_read_file('C:\Collect-Deadlocks_0_132216492977010000.xel', null, null, null);

Start and Stop a Session

ALTER EVENT SESSION <YOUR_XE_SESSION_NAME>

ON SERVER 

STATE = START;

GO 

ALTER EVENT SESSION <YOUR_XE_SESSION_NAME>

ON SERVER 

STATE = STOP;

GO 

Session Status check

SELECT rs.*

FROM sys.dm_xe_sessions RS

RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name

WHERE es.name like '<YOUR_XE_SESSION_NAME>'