T-SQL Performance DeadLock 偵測+語法

自己參考各方文件寫的偵測
可以抓造成死結的源頭+語法

IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid
IN (SELECT blocked FROM master.sys.sysprocesses))
BEGIN
DECLARE @SPID binary(20),@Handle binary(20), @retVal varchar(8000)
SELECT @SPID =SPID FROM sysprocesses WHERE blocked = 0 AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
SELECT @Handle = sql_handle
FROM master..sysprocesses
WHERE SPID = @SPID
SELECT @retVal = convert(varchar(8000), [text])
FROM ::fn_get_sql(@Handle)

SELECT SPID,status 'Status','UserPcName'=SUBSTRING(hostname,1,12),'LogInID'=SUBSTRING(SUSER_SNAME(sid),1,30),'IsLock'=CONVERT(char(3),blocked),
'DBName' = SUBSTRING(DB_NAME(dbid),1,20),cmd 'cmd',waittype 'waittype', Program = program_name, LastCommand = @retVal
FROM sysprocesses
WHERE blocked = 0
AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
END
ELSE
SELECT 'Processes No Lock'