主页 / dba / 问题 / 134828
Accepted
crokusek
Asked: 2016-04-09 15:54:35 +0800 CST

如何改进 DateAdd() 对索引约束的视图中 1 行的估计

  • 772

使用 Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)。

给定一个表和索引：

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

以下每个查询的实际行数为 3.1M，估计行数显示为注释。

当这些查询在 View 中提供另一个查询时，优化器会因为 1 行估计而选择循环连接。 如何在此基础上改进估计以避免覆盖父查询连接提示或求助于 SP？

使用硬编码日期效果很好：

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

这些等效查询是视图兼容的，但都估计 1 行：

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

尝试一些提示（但 N/A 无法查看）：

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s    
    on s.CreatedUtc > d.MinCreatedUtc  
option (recompile);

尝试使用参数/提示（但 N/A 以查看）：

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

估计与实际

统计数据是最新的。

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

显示了直方图的最后几行（总共 189 行）：

在此处输入图像描述

sql-server sql-server-2012

3 个回答

  1. Best Answer

    一个不如 Aaron 全面的答案，但核心问题是DATEADD使用datetime2类型时的基数估计错误：

    连接：当 sysdatetime 出现在 dateadd() 表达式中时估计不正确

    一种解决方法是使用GETUTCDATE（返回日期时间）：

    WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

    请注意，到datetime2的转换必须在 之外DATEADD才能避免错误。

    当使用 70 模型基数估计器时，不正确的基数估计在所有版本的 SQL Server 中重现，包括 2019 CU8 GDR（内部版本 15.0.4083）。

    Aaron Bertrand为 SQLPerformance.com 写了一篇关于此的文章：

    • 7

  2. 在某些情况下，SQL Server 可能会对DATEADD/进行非常疯狂的估计DATEDIFF，具体取决于参数是什么以及您的实际数据是什么样的。DATEDIFF我在处理月初时写了这个，还有一些解决方法，在这里：

    但是，我的典型建议是停止在 where/join 子句中使用DATEADD/ 。DATEDIFF

    以下方法虽然在过滤范围内的闰年（在这种情况下会包括额外的一天）时不是非常准确，但四舍五入到一天时，会变得更好（但仍然不是很好！）估计，就像您的 non-sargableDATEDIFF反对专栏方法，并且仍然允许使用搜索：

    DECLARE @start date = DATEFROMPARTS
(
  YEAR(GETUTCDATE())-1, 
  MONTH(GETUTCDATE()), 
  DAY(GETUTCDATE())
);

SELECT ... WHERE CreatedUtc >= @start;

    您可以操纵输入以DATEFROMPARTS避免闰日出现问题，使用它DATETIMEFROMPARTS来获得更高的精度而不是四舍五入到当天等。这只是为了证明您可以在不使用的情况下用过去的日期填充变量DATEADD（这只是一个多做一些工作），从而避免估计错误（已在 2014+ 中修复）中更严重的部分。

    为避免在闰日出现错误，您可以从去年的 2 月 28 日而不是 29 日开始执行此操作：

    DECLARE @start date = DATEFROMPARTS
(
  YEAR(GETUTCDATE())-1, 
  MONTH(GETUTCDATE()), 
  CASE WHEN DAY(GETUTCDATE()) = 29 AND MONTH(GETUTCDATE()) = 2 
    THEN 28 ELSE DAY(GETUTCDATE()) END
);

    你也可以通过检查今年是否过了闰日来添加一天，如果是，则在开始时添加一天（有趣的是，DATEADD 在这里使用仍然可以进行准确的估计）：

    DECLARE @base date = GETUTCDATE();
IF GETUTCDATE() >= DATEFROMPARTS(YEAR(GETUTCDATE()),3,1) AND 
  TRY_CONVERT(datetime, DATEFROMPARTS(YEAR(GETUTCDATE()),2,29)) IS NOT NULL
