sql 的阻塞查询（2008版本的）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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 LEFT JOIN ( SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES 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 --查找阻塞源头v3.0 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 LEFT JOIN SYS.DM_EXEC_REQUESTS SR ON SP.spid=SR.session_id LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC 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 WHERE BLOCKED<>0 ) AND SP.BLOCKED=0