DATEADD não está produzindo uma expectativa SARGable de uma busca de índice

Eu tenho uma [UserActivity]tabela básica que captura um ActivityTypeIdpor UserIde o ActivityDateem que a atividade ocorreu.

Estou escrevendo uma consulta/procedimento armazenado que permite a entrada do @UserId, @ForTypeId, bem como o @DurationIntervale @DurationIncrementpara retornar resultados dinamicamente com base no número N de segundos/minutos/horas/dias/meses/anos. Dado que o datepartargumento dentro DATEADD/DATEDIFFnão permite parâmetros, eu tive que reverter um pouco de truque para obter os resultados desejados dentro da WHEREcláusula.

Inicialmente escrevi a consulta usando DATEDIFF, mas imediatamente após escrever e dar uma olhada no plano de execução, lembrei que não é uma função SARGable (junto com o fato de que os níveis de precisão podem oferecer para algumas datas caindo em um ano bissexto). Então, eu reescrevi a consulta para utilizar DATEPARTo pensamento de que eu atingiria uma busca de índice em vez de uma varredura de índice e geralmente teria um desempenho melhor.

Infelizmente, descobri que escrever a consulta como DATEADDoferece os mesmos resultados: uma verificação de índice está ocorrendo e o otimizador de consulta não está aproveitando o índice não clusterizado em relação ao [ActivityDate].

Eu li a postagem no blog de Aaron Bertrand, "Performance Surprises and Assumptions: DATEADD" e implementei as mudanças que ele descreveu para CONVERTa DATEADDparte na datetime2definição de coluna equivalente devido a truques estranhos envolvidos com datetime2. No entanto, o problema ainda estava presente mesmo depois de fazê-lo.

Para ilustrar melhor o cenário, aqui está uma definição de tabela comparável.

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

Preencha a tabela com dados fictícios recursivamente para 5 usuários diferentes com um aleatório ActivityTypeIdentre 1 e 10 com um novo a ActivityDatecada 4 minutos.

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;

Abaixo está a primeira consulta que escrevi com DATEDIFF. Observe que estou excluindo os predicados @UserIde @ForTypeIdintencionalmente para evitar essas pesquisas de chave e reduzir o ruído nos planos anexados.

Como você encontrará em PasteThePlan para esta consulta , ele está realizando uma verificação de índice conforme o esperado, uma vez que DATEDIFFnão é 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

Abaixo está a DATEADDconsulta. PasteThePlan aqui. Infelizmente, uma busca de índice não está ocorrendo. Esta pode ser uma suposição incorreta da minha parte, mas estou perplexo por que isso não está ocorrendo.

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

Qual é a causa disso? O comportamento que estou vendo é resultado do meu uso de ORnegação de qualquer potencial para que ele chegue a usar o índice? Estou ignorando algo meticulosamente óbvio aqui?

ATUALIZAÇÃO: Minha segunda pergunta acima me levou a realizar uma consulta anterior às ORoperações. A consulta executou a busca de índice, então algo está ocorrendo durante essas comparações que o SQL Server não gosta. PasteThePlan aqui.

DECLARE @DurationIncrement int = 1

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

ATUALIZAÇÃO: Solução compartilhada aqui.