-
Notifications
You must be signed in to change notification settings - Fork 27
/
6-Blocking Tree Script
44 lines (38 loc) · 1.73 KB
/
6-Blocking Tree Script
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
41
42
43
44
--select * from sys.sysprocesses where blocked <> 0
go
--select @@servername
--go
SET NOCOUNT ON;
SET CONCAT_NULL_YIELDS_NULL OFF
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH,waittype,lastwaittype)
AS
(
SELECT
SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,
R.waittype,
R.lastwaittype
FROM sys.sysprocesses R with (nolock)
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT SPID,BLOCKED,CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,R.waittype,R.lastwaittype FROM sys.sysprocesses R2 with (nolock)
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT
R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,
R.waittype,
R.lastwaittype
FROM sys.sysprocesses AS R with (nolock)
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS VARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE , waittype ,lastwaittype, GETDATE() as Time FROM BLOCKERS with (nolock) ORDER BY LEVEL ASC
go