locked
Trace files RRS feed

  • Question

  • We were requested to create a job that will collect trace information related to deadlocks on a set of servers.

    I've read that running trace\profiler would incur performance cost when run for a longer period.

    And the particular servers doesn't have frequent deadlock issues. In this case, are we good to create one such job or no?

    Or can we check for timings when the deadlock is reported & have the profiler run once to understand the issue.

    Could you please provide your suggestions.

    Friday, December 13, 2019 8:22 AM

Answers

All replies

  • you can use extended events session..

    see this article for set up :

    https://www.mssqltips.com/sqlservertip/5658/capturing-sql-server-deadlocks-using-extended-events/


    Hope it Helps!!

    • Marked as answer by SSG92 Friday, December 13, 2019 9:08 AM
    Friday, December 13, 2019 8:30 AM
  • Thank You!
    Friday, December 13, 2019 9:08 AM
  • Adding that the xml_deadlock_report extended event is already included in the system_health trace so you don't need a separate trace to capture deadlock info. Below is an example query to return the 10 most recent deadlocks.

    --get xml_deadlock_report from system_health session file target
    WITH
    	  --get full path to current system_health trace file
    	  CurrentSystemHealthTraceFile AS (
    		SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
    		FROM sys.dm_xe_session_targets
    		WHERE
    			target_name = 'event_file'
    			AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    	)
    	  --get trace folder name and add base name of system_health trace file with wildcard
    	, BaseSystemHealthFileName AS (
    		SELECT 
    			REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
    		FROM CurrentSystemHealthTraceFile
    		)
    	  --get xml_deadlock_report events from all system_health trace files
    	, DeadLockReports AS (
    		SELECT CAST(event_data AS xml) AS event_data
    		FROM BaseSystemHealthFileName
    		CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
    		WHERE xed.object_name like 'xml_deadlock_report'
    	)
    --display 10 most recent deadlocks
    SELECT TOP 10
    	  DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
    	, event_data AS DeadlockReport
    FROM DeadLockReports
    ORDER BY LocalTime ASC;
    GO
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, December 13, 2019 10:54 AM