forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbo.PerformanceGraph.sql
256 lines (242 loc) · 11.4 KB
/
dbo.PerformanceGraph.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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
IF OBJECT_ID('dbo.PerformanceGraph', 'P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.PerformanceGraph AS BEGIN SELECT 1 END');
END;
GO
ALTER PROCEDURE PerformanceGraph
@ShowCPUAsLines BIT = 1,
@ShowDeadlock BIT = 1,
@DeadlockBlobRadius TINYINT = 1,
@OutputDeadlockTable BIT = 1,
@ShowEndBar BIT = 1,
@EndBarWidth TINYINT = 1
AS
/*
https://thelonedba.wordpress.com/2019/09/27/wrapping-up-cpu-load-graphs-in-a-stored-procedure/
EXEC dbo.PerformanceGraph;
*/
BEGIN
SET NOCOUNT ON;
DECLARE @ts_now BIGINT =
(
SELECT TOP (1)
cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
ORDER BY cpu_ticks DESC
);
DECLARE @CPUSQLOut AS VARCHAR(8000),
@CPUIdleOut AS VARCHAR(8000),
@CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockPoints AS VARCHAR(8000),
@DeadlockBlobs AS VARCHAR(8000),
@DeadlockRecords AS INT;
DECLARE @OutputGeometry TABLE (ShapeInfo GEOMETRY, Caption VARCHAR(20));
WITH
CPULoad AS
(
SELECT TOP (256)
y.SQLProcessUtilization AS [SQL Server Process CPU Utilization],
y.SystemIdle AS [System Idle Process],
100 - (y.SystemIdle + y.SQLProcessUtilization) AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
FROM
(
SELECT x.record.value('(./Record/@id)[1]', 'int') AS record_id,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
x.timestamp
FROM
(
SELECT timestamp,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY y.timestamp DESC
)
SELECT @CPUSQLOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
+ ' ' + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUIdleOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
+ ' ' + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUOtherOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time])
- COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPURecords = (SELECT COUNT(*) FROM CPULoad);
IF @ShowCPUAsLines = 1
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
(geometry::Parse('LINESTRING(' + @CPUSQLOut + ')'), 'CPU-SQL'),
(geometry::Parse('LINESTRING(' + @CPUOtherOut + ')'), 'CPU-Other'),
(geometry::Parse('LINESTRING(' + @CPUIdleOut + ')'), 'CPU-Idle');
END;
ELSE
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUSQLOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-SQL'),
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUOtherOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-Other'),
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUIdleOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-Idle');
END;
DECLARE @XMLDeadlocks TABLE ([XML] XML, UTCTime DATETIME, LocalTime DATETIME, MinutesAgo INT);
IF @ShowDeadlock = 1
BEGIN;
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
)
INSERT INTO @XMLDeadlocks ([XML], UTCTime, LocalTime, MinutesAgo)
SELECT xmldates.deadlock_xml,
xmldates.deadlock_UTCtime,
xmldates.deadlock_LocalTime,
DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
FROM xmldates;
SELECT @DeadlockPoints = STUFF(
(
SELECT DISTINCT
', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
FROM @XMLDeadlocks xml
WHERE xml.MinutesAgo <= @CPURecords
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockBlobs
= STUFF(
(
-- SQL Prompt formatting off
SELECT DISTINCT ', CURVEPOLYGON(CIRCULARSTRING('
+ CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
+ CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
+ CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
+ '))'
-- SQL Prompt formatting on
FROM @XMLDeadlocks xml
WHERE xml.MinutesAgo <= @CPURecords
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockRecords =
(
SELECT COUNT(*) FROM @XMLDeadlocks xml WHERE xml.MinutesAgo <= @CPURecords
);
IF @DeadlockBlobRadius = 0
BEGIN -- show points
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('MULTIPOINT(' + @DeadlockPoints + ')'), -- ShapeInfo - geometry
'Deadlocks' -- Caption - varchar(20)
);
END;
ELSE
BEGIN -- show blobs
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('GEOMETRYCOLLECTION(' + @DeadlockBlobs + ')'), -- ShapeInfo - geometry
'Deadlocks' -- Caption - varchar(20)
);
END;
IF @OutputDeadlockTable = 1
BEGIN
SELECT UTCTime, LocalTime, MinutesAgo, XML FROM @XMLDeadlocks xd ORDER BY xd.MinutesAgo;
END;
END;
--End Bar
IF @ShowEndBar = 1
BEGIN
IF @EndBarWidth = 0
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('LINESTRING(0 0,0 100)'), -- ShapeInfo - geometry
'Now' -- Caption - varchar(100)
);
END;
ELSE
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('POLYGON((0 0,0 100, ' + CAST(@EndBarWidth AS CHAR(3)) + ' 100, '
+ CAST(@EndBarWidth AS CHAR(3)) + ' 0, 0 0))'
), -- ShapeInfo - geometry
'Now' -- Caption - varchar(100)
);
END;
END;
-- Final Output
SELECT ShapeInfo, Caption FROM @OutputGeometry;
END;
GO