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

Question

我有一个包含 100 万行的大表 (Acct) 和包含 10,000 行的较小表 (AcctTxns) 之间的 INNER JOIN 的 SQL Server 查询，但是 SQL Server 生成的执行计划错误的基数估计.

我已将问题归结为以下陈述:

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

Full Execution Plan

嵌套循环运算符显示 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;

Answer 1

您似乎在两个表中缺少非聚集索引，在您要加入的列上。

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

您应该对查询有更好的估计，但是如果您没有使用 WHERE 子句从两个表中过滤数据，那么您将获得索引扫描而不是聚簇索引扫描，这如果您使用上述索引，则在性能方面会稍微好一些。

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

另外你可以看看this answer来自 Paul White 关于运营商估计以及问题的其他答案。