找出SQL中的死锁进程

select a.resource_type, 
case a.resource_type
when 'RID' then '行锁（表没有聚集索引表）'
when 'KEY' then '行锁（表有聚集索引表）'
when 'Page' then '数据页锁'
when 'EXTEnt' then '连续8个页面集合锁'
when 'Hobt' then '堆或平衡树锁（表没有聚集索引）'
when 'OBJECT' then '表锁'
when 'application' then '应用程序锁'
else '其他' end as '其他锁',
case when a.resource_type ='OBJECT' then object_name(a.resource_associated_entity_id)
when a.resource_associated_entity_id is null or a.resource_associated_entity_id=0 then null
else object_name(p.[object_id]) end as '对象',
db_name(a.resource_database_id) as '数据库',
a.request_mode '请求锁模式',
case a.request_mode 
when 'S' then '共享'
when 'X' then '排他'
when 'U' then '更新'
when 'IS' then '意向共享'
when 'IU' then '意向更新'
when 'IX' then '意向排他'
when 'SIX' then '意向排他共享'
when 'SIU' then '意向更新共享'
when 'UIX' then '更新意向排他'
when 'shc-s' then '架构稳定'
when 'Sch-m' then '架构修改'
when 'BU' then '大容量更新'
else '其他' end as '锁模式',
a.request_type as '事物上锁对象',
case a.request_status 
when 'grant' then '锁已赋予'
when 'convert' then '锁正在转换'
else '等待中' end as '等待赋予锁', 
a.request_owner_type as '请求所有者的实体类型',
b.session_id as '被封锁会话',
b.blocking_session_id as '源锁会话',
b.wait_type as '等待类型',
c.[host_name] as '主机名称',
c.[login_name] as '登录名',
c.[status] as '状态'

from sys.dm_tran_locks a 
join  sys.dm_os_waiting_tasks b on b.resource_address = a.lock_owner_address
join sys.dm_exec_sessions c on c.[session_id]=b.[session_id]
left join sys.partitions as p on a.resource_associated_entity_id=p.partition_id
--54指锁定资源的进程ID号
--找到那个进程，然后把进程号替换54，再执行DBCC，然后看看那条语句，通过这样可以锁定目标，优化就可以开展了
dbcc inputbuffer(54)