SqlServer 并发事务：死锁跟踪（三）6种跟踪死锁的方法总结

方法一：Windows 性能计数器监控

命令行输入：perfmon  或者 perfmon /sys

选择实例：SQL Server :Locks \\  Number of DeadLocks/sec \\ _Total 

 （只能知道什么时候发生死锁，看不得具体死锁信息）

 

实时查看：

 

作业记录到文件：（可跟踪一段时间，耗系统性能很小）

 

 

方法二：打开profiler跟踪事件 locks: deadlock graph

（生成的死锁图可以拷贝出来XML格式的死锁信息）

 

或者导出上面为脚本在后台执行跟踪

 

[sql]  view plain  copy

 

1. --  后台跟踪  
2. declare @rc int    
3. declare @traceid int          --跟踪分配的编号    
4. declare @options int          --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8    
5. declare @tracefile nvarchar(500)--跟踪文件的存储路径    
6. declare @maxfilesize bigint   --跟踪文件的大小，单位是mb，默认5mb    
7. declare @endtime datetime     --停止跟踪的日期和时间,为NULL则表示一直跟踪    
8. declare @filecount int        --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效    
9. set @options = 2    
10. set @tracefile = N'E:\BlockedTraceName'    
11. set @maxfilesize = 20    
12. set @endtime = NULL  
13. set @filecount = 5    
14.     
15. --  exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL     
16. exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@endtime,@filecount    
17. if (@rc != 0) goto error    
18.   
19. -- Set the events  
20. declare @on bit  
21. set @on = 1  
22. exec sp_trace_setevent @TraceID, 148, 1, @on  
23. exec sp_trace_setevent @TraceID, 148, 12, @on  
24. exec sp_trace_setevent @TraceID, 148, 14, @on  
25. exec sp_trace_setevent @TraceID, 148, 26, @on  
26.   
27. -- Set the Filters  
28. declare @intfilter int  
29. declare @bigintfilter bigint  
30.   
31. -- Set the trace status to start  
32. exec sp_trace_setstatus @TraceID, 1  
33.   
34. -- display trace id for future references  
35. select TraceID=@TraceID  
36. goto finish  
37.   
38. error:   
39. select ErrorCode=@rc  
40.   
41. finish:   
42. go  
43.   
44. --查看跟踪记录  
45. select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData    
46. from ::fn_trace_gettable('E:\BlockedTraceName.trc',default)    

方法三：打开1222或者1204标志记录死锁,在sqlserver日志查看

 

[sql]  view plain  copy

 

1. DBCC TRACEON(1222,-1)    
2. DBCC TRACEON(1204,-1)  

 

 

[sql]  view plain  copy

 

1. 如果日志太多就不好找了，这时可以用系统扩展存储过程筛选！  
2.   
3.   
4. --查看是否死锁，确定死锁的的时间  
5. exec xp_readerrorlog 0,1,'deadlock victim',NULL,'2015-01-01','2015-01-10','ASC'  
6.   
7. --按时间查看sqlserver日志，即为死锁信息  
8. exec xp_readerrorlog 0,1,NULL,NULL,'2015-01-07 22:13:10','2015-01-07 22:13:11','ASC'  

 

方法四：扩展事件

 

[sql]  view plain  copy

 

1. IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'deadlock')  
2.     DROP EVENT SESSION [deadlock] ON SERVER   
3. GO  
4.   
5. --  跟踪的事件：database_xml_deadlock_report，xml_deadlock_report (跟踪一个即可，这里跟踪俩个)  
6. --  选择跟踪列：client_hostname，database_name，nt_username，plan_handle，sql_text  
7. CREATE EVENT SESSION [deadlock]   
8. ON SERVER   
9. ADD EVENT sqlserver.database_xml_deadlock_report(  
10.     ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.sql_text)),  
11. ADD EVENT sqlserver.xml_deadlock_report(  
12.     ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.sql_text))   
13. ADD TARGET package0.event_file(  
14.     SET filename=N'D:\Program Files\Microsoft SQL Server\deadlock', --存储路径及文件名前缀  
15.     max_file_size=(10),     --最大文件大小，单位MB  
16.     max_rollover_files=(4)) --启用文件滚动存储的最大文件数  
17. GO  
18.   
19. ALTER EVENT SESSION [deadlock] ON SERVER STATE=START  
20. GO  
21.   
22. --查询跟踪  
23. SELECT object_name as event,convert(xml, event_data) as xml_data  
24. FROM master.sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\deadlock*', NULL, NULL, NULL)  

 

方法五：系统扩展事件会话system_health自动记录

 

[sql]  view plain  copy

 

1. --扩展事件会话的信息  
2. select * from sys.dm_xe_sessions where name = 'system_health'  
3.   
4.   
5. SELECT   
6.     xed.value('@timestamp','datetime')as Creation_Date,    
7.     xed.query('.')AS Extend_Event    
8. FROM   
9. (    
10.     SELECT CAST([target_data] AS XML)AS Target_Data    
11.     FROM sys.dm_xe_session_targets AS xt    
12.     INNER JOIN sys.dm_xe_sessions AS xs    
13.     ON xs.address= xt.event_session_address    
14.     WHERE xs.name=N'system_health'    
15.     AND xt.target_name=N'ring_buffer'  
16. ) AS XML_Data    
17. CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')AS XEventData(xed)    
18. ORDER BY Creation_Date DESC  

 

 

 

方法六：Service Broker Event Notifications

 

[sql]  view plain  copy

 

1. USE msdb;  
2.   
3. --  队列用来保存数据  
4. --  DROP QUEUE DeadlockQueue   
5. CREATE QUEUE DeadlockQueue WITH STATUS=ON  
6. GO  
7.   
8. --  将消息传递到数据库中的正确队列  
9. --  DROP SERVICE DeadlockService  
10. CREATE SERVICE DeadlockService  
11. ON QUEUE DeadlockQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])  
12. GO  
13.   
14. --  创建可向服务发送有关数据库或服务器事件的信息的对象  
15. --  DROP EVENT NOTIFICATION CaptureDeadlocks ON SERVER  
16. CREATE EVENT NOTIFICATION CaptureDeadlocks  
17. ON SERVER  
18. WITH FAN_IN  
19. FOR DEADLOCK_GRAPH  
20. TO SERVICE 'DeadlockService','current database' ;  
21. GO  
22.   
23. --[此时发生死锁]  
24.   
25. --  查询XML格式的死锁信息  
26. SELECT CAST(message_body AS XML) AS message_body FROM DeadlockQueue  

 

转载于:https://www.cnblogs.com/qi123/p/8973408.html