时间戳

首页 > 解决方案 > SQL CPU 脚本 - 计算连续出现的值

sql-server - SQL CPU 脚本 - 计算连续出现的值

问题描述

所以我正在研究一个 SQL CPU 利用率脚本，该脚本获取最后一个（例如，10 分钟的 CPU 使用率），用于 sys.dm_os_ring_buffers 中可用的 SQL 实例 - 非常标准的脚本。

但是，我想要做的是获取此信息，但计算样本中的连续出现次数（即 10 分钟），所以如果 10 分钟（10 个连续记录，其中值 > 90%）做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;

谢谢

标签： sql-servercpu-usage

解决方案

听起来您想要一种间隙和岛屿方法。这是我想出的：

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);

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

推荐阅读