主页 / dba / 问题 / 219137
Accepted
Joe Obbish
Asked: 2018-10-03 19:26:33 +0800 CST

为什么扫描比寻找这个谓词更快？

  • 772

我能够重现一个我认为是意外的查询性能问题。我正在寻找一个专注于内部的答案。

在我的机器上，以下查询执行聚集索引扫描，占用大约 6.8 秒的 CPU 时间：

SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

以下查询执行聚集索引查找（唯一的区别是删除FORCESCAN提示）但需要大约 18.2 秒的 CPU 时间：

SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);

查询计划非常相似。对于这两个查询，从聚集索引中读取了 120000001 行：

查询计划

我在 SQL Server 2017 CU 10 上。这是创建和填充two_col_key_test表的代码：

drop table if exists dbo.two_col_key_test;

CREATE TABLE dbo.two_col_key_test (
    ID1 NVARCHAR(50) NOT NULL,
    ID2 NVARCHAR(50) NOT NULL,
    FILLER NVARCHAR(50),
    PRIMARY KEY (ID1, ID2)
);

DROP TABLE IF EXISTS #t;

SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);


INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;

我希望得到一个不仅仅是调用堆栈报告的答案。例如，我可以看到，sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataX与快速查询相比，慢速查询需要更多的 CPU 周期：

观点

我不想停在那里，而是想了解那是什么以及为什么两个查询之间存在如此大的差异。

为什么这两个查询的 CPU 时间差异很大？

sql-server performance

1 个回答

  1. Best Answer

    为什么这两个查询的 CPU 时间差异很大？

    扫描计划为每一行评估以下推送的不可分割（残差）谓词：

    [two_col_key_test].[ID1]<>N'1' 
AND [two_col_key_test].[ID1]<>N'10' 
AND [two_col_key_test].[ID1]<>N'11' 
AND [two_col_key_test].[ID1]<>N'12' 
AND [two_col_key_test].[ID1]<>N'13' 
AND [two_col_key_test].[ID1]<>N'14' 
AND [two_col_key_test].[ID1]<>N'15' 
AND [two_col_key_test].[ID1]<>N'16' 
AND [two_col_key_test].[ID1]<>N'17' 
AND [two_col_key_test].[ID1]<>N'18' 
AND [two_col_key_test].[ID1]<>N'19' 
AND [two_col_key_test].[ID1]<>N'2' 
AND [two_col_key_test].[ID1]<>N'20' 
AND [two_col_key_test].[ID1]<>N'3' 
AND [two_col_key_test].[ID1]<>N'4' 
AND [two_col_key_test].[ID1]<>N'5' 
AND [two_col_key_test].[ID1]<>N'6' 
AND [two_col_key_test].[ID1]<>N'7' 
AND [two_col_key_test].[ID1]<>N'8' 
AND [two_col_key_test].[ID1]<>N'9' 
AND 
(
    [two_col_key_test].[ID1]=N'FILLER TEXT' 
    AND [two_col_key_test].[ID2]>=N'' 
    OR [two_col_key_test].[ID1]>N'FILLER TEXT'
)

    扫描残差

    搜索计划执行两个搜索操作：

    Seek Keys[1]: 
    Prefix: 
    [two_col_key_test].ID1 = Scalar Operator(N'FILLER TEXT'), 
        Start: [two_col_key_test].ID2 >= Scalar Operator(N'')
Seek Keys[1]: 
    Start: [two_col_key_test].ID1 > Scalar Operator(N'FILLER TEXT')

    ...匹配谓词的这一部分：

    (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))

    剩余谓词应用于通过上述查找条件的行（示例中的所有行）。

    但是，每个不等式都由两个单独的小于 OR 大于的测试代替：

    ([two_col_key_test].[ID1]<N'1' OR [two_col_key_test].[ID1]>N'1') 
AND ([two_col_key_test].[ID1]<N'10' OR [two_col_key_test].[ID1]>N'10') 
AND ([two_col_key_test].[ID1]<N'11' OR [two_col_key_test].[ID1]>N'11') 
AND ([two_col_key_test].[ID1]<N'12' OR [two_col_key_test].[ID1]>N'12') 
AND ([two_col_key_test].[ID1]<N'13' OR [two_col_key_test].[ID1]>N'13') 
AND ([two_col_key_test].[ID1]<N'14' OR [two_col_key_test].[ID1]>N'14') 
AND ([two_col_key_test].[ID1]<N'15' OR [two_col_key_test].[ID1]>N'15') 
AND ([two_col_key_test].[ID1]<N'16' OR [two_col_key_test].[ID1]>N'16') 
AND ([two_col_key_test].[ID1]<N'17' OR [two_col_key_test].[ID1]>N'17') 
AND ([two_col_key_test].[ID1]<N'18' OR [two_col_key_test].[ID1]>N'18') 
AND ([two_col_key_test].[ID1]<N'19' OR [two_col_key_test].[ID1]>N'19') 
AND ([two_col_key_test].[ID1]<N'2' OR [two_col_key_test].[ID1]>N'2') 
AND ([two_col_key_test].[ID1]<N'20' OR [two_col_key_test].[ID1]>N'20') 
AND ([two_col_key_test].[ID1]<N'3' OR [two_col_key_test].[ID1]>N'3') 
AND ([two_col_key_test].[ID1]<N'4' OR [two_col_key_test].[ID1]>N'4') 
AND ([two_col_key_test].[ID1]<N'5' OR [two_col_key_test].[ID1]>N'5') 
AND ([two_col_key_test].[ID1]<N'6' OR [two_col_key_test].[ID1]>N'6') 
AND ([two_col_key_test].[ID1]<N'7' OR [two_col_key_test].[ID1]>N'7') 
AND ([two_col_key_test].[ID1]<N'8' OR [two_col_key_test].[ID1]>N'8') 
AND ([two_col_key_test].[ID1]<N'9' OR [two_col_key_test].[ID1]>N'9')

    寻求剩余

    重写每个不等式，例如：

    [ID1] <> N'1'  ->  [ID1]<N'1' OR [ID1]>N'1'

    ...在这里适得其反。排序感知字符串比较是昂贵的。将比较次数加倍解释了您看到的大部分 CPU 时间差异。

    您可以通过使用未记录的跟踪标志 9130 禁用非 sargable 谓词的推送来更清楚地看到这一点。这会将残差显示为单独的过滤器，您可以单独检查性能信息：

    扫描

    寻找

    这也将突出对搜索的轻微基数错误估计，这解释了为什么优化器首先选择搜索而不是扫描（它期望搜索部分消除一些行）。

    虽然不等式重写可能使（可能过滤）索引匹配成为可能（以充分利用 b 树索引的搜索能力），但如果两半最终都在残差中，则最好随后恢复这种扩展。您可以将此作为对SQL Server 反馈站点的改进提出建议。

    另请注意，原始（“旧版”）基数估计模型恰好为此查询默认选择扫描。

    • 32

相关问题