Giám sát và truy tìm deadlock trong SQL 2014, how to monitor deadlock trong SQL 2014

Giám sát và truy tìm deadlock trong SQL 2014, how to monitor deadlock trong SQL 2014

1. Thông kế số deadlock tính từ lúc lần restart

SELECT cntr_value AS NumOfDeadLocks FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'

2. List và tìm các deadlock

WITH SystemHealth
AS (
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'
AND st.target_name = 'ring_buffer')

SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

3. Tìm thông tin về 1 Deadlock

SELECT OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = 289180401860608;

Các link tham khảo:

https://www.mssqltips.com/sqlservert...rver-deadlock/

http://blogs.technet.com/b/mspfe/arc...00_server.aspx