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,1,'process id=process',NULL,'2015-03-01','2015-03-12','ASC'

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

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

--导入后：--将有用的信息提取成各列，插入到新表中--DROP TABLE dbo.DeadlockProcSELECT  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,LTRIM(RTRIM(substring(col,CHARINDEX('waittime',col)+LEN('waittime='),CHARINDEX('ownerId',col)-CHARINDEX('waittime',col)-LEN('waittime=')))) AS waittime,LTRIM(RTRIM(substring(col,CHARINDEX('ownerId',col)+LEN('ownerId='),CHARINDEX('transactionname',col)-CHARINDEX('ownerId',col)-LEN('ownerId=')))) AS ownerId,LTRIM(RTRIM(substring(col,CHARINDEX('lockMode',col)+LEN('lockMode='),CHARINDEX('schedulerid',col)-CHARINDEX('lockMode',col)-LEN('lockMode=')))) AS lockMode,LTRIM(RTRIM(substring(col,CHARINDEX('clientapp',col)+LEN('clientapp='),CHARINDEX('hostname',col)-CHARINDEX('clientapp',col)-LEN('clientapp=')))) AS clientapp,LTRIM(RTRIM(substring(col,CHARINDEX('hostname',col)+LEN('hostname='),CHARINDEX('hostpid',col)-CHARINDEX('hostname',col)-LEN('hostname=')))) AS hostname,LTRIM(RTRIM(substring(col,CHARINDEX('loginname',col)+LEN('loginname='),CHARINDEX('isolationlevel',col)-CHARINDEX('loginname',col)-LEN('loginname=')))) AS loginname,LTRIM(RTRIM(substring(col,CHARINDEX('trancount',col)+LEN('trancount='),CHARINDEX('lastbatchstarted',col)-CHARINDEX('trancount',col)-LEN('trancount=')))) AS trancount,LTRIM(RTRIM(substring(col,CHARINDEX('lastbatchstarted',col)+LEN('lastbatchstarted='),CHARINDEX('lastbatchcompleted',col)-CHARINDEX('lastbatchstarted',col)-LEN('lastbatchstarted=')))) AS lastbatchstarted,LTRIM(RTRIM(substring(col,CHARINDEX('lastbatchcompleted',col)+LEN('lastbatchcompleted='),CHARINDEX('clientapp',col)-CHARINDEX('lastbatchcompleted',col)-LEN('lastbatchcompleted=')))) AS lastbatchcompletedINTO dbo.DeadlockProcFROM 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)))),CHARINDEX(':',LTRIM(RTRIM(RIGHT(waitresource,LEN(waitresource)-CHARINDEX(':',waitresource)))))-1))) AS Database_id,REPLACE(LEFT(REVERSE(LEFT(REVERSE(waitresource),CHARINDEX(':',REVERSE(waitresource))-1)),CHARINDEX('(',REVERSE(LEFT(REVERSE(waitresource),CHARINDEX(':',REVERSE(waitresource))-1)))),'(','') AS hobt_id,LTRIM(RTRIM(SUBSTRING(waitresource,CHARINDEX('(',waitresource),CHARINDEX(')',waitresource)-CHARINDEX('(',waitresource)+1))) KeyHashfrom DeadlockProc) select lockResource,COUNT(*) cnt from tab group by lockResource

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

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

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

分析：

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

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