SQL CPU脚本-计数值的连续出现次数

Question

因此，我正在为一个SQL实例编写一个SQL CPU利用率脚本，该脚本可以从sys.dm_os_ring_buffers(相当标准的脚本(中获得最后一个CPU使用率(例如，10分钟的CPU使用率(。

然而，我想做的是获取这些信息，但计算样本中连续出现的次数(即10分钟(，因此如果持续10分钟(值>90%的10个连续记录(，则执行X

这是我正在使用的代码：(为正确代码编辑(

DECLARE @ts BIGINT;
DECLARE @lastNmin TINYINT;
SET @lastNmin = 10;
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM 
sys.dm_os_sys_info); 
SELECT TOP(@lastNmin)
SQLProcessUtilization AS [SQLServer_CPU_Utilization], 
SystemIdle AS [System_Idle_Process], 
100 - SystemIdle - SQLProcessUtilization AS 
[Other_Process_CPU_Utilization], 
DATEADD(ms,-1 *(@ts - [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             
WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'AND record 
LIKE'%%')AS x )AS y 
ORDER BY record_id DESC; 

感谢

Answer 1

听起来你想要一种缺口和孤岛的方法。以下是我的想法：

DROP TABLE IF EXISTS #tmp;
DECLARE @ts BIGINT;
DECLARE @lastNmin TINYINT;
SET @lastNmin = 10;
SELECT @ts =
(
SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info
);
SELECT TOP (@lastNmin)
SQLProcessUtilization AS [SQLServer_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
DATEADD(ms, -1 * (@ts - [timestamp]), GETDATE()) AS [Event_Time]
INTO #tmp
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
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%'
) AS x
) AS y
ORDER BY record_id DESC;
WITH cte AS (
SELECT *, CAST(CASE WHEN [System_Idle_Process] >= 95 THEN 1 ELSE 0 END AS BIT) as [HighCPU]
FROM #tmp
),
GapsAndIslands AS (
SELECT *, 
ROW_NUMBER() OVER (ORDER BY cte.Event_Time) AS rn1,
ROW_NUMBER() OVER (PARTITION BY cte.HighCPU ORDER BY cte.Event_Time) AS rn2
FROM cte
)
SELECT *, rn1 - rn2 AS GroupID
FROM GapsAndIslands
ORDER BY GapsAndIslands.Event_Time;

作为解释，我正在创建三个合成柱

1. 一个布尔值，表示您要跟踪的条件(注意-我使用的指标与您应该使用的指标不同，因为我的CPU使用率很低！(
2. 整个数据集的行号列
3. 跟踪度量的每个不同值的行号列

请注意，对于具有相同跟踪度量值的连续行，这两个行号列中的差异将是相同的，并且将在边界上发生变化。我在最终结果集中把它作为GroupID，您可以使用它来跟踪连续行的组。

如果您将最后一次选择替换为：

SELECT MIN(Event_Time), MAX(Event_Time)
FROM GapsAndIslands
WHERE [HighCPU] = 1
GROUP BY rn1 - rn2
ORDER BY MIN(Event_Time);

这将为您提供跟踪指标高于阈值的时间范围。