主页 / dba / 问题 / 227747
Accepted
Frederik Vanderhaegen
Asked: 2019-01-23 01:46:45 +0800 CST

由于数据偏斜，嵌套循环的估计值非常低

  • 772

在 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
sql-server performance

2 个回答

  1. Best Answer

    通常，当我有一个包含倾斜数据的表时，我会使用过滤统计来改进估计。但在这种情况下，这似乎不起作用。

    您应该会发现以下过滤后的统计数据很有用：

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

    这为优化器提供了有关匹配值分布的信息，从而为连接提供了更好的选择性估计：id field = 'varchar'

    实际执行计划

    上面的执行计划使用过滤后的统计信息显示了完全正确的估计，导致优化器选择散列连接（出于成本原因）。

    此分布信息比估计器用于匹配连接直方图（精细或粗略对齐）或什至一般假设（例如简单连接、基本包含）的精确方法重要得多。

    如果您不能这样做，您的选择大致与您对上一个问题Sort spills to tempdb due to varchar(max)的回答中概述的一样。我的偏好可能是一个中间临时表。

    • 7

  2. 完全同意过滤索引，添加这个答案是为了扩展@PaulWhite 提到的另一个选项，使用中间临时表并因此摆脱SORT操作员

    您可以添加索引或更改现有索引：

    CREATE INDEX IX_SkewedTable_MytableId_startdate
ON SkewedTable(myTableId,startdate)
INCLUDE(remark);

    将值插入中间临时表

    CREATE TABLE  #temp2(param int);
INSERT INTO  #temp2(param)
SELECT t.id 
FROM mytable t 
WHERE t.field = 'varchar';

    在此处输入图像描述

    在临时表上添加索引 

    CREATE INDEX IX_ID on #temp2(param);

    然后使用 CTE 从查询计划中删除排序运算符 

    ;WITH CTE AS
(
select TOP(999999999999)
s.myTableId,s.id,s.remark from 
SkewedTable s
order by startdate
)
SELECT s.id , s.remark
from CTE  s
INNER JOIN #temp2 
on s.myTableId = #temp2.param
OPTION(RECOMPILE)

    正如@Forrest 提到的将排序降低到这里

    结果：

    在此处输入图像描述

    这删除了排序运算符。

    • 0

相关问题