主页 / dba / 问题 / 238099
Accepted
Yunus UYANIK
Asked: 2019-05-15 01:16:15 +0800 CST

为什么MSSQL在执行计划中选择scan？

  • 772

这是我的查询（它是 Microsoft Axapta 查询）：

(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID 
FROM LEDGERJOURNALTRANS T1 
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))

当前执行计划：

在此处输入图像描述

当前计划

实际上，表上有一个适当的索引。

索引列：（PARTITION，DATAAREAID，REVRECID）

分片： 在此处输入图像描述

我尝试了索引力。这个执行计划（index seek+key lookup）比after plan（索引扫描）要快：

力指数计划

在此处输入图像描述

在此处输入图像描述

我试图：

  • 更新统计

  • 更改了它的列顺序，例如 (REVRECID,PARTITION,DATAAREAID)

MSSQL 为什么选择聚集索引？

sql-server index

1 个回答

  1. Best Answer

    估计，选择大量列和谓词下推

    查询的估计没有考虑到扫描的剩余谓词比从聚集索引中获取所有这些额外列的查找 + 键查找的成本更高。这导致选择聚集索引扫描 + 剩余谓词而不是索引查找。

    我的版本是 Microsoft SQL Server 2016 (RTM-GDR)

    这些关于谓词下推的估计值在SQL Server 2016 SP1中得到了改进

    更新以改进对 SQL Server 2016 中涉及残留谓词下推的查询执行计划的诊断

    为了改进对“症状”部分中描述的方案的诊断，SQL Server 2016 Service Pack 1 (SP1) 引入了一个新的显示计划 XML 属性，即 Estimated Rows Read。此属性提供在应用残差谓词之前将由运算符读取的估计行数。此更新是对 KB 3107397 的补充。

    这将添加 EstimatedRowsRead=""到查询计划 XML，在您的情况下，如果选择了扫描，这将接近或匹配剩余谓词。

    这应该可以解决您的问题

    残差谓词示例

    在此处输入图像描述

    在此处输入图像描述

    读取 1.2M 行返回 0

    索引扫描查询估计总成本

    EstimatedTotalSubtreeCost="0.00449281">

    索引搜索查询估计总成本

    EstimatedTotalSubtreeCost="0.00672858">

    由于不考虑残差谓词，这高于索引扫描估计，这就是选择性能较差的计划的原因。

    主要解决方案

    主要解决方案是升级到至少 SP1 以添加：

    更新以改进对 SQL Server 2016 中涉及残留谓词下推的查询执行计划的诊断

    由于SP2 CU6 已于2019年 3 月 19 日发布，因此您应该更快更频繁地进行修补，这将是一个更好的选择。

    另一个注意事项，SQL Server 2016 的SP1添加了许多附加功能，例如内存中 OLTP、压缩、列存储索引......

    其他可能值得一提或不值得一提的解决方法

    • 如果不需要，选择更少的列
    • 将所有这些列添加到 NC 索引的包含列
    • 您可以尝试使用OPTION(QUERYTRACEON 4138) （也许）禁用行目标
    • 使用WITH(INDEX))提示

    与 SQL Server 2016 SP1 的比较

    在运行与您类似的查询时，强制在 SQL2016 SP1 版本上使用聚集索引： 在此处输入图像描述

    估计的子树成本要高得多。

    EstimatedTotalSubtreeCost="93.6951"

    聚集索引扫描的估计子树成本在哪里

    <RelOp AvgRowSize="4788" EstimateCPU="1.36996" EstimateIO="185.267" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00448209" TableCardinality="1245280">

    低

    EstimatedTotalSubtreeCost="0.00448209"

    主要区别在于

    EstimatedRowsRead="1000000"

    在应用了SP1的 SQL 2016 上执行查询时显示。

    并且在使用指定的 NC 指数进行测试时

    CREATE INDEX IX_PARTITION_DATAAREAID_REVRECID
ON dbo.LEDGERJOURNALTRANS(PARTITION,DATAAREAID,REVRECID);

<RelOp AvgRowSize="980" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000000">

    索引搜索的 EstimatedTotalSubtreeCost （不是整个计划的总数）也很低：

     EstimatedTotalSubtreeCost="0.0032831

    并且我的测试查询的总估计子树成本非常接近您的

    EstimatedTotalSubtreeCost="0.00657048">
    • 5

相关问题