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_time

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE session_Id > 50 /* Ignore system spids.*/

	AND session_Id NOT IN (@@SPID)

--每秒死锁数量

SELECT *

FROM sys.dm_os_performance_counters

WHERE 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.TEXT

FROM CTE_SID C

JOIN sys.dm_exec_sessions S ON C.sid = s.session_id

CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q

ORDER 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_lock

EXEC sp_lock

SELECT *,

	[Database] = DB_NAME([dbid]),

	[Object] = OBJECT_NAME([ObjId], [dbid])

FROM @t_lock

WHERE [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'

		)