How to create an extended event session for capturing a deadlock occurring on a database to trace into a file?

Problem: How to create an extended event session for capturing a deadlock occurring on a database to trace into a file?

Solution: Create an Extended Events Session Starting SQL Server 2012

SQL server management studio gives us two options for creating a session in the extended events:

·         T-SQL.

·         Session wizard from the object explorer.

You can create Extended Events sessions to diagnose SQL Server tracing, which enables you to resolve issues such as the following:

·         Find your most expensive queries

·         Find root causes of latch contention

·         Find a query that is blocking other queries

·         Troubleshoot excessive CPU usage caused by query recompilation

·         Troubleshoot deadlocks

Example:

In this example, I would like to show how we can create an extended event session to capture deadlock and apply certain filters using wizard and via T-SQL as well:

Example 1: creating an extended event session using wizard.

Step 1: right click on the sessions folder from extended sessions and open the new session wizard

Step 2: after the you click new this dialog will open hit next.

Step 3: Create session name and follow the naming convention.

Step 4: Use template or custom template.

Step 5: select the events to be captured.

Step 6: select the option and toggle to the next window to hit the arrow.

Step 7: after selecting the option hit next

Step 8: select values to be captured in the xml

Step 9: filter the option to specify particular clauses and hit next.

Step 10: select the file name and browse to the folder where to save.

Step 11: Additional options of when a spill occurs how many events to be saved.

Step 12:  Final summary of the options selected. at this point you can script out to get the create script.

Step 13: Success! and click the options to start on or not.

Step 14: create and test dead locks.

Example 2: Creating a session via deadlock event session using T-SQL.

CREATE EVENT SESSION [DeadLocks] ON SERVER

ADD EVENT sqlserver.database_xml_deadlock_report(

    ACTION(package0.process_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_instance_name,sqlserver.sql_text)

   )

ADD TARGET package0.event_file(SET filename=N'C:\Temp\Deadlocks.xel',max_file_size=(256000),max_rollover_files=(3)),

ADD TARGET package0.ring_buffer(SET occurrence_number=(100))

WITH (STARTUP_STATE=ON)

GO

CREATE EVENT SESSION [Deadlocks_2] ON SERVER

ADD EVENT sqlserver.xml_deadlock_report

ADD TARGET package0.event_file(SET filename=N'Deadlocks', max_file_size=(250), max_rollover_files=(3))

WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=ON)

GO

ALTER EVENT SESSION [Deadlocks] ON SERVER STATE = START

GO