DATEADD 不产生索引查找的 SARGable 期望

我有一个基本[UserActivity]表，用于捕获活动发生的ActivityTypeId时间UserId和地点。ActivityDate

我正在编写一个查询/存储过程，允许输入@UserId,@ForTypeId以及@DurationInterval和 并@DurationIncrement根据N秒/分钟/小时/天/月/年的数量动态返回结果。鉴于datepart内部DATEADD/DATEDIFF参数不允许参数，我不得不恢复一些技巧才能在WHERE子句中获得所需的结果。

最初我使用 编写查询DATEDIFF，但在编写并查看执行计划后，我立即记起它不是 SARGable 函数（以及精度级别可以提供闰年下降的某些日期这一事实）。因此，我重新编写了查询，以利用DATEPART我会命中索引查找而不是索引扫描的想法，并且通常表现得更好。

不幸的是，我发现编写查询DATEADD提供了相同的结果：正在进行索引扫描，并且查询优化器没有利用针对[ActivityDate].

我阅读了Aaron Bertrand 的博客文章“性能惊喜和假设：DATEADD”，并CONVERT由于. 但是，即使这样做了，问题仍然存在。DATEADDdatetime2datetime2

为了更好地说明该场景，这里有一个可比较的表定义。

DROP TABLE IF EXISTS [dbo].[UserActivity]
IF OBJECT_ID('[dbo].[UserActivity]', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[UserActivity] (
        [UserId] [int] NOT NULL
        ,[UserActivityId] [bigint] IDENTITY(1,1) NOT NULL
        ,[ActivityTypeId] [tinyint] NOT NULL
        ,[ActivityDate] [datetime2](0) NOT NULL CONSTRAINT [DF_UserActivity_ActivityDate] DEFAULT GETDATE()
        ,CONSTRAINT [PK_UserActivity] PRIMARY KEY CLUSTERED ([UserActivityId] ASC)
        ,INDEX [IX_UserActivity_UserId] NONCLUSTERED ([UserId] ASC)
        ,INDEX [IX_UserActivity_ActivityTypeId] NONCLUSTERED ([ActivityTypeId] ASC)
        ,INDEX [IX_UserActivity_ActivityDate] NONCLUSTERED ([ActivityDate] ASC)
    )
END;
GO

为 5 个不同的用户递归地用虚拟数据填充表，随机数ActivityTypeId在 1 到 10 之间，ActivityDate每 4 分钟一个新的。

DECLARE @UserId int = (SELECT ISNULL((SELECT TOP (1) [UserId] + 1 FROM [dbo].[UserActivity] ORDER BY [UserId] DESC), 1))
;WITH [UserActivitySeed] AS (
    SELECT
        CONVERT(datetime2(0), '01/01/2018') AS 'ActivityDate'
    UNION ALL
    SELECT
        DATEADD(minute, 4, [ActivityDate])
    FROM
        [UserActivitySeed]
    WHERE
        [ActivityDate] < '2018-04-01')
INSERT INTO [dbo].[UserActivity] ([UserId], [ActivityTypeId], [ActivityDate])
SELECT
    @UserId
    ,ABS(CHECKSUM(NEWID()) % 9) + 1
    ,[ActivityDate]
FROM
    [UserActivitySeed] OPTION (MAXRECURSION 32767);

GO 5

ALTER INDEX ALL ON [dbo].[UserActivity] REBUILD;

下面是我用 编写的第一个查询DATEDIFF。请注意，我有意排除了@UserId和@ForTypeId谓词，以避免那些关键查找并减少附加计划中的噪音。

正如您将在PasteThePlan 上针对此查询发现的那样，它正在按预期执行索引扫描，因为它DATEDIFF不是 SARGable。

DECLARE @UserId int = 1
DECLARE @ForTypeId int = 3
DECLARE @DurationInterval varchar(6) = 'hour'
DECLARE @DurationIncrement int = 1

SELECT
    COUNT(UA.[UserActivityId]) AS 'ActivityTypeCount'
FROM
    [dbo].[UserActivity] UA
WHERE
    -- Exclude the @UserId and @ForTypeId predicates.
    -- UA.[UserId] = @UserId
    -- AND UA.[ActivityTypeId] = @ForTypeId
    -- AND 
    CASE
        WHEN @DurationInterval IN ('year', 'yy', 'yyyy') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0 / 365.25
        WHEN @DurationInterval IN ('month', 'mm', 'm') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0 / 365.25 * 12
        WHEN @DurationInterval IN ('day', 'dd', 'd') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0 / 24.0
        WHEN @DurationInterval IN ('hour', 'hh') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 3600.0
        WHEN @DurationInterval IN ('minute', 'mi', 'n') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE()) / 60.0
        WHEN @DurationInterval IN ('second', 'ss', 's') THEN DATEDIFF(SECOND, UA.[ActivityDate], GETDATE())
    END < @DurationIncrement

下面是DATEADD查询。在此处粘贴计划。不幸的是，没有发生索引查找。这对我来说可能是一个错误的假设，但我很困惑为什么它根本没有发生。

DECLARE @UserId int = 1
DECLARE @ForTypeId int = 3
DECLARE @DurationInterval varchar(6) = 'hour'
DECLARE @DurationIncrement int = 1

SELECT
    COUNT(UA.[UserActivityId]) AS 'ActivityTypeCount'
FROM
    [dbo].[UserActivity] UA
WHERE
    -- Exclude the @UserId and @ForTypeId predicates.
    -- UA.[UserId] = @UserId
    -- AND UA.[ActivityTypeId] = @ForTypeId
    -- AND 
    (
        (@DurationInterval IN ('year', 'yy', 'yyyy') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(YEAR, -@DurationIncrement, GETDATE())))
        OR
        (@DurationInterval IN ('month', 'mm', 'm') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(MONTH, -@DurationIncrement, GETDATE())))
        OR
        (@DurationInterval IN ('day', 'dd', 'd') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(DAY, -@DurationIncrement, GETDATE())))
        OR
        (@DurationInterval IN ('hour', 'hh') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(HOUR, -@DurationIncrement, GETDATE())))
        OR
        (@DurationInterval IN ('minute', 'mi', 'n') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(MINUTE, -@DurationIncrement, GETDATE())))
        OR
        (@DurationInterval IN ('second', 'ss', 's') AND UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(SECOND, -@DurationIncrement, GETDATE())))
        )

这是什么原因？我看到的行为是否是我使用OR否定它甚至可以使用索引的任何可能性的结果？我是否忽略了这里非常明显的东西？

更新：我上面的第二个问题导致我在OR操作之前执行查询。查询执行了索引查找，因此在这些比较过程中发生了 SQL Server 不喜欢的事情。在此处粘贴计划。

DECLARE @DurationIncrement int = 1

SELECT
    COUNT(UA.[UserActivityId]) AS 'ActivityTypeCount'
FROM
    [dbo].[UserActivity] UA
WHERE
    UA.[ActivityDate] > CONVERT(datetime2(0), DATEADD(HOUR, -@DurationIncrement, GETDATE()))

更新： 解决方案在这里共享。