DEADLOCK 확인

데드락 발생 시 확인 방법

참고 사이트: sql server - Help reading deadlock XML

1. Profiler 실행
2. Deadlock 확인 후 XML Report 확인
3. resource-list > keylock의 associatedObjectId로 테이블 확인 (하단 Deadlock Table Search)
4. process-list > process > inputbuf에서 SP, SQL 확인. SP, SQL이 나와있지 않은 경우 Object ID로 확인 (하단 Stored Procedure Search)
5. Table, SP, SQL을 확인하여 어느 곳에서 Deadlock이 발생하는지 체크

-- Deadlock Table Search
SELECT OBJECT_NAME(p.object_id) AS TableName
     , i.name AS IndexName 
  FROM sys.partitions AS p
 INNER JOIN sys.indexes AS i 
    ON p.object_id = i.object_id
   AND p.index_id = i.index_id 
 WHERE i.object_id = 'associatedObjectId'

-- Stored Procedure Search #1
SELECT DB_NAME(st.dbid) DBName 
     , OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName 
     , OBJECT_NAME(st.objectid,dbid) StoredProcedure
     , st.text
  FROM sys.dm_exec_cached_plans cp 
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
 where DB_NAME(st.dbid) is not null 
   and cp.objtype = 'proc' 
   AND st.objectid = 'objectID'
   
-- Stored Procedure Search #2
SELECT OBJECT_NAME('objectID')