-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathADMIN_Blocking_Locking_Hierarchical.sql
150 lines (142 loc) · 6.53 KB
/
ADMIN_Blocking_Locking_Hierarchical.sql
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
/*
-- =============================================================================
ADMIN_Blocking_Locking_Hierarchical.sql
This script displays a hierarchical tree
of locked/blocked processes on a SQL Server
instance.
Copyright (C) 2021 hot2use / JohnKNess
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <https://www.gnu.org/licenses/>.
-- =============================================================================
*/
/*
-- =============================================================================
Author......: hot2use / JohnKNess
Date........: 01.06.2021
Version.....: 0.1
Server......: SQL Server 2012+ (first created for)
Database....: master
Owner.......: -
Table.......: -
Type........: Script
Name........: ADMIN_Blocking_Locking_Hierarchical.sql
Description.: This script displays a hierarchical tree
............ of locked/blocked processes on a SQL Server
............ instance.
............
History.....: 01-Jun-2021 0.1 JN First created
............
............
Editors.....: UEStudio (IDM Computer Solutions, Inc.)
............ SQLAssistant (SoftTree Technologies, Inc.)
............ SQL Server Management Studio 18.9.1 (Microsoft, Inc.)
-- =============================================================================
*/
SET LANGUAGE 'us_english';
GO
-- USE MASTER -- commanted out to allow for use on Azure SQL
-- GO
-- Create a Temp Table with Base Data
SELECT sdes.session_id AS SESSION_ID, -- SESSION_ID
sder.blocking_session_id AS BLOCKING_SESSION_ID, -- BLOCKED_SESSION_ID
sder.wait_resource AS WAIT_RESSOURCE, -- WAIT_RESOURCE
dowt.wait_duration_ms AS WAIT_DURATION_MS, -- WAIT_DURATION_MS
dowt.wait_type AS WAIT_TYPE, -- WAIT_TYPE
dest.text AS SQL_TEXT, -- SQL_TEXT
deqp.query_plan AS QUERY_PLAN -- PLAN_CACHE
INTO #SESSION_BASE_DATA
FROM sys.dm_exec_sessions AS sdes
JOIN sys.dm_exec_connections AS sdec
ON sdes.session_id = sdec.session_id
LEFT JOIN sys.dm_exec_requests AS sder
ON sder.session_id = sdec.session_id
AND sder.connection_id = sdec.connection_id
LEFT JOIN sys.dm_os_waiting_tasks AS dowt
ON sdes.session_id = dowt.session_id
OUTER APPLY sys.dm_exec_sql_text(sder.sql_handle) AS dest
OUTER APPLY sys.dm_exec_query_plan(sder.plan_handle) AS deqp;
GO
/*
SELECT * FROM #SESSION_BASE_DATA
go
*/
WITH DirectSessions(
HIERARCHY,
SESSION_ID,
BLOCKING_SESSION_ID,
WAIT_RESSOURCE,
WAIT_DURATION_MS,
WAIT_TYPE,
BLOCKING_LEVEL,
SQL_TEXT,
QUERY_PLAN,
SORTPATH
)
AS (
-- Base Elements(s) of CTE
SELECT CONVERT(nchar(50), N'| ' + CAST(SESSION_ID AS nchar(4)) + N'')
AS HIERARCHY, -- HIERARCHY (Base Tree Design Element)
SESSION_ID, -- SESSION_ID
BLOCKING_SESSION_ID, -- BLOCKED_SESSION_ID
WAIT_RESSOURCE, -- WAIT_RESOURCE
WAIT_DURATION_MS, -- WAIT_DURATION_MS
WAIT_TYPE, -- WAIT_TYPE
1 AS BLOCKING_LEVEL, -- BLOCKING_LEVEL
SQL_TEXT, -- SQL_TEXT
QUERY_PLAN, -- PLAN_CACHE
CAST(SESSION_ID AS nvarchar(200)) -- SORTPATH
FROM #SESSION_BASE_DATA
WHERE 1 = 1
AND (
BLOCKING_SESSION_ID = 0
OR BLOCKING_SESSION_ID IS NULL
) -- Base element(s) with a blocking process id = 0 or NULL
UNION ALL
-- Next Element(s) of CTE
SELECT CONVERT(
nchar(50),
REPLICATE(N'| ', BLOCKING_LEVEL - 1) + N'|----¬ ' + CAST(sbd.SESSION_ID AS nchar(4))
) AS HIERARCHY, -- HIERARCHY (Extended Tree Design Elements)
sbd.SESSION_ID, -- SESSION_ID
sbd.BLOCKING_SESSION_ID, -- BLOCKED_SESSION_ID
sbd.WAIT_RESSOURCE, -- WAIT_RESOURCE
sbd.WAIT_DURATION_MS, -- WAIT_DURATION_MS
sbd.WAIT_TYPE, -- WAIT_TYPE
BLOCKING_LEVEL + 1, -- BLOCKING_LEVEL + 1
sbd.SQL_TEXT, -- SQL_TEXT
sbd.QUERY_PLAN, -- PLAN_CACHE
CAST(
CAST(ds.SORTPATH AS NVARCHAR(200)) + N' ' + CAST(sbd.SESSION_ID AS nvarchar(4)) AS nvarchar(200)
) -- SORTPATH = base SESSION_ID + CURRENT SESSION_ID from iteration in CTE
FROM #SESSION_BASE_DATA AS sbd
JOIN DirectSessions AS ds
ON ds.SESSION_ID = sbd.BLOCKING_SESSION_ID
WHERE 1 = 1
AND (
sbd.BLOCKING_SESSION_ID != 0
OR sbd.BLOCKING_SESSION_ID IS NOT NULL
) -- All other elements with a blocking session_id
)
SELECT HIERARCHY,
SESSION_ID,
BLOCKING_SESSION_ID,
WAIT_RESSOURCE,
WAIT_DURATION_MS,
WAIT_TYPE,
BLOCKING_LEVEL,
SQL_TEXT,
QUERY_PLAN
FROM DirectSessions
ORDER BY
SORTPATH;
GO
DROP TABLE #SESSION_BASE_DATA;
GO