原文:

SELECT [Individual Query] = SUBSTRING(qt.TEXT, er.statement_start_offset / 2, (			CASE 				WHEN er.statement_end_offset = - 1					THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2				ELSE er.statement_end_offset				END - er.statement_start_offset			) / 2),	[Parent Query] = qt.TEXT,	[Spid] = session_Id,	ecid,	[Database] = DB_NAME(sp.dbid),	[User] = nt_username,	[Status] = er.STATUS,	[Wait] = wait_type,	Program = program_name,	Hostname,	nt_domain,	start_timeFROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qtWHERE session_Id > 50 /* Ignore system spids.*/	AND session_Id NOT IN (@@SPID) --每秒死锁数量SELECT *FROM sys.dm_os_performance_countersWHERE counter_name LIKE 'Number of Deadlocksc%'; --查询当前阻塞WITH CTE_SID(BSID, SID, sql_handle) AS (		SELECT blocking_session_id,			session_id,			sql_handle		FROM sys.dm_exec_requests		WHERE blocking_session_id <> 0		UNION ALL		SELECT A.blocking_session_id,			A.session_id,			A.sql_handle		FROM sys.dm_exec_requests A		JOIN CTE_SID B ON A.SESSION_ID = B.BSID		)   SELECT C.BSID,	C.SID,	S.login_name,	S.host_name,	S.STATUS,	S.cpu_time,	S.memory_usage,	S.last_request_start_time,	S.last_request_end_time,	S.logical_reads,	S.row_count,	q.TEXTFROM CTE_SID CJOIN sys.dm_exec_sessions S ON C.sid = s.session_idCROSS APPLY sys.dm_exec_sql_text(C.sql_handle) QORDER BY sid --检查表的更新排他锁DECLARE @t_lock AS TABLE (	[spid] [smallint] NULL,	[dbid] [smallint] NOT NULL,	[ObjId] [int] NOT NULL,	[IndId] [smallint] NOT NULL,	[Type] [nvarchar](4) NULL,	[Resource] [nvarchar](32) NULL,	[Mode] [nvarchar](8) NULL,	[Status] [nvarchar](5) NULL	) INSERT INTO @t_lockEXEC sp_lock SELECT *,	[Database] = DB_NAME([dbid]),	[Object] = OBJECT_NAME([ObjId], [dbid])FROM @t_lockWHERE [spid] > 50 /* Ignore system spids.*/	AND [spid] NOT IN (@@SPID)	AND [Type] = 'TAB'	AND [Mode] IN (		'U',		'IU',		'SIU',		'UIX',		'BU',		'RangeS_U',		'RangeI_U',		'X',		'IX',		'SIX',		'UIX',		'RangeI_X',		'RangeX_S',		'RangeX_U',		'RangeX_X'		)