由於數據偏斜，嵌套循環的估計值非常低

在 SQL Server 2016 SP2 上，我們有一個查詢，它對嵌套循環運算符的估計非常低。由於估計值過低，此查詢也會溢出到 tempdb。

如果我是正確的，SQL Server 2014+ 使用粗直方圖估計來計算連接上的估計行數。

但是當我執行查詢時，SQL Server 使用密度向量來計算估計的行數。如果沒有子句

，SQL Server 是否僅使用粗略直方圖估計？where

通常，當我有一個包含傾斜數據的表時，我會使用*過濾的統計資訊來改進估計。*但在這種情況下，這似乎不起作用。

有沒有辦法改進對嵌套循環的估計？

使用以下程式碼，您可以重現數據：

create table MyTable
(
   id int identity,
   field varchar(50),
   constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
   id int identity,
   startdate datetime,
   myTableId int,
   remark varchar(50),
   constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
( 
   select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
   union all
   select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go

通常，當我有一個包含傾斜數據的表時，我會使用*過濾的統計資訊來改進估計。*但在這種情況下，這似乎不起作用。

您應該會發現以下過濾後的統計數據很有幫助：

CREATE STATISTICS [stats id (field=varchar)]
ON dbo.MyTable (id)
WHERE field = 'varchar'
WITH FULLSCAN;

這為優化器提供了有關匹配值分佈的資訊，從而為連接提供了更好的選擇性估計：id field = 'varchar'

上面的執行計劃使用過濾後的統計數據顯示了完全正確的估計，導致優化器選擇散列連接（出於成本原因）。

這個分佈資訊比估計器用來匹配連接直方圖（精細或粗略對齊）甚至一般假設（例如簡單連接、基本包含）的精確方法重要得多。

如果您不能這樣做，您的選項大致與您之前的問題Sort 溢出到 tempdb 由於 varchar(max)的回答中概述的一樣。我的偏好可能是中間臨時表。

引用自：https://dba.stackexchange.com/questions/227747