sql - SQL SERVER 2017 查询优化器提供一百万行估计

Question

我们有 SQL Server 2017，我们刚刚将兼容模式更改为 140。我们希望提高性能，但不像这么多查询需要很长时间并消耗大量服务器资源。原因似乎是新的查询优化器在实际行数为 2 时估计数百万行。

例如，通过一个小查询，QO 估计 1500 行 执行计划，但估计错误，但如果使用 OPTION (QUERYTRACEON 9481)，估计是完全准确的 执行计划，估计准确

我不知道这是否是参数嗅探案例，因为如果我们更改其中一个过滤器的值，则查询是正确的。但是在兼容性程度为 110 的情况下，2 个查询可以正确执行。

带其他参数的执行计划

我们已经完美更新了统计数据。有谁知道我应该怎么做才能解决这个问题？

已编辑** 使用 Ola Hallengren 的算法每天都会更新统计数据，其中 SQL Server 会自动计算所需的样本。但是，由于我们正在使用此查询进行测试，因此我们进行了几次全扫描。数据库统计选项有： 自动创建增量统计 False
自动创建统计 True
自动更新统计 True
异步自动更新统计 False
Legacy Cardinality Estimation OFF
Parameter Sniffing ON
Query Optimizer Fixes OFF

我刚刚尝试了 OPTION (RECOMPILE) 并且 QO 估计有 2300 行

您可以在执行计划的链接中看到实际的查询，但是是这样的：

SELECT  
        table1.field1  
FROM table1  
INNER JOIN table2 ON table1.field1 = table2.field1 AND table2.date_field=(  
      SELECT MAX( date_field ) 
      FROM table2 as t2  
      WHERE table2.field1 = t2.field1  
             AND t2.date_field<=@date 
)  
INNER JOIN table3 ON table1.field1 = table3.field1 AND table3.date_field=(  
      SELECT MAX( date_field ) 
      FROM table3 as t3  
      WHERE table3.field1 = t3.field1  
             AND t3.date_field<=@date 
)

field1 是 table1 中的键，field1 和 date_field 是 table2 和 table3 中的键。这种查询在我们的系统中很常见，直到升级兼容模式才出现问题。

这些表中的数据可能每天更改一次。原始查询确实要复杂得多，估计有 3 亿行，而在上一级它没有问题。为了调查发生了什么，我从查询的最基本级别开始。
这些表最多有 85,000 条记录，密度为每个键 3 或 4 条记录。最多，在某些情况下达到 6。
更改服务器配置的步骤如下：

1. 亚马逊的新 EC2 实例与 SQL Server 2017 标准版
2. 我们创建兼容级别为 110 的数据库并恢复 SQL Server 2012 的数据
3. 2 周后我们修改了兼容级别 140。
4. 2 周后，我们启用查询存储

标签： sqlsql-server