| hoctinhoc |
28-01-2016 11:12 AM |
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
Trích dẫn:
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
Trích dẫn:
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
Trích dẫn:
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
:battay: |
