如何查询Microsoft SQL Server中ETL文件加载是否停滞？

需求分析 #

需要识别状态为RUNNING、进度超过30分钟未增长的文件，已知正常加载时进度会持续增长，但任务相关日期列（如启动时间）不会更新。

原查询问题说明 #

你提供的原始查询逻辑错误：将Progress（百分比数值）与时间戳直接比较，无法实现进度停滞的检测。

解决方案 #

情况1：表中存在进度最后更新时间列

如果你的ExecuteFileStatus表中有记录进度最后更新时间的列（比如LastProgressUpdate，注意不是任务启动时间），可以直接通过时间差判断：

DECLARE @CurrentTime DATETIME = CURRENT_TIMESTAMP;
-- 设置30分钟停滞阈值
DECLARE @StallThreshold DATETIME = DATEADD(MINUTE, -30, @CurrentTime);

SELECT *
FROM ExecuteFileStatus
WHERE 
    Runstate = 'RUNNING'
    AND Progress < 100 -- 排除已完成但状态未同步的文件
    AND LastProgressUpdate <= @StallThreshold;

情况2：表中仅保留当前状态（无进度历史）

如果表中只有当前进度和任务启动时间（日期列不更新），则需要先记录进度历史，再对比判断：

1. 创建进度日志表（仅需执行一次）：

CREATE TABLE FileProgressLog (
    FileName VARCHAR(255) NOT NULL,
    Progress INT NOT NULL,
    LogTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (FileName, LogTime)
);

2. 定期记录进度（比如每5分钟执行一次，可通过SQL Agent Job自动化）：

INSERT INTO FileProgressLog (FileName, Progress)
SELECT FileName, Progress
FROM ExecuteFileStatus
WHERE Runstate = 'RUNNING';

3. 查询停滞文件：

WITH LatestProgress AS (
    SELECT 
        FileName,
        Progress,
        LogTime,
        -- 获取该文件最近一次的进度和记录时间
        LAG(Progress) OVER (PARTITION BY FileName ORDER BY LogTime DESC) AS PreviousProgress,
        LAG(LogTime) OVER (PARTITION BY FileName ORDER BY LogTime DESC) AS PreviousLogTime
    FROM FileProgressLog
    -- 仅查询最近1小时的日志，缩小范围
    WHERE LogTime >= DATEADD(MINUTE, -60, CURRENT_TIMESTAMP)
)
SELECT DISTINCT
    e.FileName,
    e.Progress AS 当前进度,
    e.ABUpdateTime AS 任务启动时间,
    DATEDIFF(MINUTE, lp.PreviousLogTime, CURRENT_TIMESTAMP) AS 停滞时长(分钟)
FROM LatestProgress lp
JOIN ExecuteFileStatus e ON lp.FileName = e.FileName
WHERE 
    e.Runstate = 'RUNNING'
    AND e.Progress < 100
    -- 最近两次记录的进度无变化
    AND lp.Progress = lp.PreviousProgress
    -- 停滞时间超过30分钟
    AND DATEDIFF(MINUTE, lp.PreviousLogTime, CURRENT_TIMESTAMP) >= 30;

内容的提问来源于stack exchange，提问作者escsavar