BEGIN
  SET @base = DATEADD(DAY, 1, GETUTCDATE());
END

DECLARE @start date = DATEFROMPARTS
(
  YEAR(@base)-1, 
  MONTH(@base),
  CASE WHEN DAY(@base) = 29 AND MONTH(@base) = 2 
    THEN 28 ELSE DAY(@base) END
);

SELECT ... WHERE CreatedUtc >= @start;

    如果您需要比午夜更准确，那么您可以在选择之前添加更多操作：

    DECLARE @accurate_start datetime2(7) = DATETIME2FROMPARTS
(
  YEAR(@start), MONTH(@start), DAY(@start),
  DATEPART(HOUR,  SYSUTCDATETIME()), 
  DATEPART(MINUTE,SYSUTCDATETIME()),
  DATEPART(SECOND,SYSUTCDATETIME()), 
  0,0
);

SELECT ... WHERE CreatedUtc >= @accurate_start;

    现在，您可以将所有这些都放在一个视图中，它仍然会使用搜索和 30% 的估计，而不需要任何提示或跟踪标志，但它并不漂亮。嵌套的 CTE 只是为了让我不必输入SYSUTCDATETIME()一百次或重复重复使用的表达式——它们仍然可以被计算多次。

    CREATE VIEW dbo.v5 
AS
  WITH d(d) AS ( SELECT SYSUTCDATETIME() ),
  base(d) AS
  (
    SELECT DATEADD(DAY,CASE WHEN d >= DATEFROMPARTS(YEAR(d),3,1) 
      AND TRY_CONVERT(datetime,RTRIM(YEAR(d))+RIGHT('0'+RTRIM(MONTH(d)),2)
      +RIGHT('0'+RTRIM(DAY(d)),2)) IS NOT NULL THEN 1 ELSE 0 END, d)
    FROM d
  ),
  src(d) AS
  (
    SELECT DATETIME2FROMPARTS
    (
      YEAR(d)-1, 
      MONTH(d),
      CASE WHEN MONTH(d) = 2 AND DAY(d) = 29
        THEN 28 ELSE DAY(d) END,
      DATEPART(HOUR,d), 
      DATEPART(MINUTE,d),
      DATEPART(SECOND,d),
      10*DATEPART(MICROSECOND,d),
      7
    ) FROM base
  )
  SELECT DISTINCT SessionId FROM [User].[Session]
    WHERE CreatedUtc >= (SELECT d FROM src);

    这比您DATEDIFF针对该专栏的内容要冗长得多，但正如我在评论中提到的那样，这种方法不是可搜索的，并且可能会在必须阅读大部分表格的情况下具有竞争力，但我怀疑它会成为一种负担因为“去年”在表格中所占的百分比较低。

    另外，仅供参考，以下是我尝试重现时获得的一些指标：

    在此处输入图像描述

    我无法获得 1 行估计值，我非常努力地匹配您的分布（313 万行，去年为 289 万行）。但是你可以看到：

    • 我们的两种解决方案都执行大致相同的读取。
    • 您的解决方案不太准确，因为它只考虑了日期边界（这可能很好，我的观点可能不太精确以匹配）。
    • 4199 + 重新编译并没有真正改变估计（或计划）。

    不要从持续时间数字中得出太多 - 它们现在很接近，但随着表格的增长可能不会保持接近（我再次相信，因为即使是搜索仍然需要阅读表格的大部分内容）。

    以下是 v4（你的 datediff 对比列）和 v5（我的版本）的计划：

    在此处输入图像描述

    在此处输入图像描述

    • 6

  3. 将 dateadd() 替换为 datediff() 以获得足够的近似值（30%ish）。

     select distinct SessionId from [User].Session     -- 1.2M est, 3.0M act.
  where datediff(day, CreatedUtc, sysutcdatetime()) <= 365

    这似乎是一个类似于MS Connect 630583的错误。

    选项重新编译没有区别。

    计划统计

    • 1

相关问题