I thought the only way to get Deadlock info out of SQL was to enable trace flags 1204 and 1222 (see http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx) but this isn’t actually the case . ..  behold  .  . .

SELECT
 --first resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[1]' , 'int') ) as process1,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[1]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[1]' , 'varchar(max)') AS text ,
 
 
 --second resource
 DB_NAME((CAST (XEventData. XEvent.value ('(data/value)[1]' , 'varchar(max)' ) AS XML)).value ('(deadlock/process-list/process/@currentdb)[2]' , 'int') ) as process2,
 --lockmode
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/@lockMode)[2]' , 'varchar(1)') as lockmode,
 --what sql was running
 (CAST( XEventData.XEvent .value( '(data/value)[1]' , 'varchar(max)' ) AS XML )).value( '(deadlock/process-list/process/inputbuf)[2]' , 'varchar(max)') AS text ,
 
 
 XEventData .XEvent. value('(@timestamp)[1]' , 'datetime' ) as Timestamp,
 
 
 --the xml
 CAST(XEventData .XEvent. value('(data/value)[1]' , 'varchar(max)' ) AS XML) AS DeadlockGraph
 
 
FROM
 (
 SELECT
 CAST(target_data AS XML ) AS TargetData
 FROM
 sys.dm_xe_session_targets st
 JOIN sys .dm_xe_sessions s
 ON s. address = st. event_session_address
 WHERE
 name = 'system_health'
 ) AS Data
 CROSS APPLY TargetData.nodes ('//RingBufferTarget/event' ) AS XEventData ( XEvent )
WHERE
 XEventData .XEvent. value('@name' , 'varchar(4000)' ) = 'xml_deadlock_report'

 Try it 😀

Query plans possess vital information which can help a DBA to make optimization decisions.

The T-SQL below will retrieve the query plan for database of your choice. By extending the where clause in the sub query you can be more precise about the query plans you return based on particular commands for example. The sub query could even be replaced with your own SPID.

select query_plan
from sys .dm_exec_requests as er
cross apply sys. dm_exec_query_plan(er .plan_handle)
where session_id in ( select spid from sys. sysprocesses where dbid = DB_ID('database_name' ))

Happy viewing :)