SQL Server 定位阻塞源

 SQL Server 定位阻塞源

/*

---------------------------------------------------------------------------------

spm_Block

功能：查看阻塞和锁,阻塞源头

参数：无

---------------------------------------------------------------------------------

*/

CREATE PROCEDURE [dbo].[spm_Block]

as

--查找有关被阻塞的请求的信息(含用户)

SELECT s.loginame

,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)

,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id

,qs.wait_type ,qs.wait_time ,qs.wait_resource

,qs.transaction_id

FROM SYS.DM_EXEC_REQUESTS qs (nolock)

LEFT JOIN (

SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES (nolock) GROUP BY spid

) s ON qs.session_id=s.spid

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr

WHERE qs.status = N'suspended'

--and s.loginame<>''

ORDER BY qs.wait_time DESC



--查找阻塞源头 

SELECT SP.spid

,CASE WHEN ST1.text IS NULL THEN ST2.text

ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2,

(

CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2

ELSE SR.statement_end_offset

END - SR.statement_start_offset)/2

)

END AS [T-sql]

,SP.loginame

,DB_NAME(SP.dbid) AS [db_name]

,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*

FROM SYS.SYSPROCESSES SP (nolock)

LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP.spid=SR.session_id

LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock) ON SP.spid=SC.session_id

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1

WHERE SP.spid IN

(

SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED<>0

)

AND SP.BLOCKED=0





GO


exec spm_Block