SqlServer 总体分析死锁历程

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.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
,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 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))))
	,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))) KeyHash
from 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.resourcelistTemp
SELECT * FROM dbo.resourcelistTemp

--	DROP TABLE dbo.ResourceList
SELECT 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 mode
INTO dbo.ResourceList 
FROM dbo.resourcelistTemp 

SELECT * FROM ResourceList

分析：

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

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