SQL Server Profiler死锁跟踪简例

--打开跟踪
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold',5
GO 
RECONFIGURE
GO
EXEC sp_configure

--授予权限
use master 
grant alter trace to logName

打开SQL Server Profiler

保存文件

选择blocked-process-report事件

--在数据库中读取文件
;WITH blockedReport AS(
	SELECT ROW_NUMBER()OVER(PARTITION BY TransactionID ORDER BY TransactionID,Duration) AS orderID
	,DatabaseID,ObjectID,TransactionID,EventClass,ServerName,LoginName,SPID,Duration,StartTime,EndTime
	,convert(XML,TextData) as XMLTextData
	FROM fn_trace_gettable('E:\TempFile\DB_20140719.trc', default)
)
SELECT orderID,DatabaseID,ObjectID,TransactionID,EventClass,ServerName,LoginName,SPID,Duration,StartTime,EndTime
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@kpid)') AS VARCHAR(max)),' ',',') as blocked_kpid
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@spid)') AS VARCHAR(max)),' ',',') as blocking_spid
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@ownerId)') AS VARCHAR(max)),' ',',') as blocked_ownerId
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@waittime)') AS VARCHAR(max)),' ',',') as blocked_waittime
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@hostname)') AS VARCHAR(max)),' ',',') as blocking_hostname
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@loginname)') AS VARCHAR(max)),' ',',') as blocking_loginname
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@transactionname)') AS VARCHAR(max)),' ',',') as transactionname
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@suspended)') AS VARCHAR(max)),' ',',') as blocked_suspended
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@spid)') AS VARCHAR(max)),' ',',') as blocked_spid
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@trancount)') AS VARCHAR(max)),' ',',') as blocked_trancount
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@trancount)') AS VARCHAR(max)),' ',',') as blocking_trancount
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@status)') AS VARCHAR(max)),' ',',') as blocking_status
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@lastbatchstarted)') AS VARCHAR(max)),' ',',') as blocked_started
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@lastbatchcompleted)') AS VARCHAR(max)),' ',',') as blocked_completed
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@lastbatchstarted)') AS VARCHAR(max)),' ',',') as blocking_started
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@lastbatchcompleted)') AS VARCHAR(max)),' ',',') as blocking_completed
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/@clientapp)') AS VARCHAR(max)),' ',',') as blocked_clientapp
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocking-process/process/@clientapp)') AS VARCHAR(max)),' ',',') as blocking_clientapp
,REPLACE(CAST(XMLTextData.query('data(//blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)') AS VARCHAR(max)),' ',',') as blocked_sqlhandle
,XMLTextData.value('(//blocked-process-report/blocked-process/process/inputbuf)[1]','VARCHAR(max)')  as blocked_SQL
,XMLTextData.value('(//blocked-process-report/blocking-process/process/inputbuf)[1]','VARCHAR(max)')  as blocking_SQL
,XMLTextData
FROM blockedReport WHERE orderID = 1

分析看看结果吧！！~