下面是我在生产中遇到的一些简化版本（在处理异常多的批次的一天，计划变得灾难性地更糟）。
已使用新的基数估计器针对 2014 年和 2016 年对重现进行了测试。
CREATE TABLE T1 (FromDate DATE, ToDate DATE, SomeId INT, BatchNumber INT);
INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
ToDate = '2017-01-01',
SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
BatchNumber = 1
FROM master..spt_values v1
CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));
INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM master..spt_values v1,
master..spt_values v2
T1包含 1,000 行。
、和在所有这些FromDate中都是相同的。唯一不同的值是介于和之间的值ToDateBatchNumberSomeId0999
+------------+------------+--------+-----------+
| FromDate | ToDate | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 | 0 | 1 |
| 2017-01-01 | 2017-01-01 | 1 | 1 |
....
| 2017-01-01 | 2017-01-01 | 998 | 1 |
| 2017-01-01 | 2017-01-01 | 999 | 1 |
+------------+------------+--------+-----------+
T2包含 100 万行
但只有 1,000 个不同的。每个重复 1,000 次如下。
+-------------------------+--------+-------+
| SomeDateTime | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 | 0 | 1000 |
| 2017-01-01 00:00:00.000 | 1 | 1000 |
...
| 2017-01-01 00:00:00.000 | 998 | 1000 |
| 2017-01-01 00:00:00.000 | 999 | 1000 |
+-------------------------+--------+-------+
执行以下
SELECT *
FROM T1
INNER JOIN T2
ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
AND T1.SomeId = T2.SomeId
WHERE T1.BatchNumber = 1
在我的机器上大约需要 7 秒。实际行和估计行对于计划中的所有操作员来说都是完美的。
现在向 T1 添加 3,000 个附加批次（批次编号为 2 至 3001）。这些每个克隆批号 1 的现有千行
INSERT INTO T1
SELECT T1.FromDate,
T1.ToDate,
T1.SomeId,
Nums.NewBatchNumber
FROM T1
CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
FROM master..spt_values v1, master..spt_values v2) Nums
并更新运气的统计数据
UPDATE STATISTICS T1 WITH FULLSCAN
并再次运行原始查询。
SELECT *
FROM T1
INNER JOIN T2
ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
AND T1.SomeId = T2.SomeId
WHERE T1.BatchNumber = 1
在杀死它之前，我让它运行了一分钟。到那时它已经输出了 40,380 行，所以我想输出完整的一百万行需要 25 分钟。
唯一改变的是我添加了一些与T1.BatchNumber = 1谓词不匹配的额外行。
然而，计划现在已经改变了。它使用嵌套循环代替，虽然来自的行数t1仍然正确估计为 1,000 (①)，但连接行数的估计现在已从 100 万下降到一千 (②)。
所以问题是……
为什么添加额外的行会以BatchNumber <> 1某种方式影响对何时连接的行的估计BatchNumber = 1？
向表中添加行最终会减少整个查询中估计的行数，这似乎违反直觉。
请务必记住，当您更改查询或表中的数据时，无法保证一致性。查询优化器可能会切换到使用不同的基数估计方法（例如使用密度而不是直方图），这会使两个查询看起来彼此不一致。话虽如此，查询优化器似乎在您的情况下做出了不合理的选择，所以让我们深入研究。
你的演示太复杂了，所以我要用一个更简单的例子来工作，我相信它显示了相同的行为。开始数据准备和表定义：
这是
SELECT要调查的查询：这个查询非常简单，因此我们可以在没有任何跟踪标志的情况下计算出基数估计的公式。但是，我将尝试使用 TF 2363 来更好地说明优化器中发生的事情。目前尚不清楚我是否会成功。
定义以下变量：
C1= 表 T1 中的行数C2= 表 T2 中的行数S1T1.SomeId=过滤器的选择性我的主张是上述查询的基数估计如下：
C2S1C1C2S1C1让我们通过一些例子，虽然我不打算通过我测试的每一个。对于初始数据准备，我们有：
C1= 1000C2= 2S1= 1.0因此，基数估计应该是：
下面的不可能伪造的屏幕截图证明了这一点：
使用未记录的跟踪标志 2363，我们可以获得一些关于正在发生的事情的线索：
使用新的 CE，我们得到通常 16% 的估计值
BETWEEN。这是由于新的 2014 CE 的指数退避。每个不等式的基数估计值为 0.3，因此BETWEEN计算为 0.3 * sqrt(0.3) = 0.164317。将 16% 的选择性乘以 T2 和 T1 中的行数，我们得到我们的估计。似乎很合理。让我们将行数T2增加到 7。现在我们有以下内容：C1= 1000C2= 7S1= 1.0因此，基数估计应为 1000，因为：
查询计划证实了这一点：
我们可以再看一眼 TF 2363，但看起来选择性在幕后进行了调整以遵守上限。我怀疑这会
CSelCalcSimpleJoinWithUpperBound阻止基数估计值超过 1000。让我们跳到
T250000 行。现在我们有：C1= 1000C2= 50000S1= 1.0因此，基数估计应该是：
查询计划再次证实了这一点。在你已经弄清楚公式后，猜测估计值就容易多了：
TF 输出：
对于此示例，指数退避似乎无关紧要：
现在让我们向 T1 添加 3k 行，
SomeId值为 0。执行此操作的代码：现在我们有：
C1= 4000C2= 50000S1= 0.25因此，基数估计应该是：
查询计划证实了这一点：
这与您在问题中提出的行为相同。我向表中添加了不相关的行，基数估计值降低了。为什么会这样？注意粗线：
选择性：0.25
选择性：0.00025
似乎这种情况下的基数估计计算如下：
C1* * * / ( * )S1C2S1S1C1Or for this particular example:
The general formula can of course can be simplified to:
C2*S1Which is the formula that I claimed above. It seems like there's some cancellation going on that shouldn't be. I would expect the total number of rows in
T1to be relevant to the estimate.If we insert more rows into
T1we can see the lower bound in action:The cardinality estimate in this case is 1000 rows. I will omit the query plan and the TF 2363 output.
最后，这种行为非常可疑，但我不知道它是否是错误。我的示例与您的复制不完全匹配，但我相信我观察到相同的一般行为。另外我要说的是，您选择初始数据的方式有点幸运。优化器似乎进行了大量的猜测，所以我不会太在意原始查询返回的 100 万行与估计完全匹配的事实。