SQL Server嵌套循环运算符中基数估计错误导致性能问题

Question

我有一个SQL Server查询，其中包含一个连接1百万行（Acct）的大表和一个包含1万行（AcctTxns）的小表的内部连接，但是SQL Server生成了一个执行计划，基数估算错误。

我已经将问题简化为以下语句：

SELECT p.AcctNo, p.Balance + t.TotalAmt as New Balance
    FROM Acct p JOIN AcctTxns t 
    ON p.AcctNo = t.AcctNo

完整执行计划

嵌套循环运算符显示的“估计行数”为16.2588，而“实际行数”为10000。

我正在使用MS SQL Server 2016（13.0.1742.0）。

我尝试了许多修复方法，包括：

1. 更新统计信息
2. 使用临时表进行中间结果
3. 关闭2014基数估计器
4. 以多种不同的方式重写SQL语句（这导致了上述问题的核心）

但它们都无法解决这个问题。 嵌套循环的错误估计会级联产生tempDB溢出，从而影响性能。

有没有人遇到过类似的问题？ 感谢任何帮助解决这个问题。 谢谢。

以下代码设置了此问题：

--- [a] 1 million row Numbers table
DROP TABLE IF EXISTS #Numbers;
CREATE TABLE #Numbers (Number int PRIMARY KEY);
INSERT INTO #Numbers (Number) 
 SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 
 FROM sys.objects A CROSS JOIN sys.objects B 

--- [b] Create Acct table and populate with 1 million accounts
DROP TABLE IF EXISTS dbo.Acct;
CREATE TABLE dbo.Acct (
  PkID  int             not null IDENTITY(1,1),
  AcctNo    varchar(48)     not null PRIMARY KEY,
  Balance   decimal(20,10)  not null constraint DF_Balance default(0)
)
INSERT INTO dbo.Acct (AcctNo) 
 SELECT RIGHT( (REPLICATE('0',6) + CAST(number as varchar(6))), 6)
 FROM #Numbers
 ORDER BY Number

--- [c] Insert 10K transactions. Each Acct gets 2 txns
DROP TABLE IF EXISTS dbo.AcctTxns;
CREATE TABLE dbo.AcctTxns
(
  PkID      int                not null IDENTITY(1,1),
  AcctNo        varchar(48)    not null,
  TxnID     nvarchar(50)       not null,    
  Amt           decimal(20,10) not null,
  TxnStatus nvarchar(10)       not null,
  LastBalance   decimal(20,10) null
  PRIMARY KEY (AcctNo, TxnID, TxnStatus)
)
DROP TABLE IF EXISTS #Acct_Inserted_3XB9F;
CREATE TABLE #Acct_Inserted_3XB9F
(
  AcctNo        varchar(48)         not null PRIMARY KEY,   
  Balance       decimal(20,10)      null
)

declare @TxnCount int = 10000
; WITH Txns (RowNo, TxnID) AS (
   SELECT Number, '#T9-' + RIGHT(REPLICATE('0',8) + CAST(Number as varchar(8)), 8)
   FROM #Numbers WHERE Number BETWEEN 1 AND @TxnCount/2
  UNION
   SELECT Number, '#T9-' + RIGHT(REPLICATE('0',8) + CAST(Number as varchar(8)), 8)
   FROM #Numbers WHERE Number BETWEEN @TxnCount/2+1 AND @TxnCount
)
INSERT INTO dbo.AcctTxns (AcctNo, TxnID, Amt, TxnStatus)
  SELECT A.AcctNo, T.TxnID, 100, 'COMM'
  FROM dbo.Acct A JOIN Txns T ON A.PkID = T.RowNo

--- [d] Update statistics
UPDATE STATISTICS dbo.Acct;
UPDATE STATISTICS dbo.AcctTxns;

--- [e] PROBLEM HERE ...
SET STATISTICS IO, XML ON;
SELECT TxnCount=COUNT(1) 
FROM dbo.Acct A INNER JOIN dbo.AcctTxns T 
ON A.AcctNo = T.AcctNo
SET STATISTICS IO, XML OFF;

- Y-Mi Wong

---

尝试使用全扫描更新统计信息，并运行您的查询一次。您还可以尝试使用选项（Recompile）提示。 - Tharunkumar Reddy

执行计划已经粘贴到建议的位置...(第二个链接)。在我看来，这是一个很好准备的问题。 - Paul Maxwell

谢谢Tharunkumar。我之前尝试过使用OPTION (RECOMPILE)，但是没有起作用，结果一样。我会犹豫在生产环境中使用这个提示，因为它会降低其他查询的性能——这是一个OLTP表。 - Y-Mi Wong

是的，我已经在Brent的“粘贴计划”上拥有了完整的执行计划。谢谢。 - Y-Mi Wong

注意 UNION。但那只是快速设置环境的代码。主要问题在于第“[e] PROBLEM HERE”行。 - Y-Mi Wong

Answer 1

看起来你在这两个表上缺少非聚集索引，尤其是在你进行连接的列上。

CREATE NONCLUSTERED INDEX NC_AcctNo on Acct(AcctNo) INCLUDE (Balance);
CREATE NONCLUSTERED INDEX NC_AcctNo on Acctxns(AcctNo) INCLUDE (TotalAmt);

如果您没有使用WHERE子句从两个表中过滤数据，则查询应该会有更好的估计值，但是您将获得索引扫描而不是聚集索引扫描，如果您使用上述索引，则性能略微更好。

但是它们仍然可能需要一些时间和资源，具体取决于您需要返回的行数。

此外，您可以查看Paul White关于运算符估计的this answer以及其他答案。

- Radu Gheorghiu

---

谢谢，但索引不是问题所在。真正的代码已经使用了你建议的覆盖索引。问题在于INNER JOIN转换为“嵌套循环”运算符，其中“预估行数”与前面索引扫描/查找节点输入的数字相差甚远。请注意，这些输入节点每个都有正确的“预估行数”10000。 - Y-Mi Wong

@Y-MiWong 我没有测试的机会，但是你的PK怎么样呢？我建议尝试将AcctTxns表的PK从默认（且静默）CLUSTERED更改为NONCLUSTERED，然后仅在AcctNo列上创建聚集键。也许这样可以给你更好的估计值，也许可以给你想要的MERGE / HASH连接？ - Radu Gheorghiu

也尝试过这种方法。在实际代码中，Acct和AcctTxns表都是临时表，我通过ETL过程填充了它们。我尝试先填充这些临时表，然后创建必要的覆盖索引的单独非聚集索引。我甚至尝试在这些索引创建之后显式地更新统计信息。但是没有成功。我还测试了使用真实表的情况（上面的代码），问题仍然存在。 - Y-Mi Wong