SQL Server查询性能测试偶现耗时异常及测量方案咨询

偶发3-4倍耗时尖刺的原因 #

你当前测试脚本的耗时波动和被测查询本身性能无关，是测试逻辑中的干扰项导致的：

• 缓存清理命令的耗时波动：你将CHECKPOINT、DBCC DROPCLEANBUFFERS、DBCC FREEPROCCACHE放在循环内紧邻计时起点的位置，三个命令本身执行耗时极不稳定：CHECKPOINT需要将内存中所有脏页刷入磁盘，上一轮查询产生的脏页量不固定，刷盘IO波动会直接带来延迟；两个DBCC命令执行时会触发内部元数据扫描、内存链表锁分配，偶尔碰到SQL OS调度器时间片切换就会出现耗时尖刺。
• 计时精度不足：GETDATE()的系统计时精度只有3.33毫秒，本身存在固有误差；同时你没有排除后台任务干扰：哪怕无用户并发，惰性写入器扫描、自动统计信息校验加载、事务日志预备扩展、资源监控线程轮询这些系统后台任务，一旦刚好落在计时区间内，就会拉高总耗时。
• 额外开销计入测量值：每轮循环直接输出单条耗时结果，SSMS渲染结果集、客户端接收数据的偶发延迟会被算入查询耗时。

优化后的原生T-SQL测试脚本 #

以下脚本排除了上述干扰项，支持输出每一轮的单独测量结果，不需要额外依赖工具：

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;

-- 创建临时表存储单轮测试结果
IF OBJECT_ID('tempdb..#TestResult') IS NOT NULL DROP TABLE #TestResult;
CREATE TABLE #TestResult (
    RoundID INT,
    ExecTimeMs BIGINT
);

DECLARE @i INT = 0;
DECLARE @itrs INT = 100;
DECLARE @t0 DATETIME2, @t1 DATETIME2;
DECLARE @res BIGINT;

-- 预执行一次查询，完成初始编译与元数据加载
SELECT @res = COUNT(*) FROM [Comments] AS [c] WHERE [c].[Score] > 100;

WHILE @i < @itrs
BEGIN
    -- 仅测试冷缓存场景时保留以下3行，测试热缓存（生产常见场景）可直接删除
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;
    -- 等待100ms，待缓存清理的后台IO完全完成后再开始计时
    WAITFOR DELAY '00:00:00.100';

    SET @t0 = SYSDATETIME();
    -- 执行被测查询，结果存入变量避免输出开销
    SELECT @res = COUNT(*) FROM [Comments] AS [c] WHERE [c].[Score] > 100;
    SET @t1 = SYSDATETIME();

    INSERT INTO #TestResult(RoundID, ExecTimeMs)
    VALUES (@i + 1, DATEDIFF(MILLISECOND, @t0, @t1));

    SET @i = @i + 1;
END

-- 输出所有轮次的单独耗时
SELECT RoundID, ExecTimeMs FROM #TestResult ORDER BY RoundID;
-- 可选输出统计参考值
SELECT 
    MIN(ExecTimeMs) AS MinMs,
    MAX(ExecTimeMs) AS MaxMs,
    AVG(CAST(ExecTimeMs AS FLOAT)) AS AvgMs,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ExecTimeMs) OVER() AS P95Ms
FROM #TestResult;

脚本优化点说明：

• 用精度达100纳秒级的SYSDATETIME()替代GETDATE()，减少计时误差
• 被测查询结果存入变量，避免结果集输出、客户端渲染的开销计入测量值
• DBCC命令加NO_INFOMSGS参数关闭消息输出，减少额外开销
• 清理缓存后增加短等待，避免缓存清理的后续后台操作干扰计时
• 所有测试结果存入临时表最后统一输出，避免每轮输出的开销干扰

无需手写代码的测量工具推荐 #

如果不想自定义T-SQL循环，可以选择以下方案，均支持查看单次执行的测量结果：

• 内置Query Store：开启对应数据库的查询存储功能后，会自动捕获所有查询的每次执行指标，包括执行时长、CPU消耗、IO消耗，直接在SSMS的查询存储面板即可筛选查看目标查询的所有执行记录。
• 扩展事件（Extended Events）：SQL Server自带的轻量级监控工具，可以创建针对语句完成事件的会话，过滤目标查询后即可自动记录每一次执行的准确耗时，对数据库本身性能影响低于3%，适合做精确性能测量。
• 轻量工具SQLQueryStress：输入待测试查询、设置迭代次数后即可自动运行，自动输出每轮执行耗时，同时支持自动计算P95、P99等分位性能值，使用门槛低。

内容的提问来源于stack exchange，提问作者Paweł Tomaszewski