如何針對索引改進受 DateAdd() 約束的視圖中 1 行的估計

使用 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)

嘗試一些提示（但不適用）：

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);

嘗試使用參數/提示（但不適用查看）：

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 行）：

DATEADD比 Aaron 的答案不太全面，但核心問題是使用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 寫了一篇關於此的文章：

• 性能驚喜和假設：DATEADD()

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