-
Notifications
You must be signed in to change notification settings - Fork 7
Expand file tree
/
Copy pathProcedure CPU Usage.sql
More file actions
76 lines (74 loc) · 3.19 KB
/
Copy pathProcedure CPU Usage.sql
File metadata and controls
76 lines (74 loc) · 3.19 KB
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
USE TEMPDB
GO
CREATE OR ALTER PROCEDURE [dbo].[usp_CPUUsage]
(@LogToTable BIT = 0, @retentiondays INT = 30)
AS
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
IF @LogToTable = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCPUUsage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblCPUUsage](
[SQLCPUUsage] [int] NULL,
[IdleProcess] [int] NULL,
[RestCPUUsage] [int] NULL,
[RunTime] [datetime] NULL
) ON [PRIMARY]
END
END
DECLARE @ts_now BIGINT=
(
SELECT cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
);
IF @LogToTable = 1
BEGIN
INSERT INTO tblCPUUsage
SELECT SQLProcessUtilization AS SQLCPUUsage,
SystemIdle AS IdleProcess,
100 - SystemIdle - SQLProcessUtilization AS RestCPUUsage,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS RunTime
FROM
(
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[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
WHERE DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) > ISNULL(
(
SELECT MAX(RunTime)
FROM tblCPUUsage
), DATEADD(MI, -256, GETDATE()));
DELETE tblCPUUsage
WHERE RunTime < DATEADD(DD, -@retentiondays, GETDATE());
END
ELSE
BEGIN
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
AS [SQLProcessUtilization], [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 record_id DESC OPTION (RECOMPILE);
END
GO
EXEC [usp_CPUUsage]
GO