DATEADD not producing a SARGable expectation of an index seek

Question

I have a basic [UserActivity] table that captures an ActivityTypeId per UserId and the ActivityDate at which the Activity occurred.

I am writing a query/stored procedure that allows for input of the @UserId, @ForTypeId, as well as the @DurationInterval and @DurationIncrement to dynamically return results based on N number of seconds/minutes/hours/days/months/years. Given that the datepart argument within DATEADD/DATEDIFF does not allow parameters, I had to revert to a bit of trickery in order to get the desired results within the WHERE clause.

Initially I wrote the query using DATEDIFF, but immediately after writing and taking a peek at the execution plan, I remembered that it is not a SARGable function (along with the fact the precision levels could offer for some dates falling off in a Leap Year). So, I re-wrote the query to utilize DATEPART thinking that I would hit an index seek instead of an index scan and generally perform better.

Unfortunately, I've found that writing the query as DATEADD offers up the same results: an index scan is occurring, and query optimizer is not leveraging the non-clustered index against [ActivityDate].

I read Aaron Bertrand's blog post, "Performance Surprises and Assumptions: DATEADD", and implemented the changes he described to CONVERT the DATEADD portion into the equivalent datetime2 column definition due to weird trickery involved with datetime2. However, the issue was still present even after doing so.

To better illustrate the scenario, here is a comparable table definition.

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

Populate the table with dummy data recursively for 5 different users with a random ActivityTypeId between 1 and 10 with a new ActivityDate every 4 minutes.

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;

Below is the first query I wrote with DATEDIFF. Note I am excluding the @UserId and @ForTypeId predicates intentionally so to avoid those key lookups and reduce noise within the plans attached.

As you'll find on PasteThePlan for this query, it is performing an index scan as expected given that DATEDIFF is not 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

Below is the DATEADD query. PasteThePlan here. Unfortunately, an index seek is not occurring. This may be an incorrect assumption on my part, but I'm perplexed as to why it isn't occurring at all.

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

What is the cause of this? Is the behavior I'm seeing a result of my usage of OR negating any potential for it to even get to using the index? Am I overlooking something painstakingly obvious here?

UPDATE: My second question above lead me to perform a query foregoing the OR operations. The query performed the index seek, so something is occurring during these comparisons that SQL Server does not like. PasteThePlan here.

DECLARE @DurationIncrement int = 1

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

UPDATE: Solution shared here.

Daniel Hutmacher · Accepted Answer · 2018-03-27 15:18:24Z

The OR condition evaluates at compile-time, rather than at runtime, which means that your WHERE condition does not generate a seek.

And just to clean up the code, I refactored out your CONVERT to make the code a bit more readable.

I would try changing the WHERE clause to:

UA.[ActivityDate]>CONVERT(datetime2(0), (CASE
    WHEN @DurationInterval IN ('year', 'yy', 'yyyy') THEN DATEADD(year, -@DurationIncrement, GETDATE())
    WHEN @DurationInterval IN ('month', 'mm', 'm')   THEN DATEADD(month, -@DurationIncrement, GETDATE())
    WHEN ...
    END))

I don't have access to an environment where I can verify this, but please let me know if it works out.

That was the solution! By swapping the WHERE clause around as such, it hits the non-clustered index appropriately. I've updated my OP with the correct query. Thank you, sir. — PicoDeGallo, Mar 27, 2018 at 15:30

Mark Sinkinson · Accepted Answer · 2018-03-27 15:24:49Z

At compilation, SQL Server does not know the value of @DurationInterval and so compiles the plan that is best-suited to retrieving the data for any possible scenario.

You can prove that out by adding a WITH (FORCESEEK) option to the query, which shows an that, in order to do an Index Seek for the given query, there will be an individual seek for each OR condition.

https://www.brentozar.com/pastetheplan/?id=HkE3lkuqf

The scan is determined to be a more optimal way of retrieving the data than 6 seeks.

@Daniel Hutmacher provides an optimal solution that performs a single Index Seek on IX_UserActivity_ActivityDate. Alternatively, you could add an OPTION(RECOMPILE), although this would force recompilation every time the query is run, potentially causing more harm than good.

Note those Filters have Startup Expression Predicates, so only one seek will execute at runtime. — Paul White, Mar 27, 2018 at 21:15

David Spillett · Accepted Answer · 2018-03-27 15:23:09Z

6

A "kitchen sink" query like that (multiple distinct filtering clauses one or more of which is used depending on the value of an input) is never going to be sargable even if all its individual clauses are.

The two quick options are to break them into individual procedures and call each as needed by a master procedure or use ad-hoc SQL.

For a detailed article describing a number of options for this type of query/procedure, see http://www.sommarskog.se/dyn-search.html

edited Mar 27, 2018 at 15:23

answered Mar 27, 2018 at 15:17

David Spillett

31.7k3 gold badges47 silver badges88 bronze badges

1

I talk about the kitchen sink pattern here: blogs.sentryone.com/aaronbertrand/…
– Aaron Bertrand
Mar 27, 2018 at 17:26
Another post of yours to bookmark. Thanks, @AaronBertrand
– PicoDeGallo
Mar 27, 2018 at 18:44

Add a comment |

PicoDeGallo · Accepted Answer · 2018-04-04 15:40:48Z

For future reference, this is the solution I came to based on Daniel Hutmatcher's proposed answer.

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 
    UA.[ActivityDate] > CONVERT(datetime2(0),
    (CASE
        WHEN @DurationInterval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, -@DurationIncrement, GETDATE())
        WHEN @DurationInterval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, -@DurationIncrement, GETDATE())
        WHEN @DurationInterval IN ('day', 'dd', 'd') THEN DATEADD(DAY, -@DurationIncrement, GETDATE())
        WHEN @DurationInterval IN ('hour', 'hh') THEN DATEADD(HOUR, -@DurationIncrement, GETDATE())
        WHEN @DurationInterval IN ('minute', 'mi', 'n') THEN DATEADD(MINUTE, -@DurationIncrement, GETDATE())
        WHEN @DurationInterval IN ('second', 'ss', 's') THEN DATEADD(SECOND, -@DurationIncrement, GETDATE())
    END))

Stack Exchange Network

DATEADD not producing a SARGable expectation of an index seek

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
index
performance
execution-plan
query-performance
or ask your own question.

Hot Network Questions

DATEADD not producing a SARGable expectation of an index seek

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverindexperformanceexecution-planquery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
index
performance
execution-plan
query-performance
or ask your own question.