SqlServer 总体分析死锁进程

--首先，开启死锁跟踪一段时间
DBCC TRACEON(1222,-1)
DBCC TRACESTATUS

现在不分析死锁，只分析参与死锁的所有进程信息和死锁的对象信息。

--搜索当前日志中参与死锁的进程信息（第一个参数代表日志，现在查看2个日志）
exec xp_readerrorlog 0,1,'process id=process',NULL,'2015-03-01','2015-03-12','ASC' 
exec xp_readerrorlog 1,'ASC'

将【text】列拷贝到txt文件中。

使用数据库导入导出工具将txt数据导入到表,只1列数据
(不要以空格分列,因为有很多是不对齐的,所以全部作为1列)

--导入后：
--将有用的信息提取成各列，插入到新表中
--	DROP TABLE dbo.DeadlockProc
SELECT 
 LTRIM(RTRIM(substring(col,CHARINDEX('currentdb',col)+LEN('currentdb='),CHARINDEX('lockTimeout',col)-CHARINDEX('currentdb',col)-LEN('currentdb=')))) AS currentdb,LTRIM(RTRIM(substring(col,CHARINDEX('waitresource',col)+LEN('waitresource='),CHARINDEX('waittime',col)-CHARINDEX('waitresource',col)-LEN('waitresource=')))) AS waitresource,col)+LEN('waittime='),CHARINDEX('ownerId',col)-CHARINDEX('waittime',col)-LEN('waittime=')))) AS waittime,col)+LEN('ownerId='),CHARINDEX('transactionname',col)-CHARINDEX('ownerId',col)-LEN('ownerId=')))) AS ownerId,CHARINDEX('lockMode',col)+LEN('lockMode='),CHARINDEX('schedulerid',col)-CHARINDEX('lockMode',col)-LEN('lockMode=')))) AS lockMode,CHARINDEX('clientapp',col)+LEN('clientapp='),CHARINDEX('hostname',col)-CHARINDEX('clientapp',col)-LEN('clientapp=')))) AS clientapp,col)+LEN('hostname='),CHARINDEX('hostpid',col)-CHARINDEX('hostname',col)-LEN('hostname=')))) AS hostname,CHARINDEX('loginname',col)+LEN('loginname='),CHARINDEX('isolationlevel',col)-CHARINDEX('loginname',col)-LEN('loginname=')))) AS loginname,CHARINDEX('trancount',col)+LEN('trancount='),CHARINDEX('lastbatchstarted',col)-CHARINDEX('trancount',col)-LEN('trancount=')))) AS trancount,col)+LEN('lastbatchstarted='),CHARINDEX('lastbatchcompleted',col)-CHARINDEX('lastbatchstarted',col)-LEN('lastbatchstarted=')))) AS lastbatchstarted,col)+LEN('lastbatchcompleted='),col)-CHARINDEX('lastbatchcompleted',col)-LEN('lastbatchcompleted=')))) AS lastbatchcompleted
INTO dbo.DeadlockProc
FROM dbo.LOCK 

select top 10 * from DeadlockProc

部分分析：

--参与死锁的客户进程数
SELECT TOP 30 loginname,COUNT(*) CNT FROM DeadlockProc GROUP BY loginname ORDER BY CNT DESC

--哪台主机连接参与最多
SELECT TOP 30 hostname,COUNT(*) CNT FROM DeadlockProc GROUP BY hostname ORDER BY CNT DESC

--请求哪些锁模式导致死锁
SELECT TOP 30 lockMode,COUNT(*) CNT FROM DeadlockProc GROUP BY lockMode ORDER BY CNT DESC

----按小时查看死锁主要发生在什么时段
SELECT LEFT(lastbatchstarted,13) hours,COUNT(*) CNT FROM DeadlockProc GROUP BY LEFT(lastbatchstarted,13) ORDER BY hours


--等待的锁资源数
;WITH TAB AS(
select 
 LTRIM(RTRIM(LEFT(waitresource,CHARINDEX(':',waitresource)-1))) AS lockResource,LTRIM(RTRIM(LEFT(LTRIM(RTRIM(RIGHT(waitresource,LEN(waitresource)-CHARINDEX(':',waitresource)))),LTRIM(RTRIM(RIGHT(waitresource,waitresource)))))-1))) AS Database_id,REPLACE(LEFT(REVERSE(LEFT(REVERSE(waitresource),REVERSE(waitresource))-1)),CHARINDEX('(',REVERSE(LEFT(REVERSE(waitresource),REVERSE(waitresource))-1)))),'(','') AS hobt_id,LTRIM(RTRIM(SUBSTRING(waitresource,waitresource),CHARINDEX(')',waitresource)-CHARINDEX('(',waitresource)+1))) KeyHash
from DeadlockProc
) select lockResource,COUNT(*) cnt from tab group by lockResource

---------------------------------------------------------------------------
---------------------------------------------------------------------------

--也可以按相同方法把锁资源也导入到数据库中
exec xp_readerrorlog 0,'indexname','hobtid','ASC'  
exec xp_readerrorlog 1,'ASC'  

--	DROP TABLE dbo.resourcelistTemp
SELECT * FROM dbo.resourcelistTemp

--	DROP TABLE dbo.ResourceList
SELECT LTRIM(RTRIM(LEFT(col,CHARINDEX('hobtid',col)-1))) AS lockResource,CHARINDEX('dbid',col)+LEN('dbid='),CHARINDEX('objectname',col)-CHARINDEX('dbid',col)-LEN('dbid=')))) AS dbid,col)+LEN('objectname='),CHARINDEX('indexname',col)-CHARINDEX('objectname',col)-LEN('objectname=')))) AS objectname,col)+LEN('indexname='),CHARINDEX('id=lock',col)-CHARINDEX('indexname',col)-LEN('indexname=')))) AS indexname,CHARINDEX('mode',col)+LEN('mode='),CHARINDEX('associatedObjectId',col)-CHARINDEX('mode',col)-LEN('mode=')))) AS mode
INTO dbo.ResourceList 
FROM dbo.resourcelistTemp 

SELECT * FROM ResourceList

分析：

--参与死锁的这些资源中,哪些表索引请求什么模式导致死锁
SELECT objectname,indexname,lockResource,mode,COUNT(*) CNT
FROM ResourceList 
GROUP BY objectname,mode
ORDER BY CNT DESC

--锁粒度及请求模式统计
SELECT lockResource,COUNT(*) CNT
FROM ResourceList 
GROUP BY lockResource,mode
ORDER BY CNT DESC

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!