8

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Given a table and index:

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)

Actual Rows for each of the following queries is 3.1M, the estimated rows are shown as comments.

When these queries feed another query in a View, the optimizer chooses a loop join because of the 1 row estimates. How to improve the estimate at this ground level to avoid overriding the parent query join hint or resorting to an SP?

Using a hardcoded date works great:

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

These equivalent queries are view compatible but all estimate 1 row:

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)

Try some hints (but N/A to View):

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

Try using Parameter/Hints (but N/A to View):

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

Estimate vs Actual

The statistics are up to date.

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

The last few rows of the histogram (189 rows total) are shown:

enter image description here

6

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:

Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression

One workaround is to use GETUTCDATE (which returns datetime):

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

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.

The issue of the 1 row cardinality estimation reproduces for me in all versions of SQL Server up to and including 2016 RC0 where the 70 model cardinality estimator is used.

Aaron Bertrand has written an article about this for SQLPerformance.com:

6

In some scenarios SQL Server can have really wild estimates for DATEADD/DATEDIFF, depending on what the arguments are and what your actual data looks like. I wrote about this for DATEDIFF when dealing with beginning of the month, and some workarounds, here:

But, my typical advice is to just stop using DATEADD/DATEDIFF in where/join clauses.

The following approach, while not super accurate when a leap year is in the filtered range (it will include an extra day in that case), and while rounded to the day, will get better (but still not great!) estimates, just like your non-sargable DATEDIFF against the column approach, and still allow a seek to be used:

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

SELECT ... WHERE CreatedUtc >= @start;

You could manipulate the inputs to DATEFROMPARTS to avoid issues on leap day, use DATETIMEFROMPARTS to get more precision instead of rounding to the day, etc. This is just to demonstrate that you can populate a variable with a date in the past without using DATEADD (it's just a little more work), and thus avoid the more crippling part of the estimation bug (which is fixed in 2014+).

To avoid errors on leap day, you can do this instead, starting from last year's Feb 28 instead of 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
);

You could also say add a day by checking to see if we're past a leap day this year, and if so, add a day to the beginning (interestingly, using DATEADD here still allows for accurate estimates):

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;

If you need to be more accurate than to the day at midnight, then you can just add more manipulation before the select:

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;

Now, you could jam all of this in a view, and it will still use a seek and the 30% estimate without requiring any hints or trace flags, but it ain't pretty. Nested CTEs are just so that I don't have to type SYSUTCDATETIME() a hundred times or repeat reused expressions - they can still be evaluated multiple times.

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

This is a lot more verbose than your DATEDIFF against the column, but as I mentioned in a comment, that approach is not sargable, and will probably perform competitively while most of the table has to be read anyway, but I suspect it will become a burden as "the last year" becomes a lower percentage of the table.

Also, just for reference, here are some of the metrics I got when I tried to reproduce:

enter image description here

I couldn't get 1-row estimates, and I tried very hard to match your distribution (3.13 million rows, 2.89 million from the last year). But you can see:

  • both of our solutions perform roughly equivalent reads.
  • your solution is slightly less accurate because it only accounts for day boundaries (and that might be fine, my view could be made less precise to match).
  • 4199 + recompile did not really change the estimates (or the plans).

Don't draw too much from the duration figures - they're close now, but may not stay close as the table grows (again, I believe because even the seek still has to read most of the table).

Here are the plans for v4 (your datediff against column) and v5 (my version):

enter image description here

enter image description here

  • In summary, as stated in your blog. this answer provides a usable estimate and seek based plan. The answer by @PaulWhite gives the best estimate. Perhaps the 1 row estimates I was getting (vs 1500) could be due to the table not having any rows in the last ~24 hours. – crokusek Apr 11 '16 at 17:17
  • @crokusek If you say >= DATEADD(DAY, -365, SYSDATETIME()) the bug is that the estimate is based on >= SYSDATETIME(). So technically the estimate is based on how many rows in the table have a CreatedUtc in the future. This is likely 0, but SQL Server always rounds 0 up to 1 for estimated rows. – Aaron Bertrand Apr 11 '16 at 17:56
1

Replace dateadd() with datediff() to get an adequate approximate (30%ish).

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

This appears to be a bug similar to MS Connect 630583.

Option recompile makes no difference.

Plan Stats

  • 2
    Note that applying datediff to the column makes the expression non-sargable, so you're going to have to scan. Which is probably okay when 90+% of the table needs to be read anyway, but as the table gets larger this will prove more costly. – Aaron Bertrand Apr 9 '16 at 13:06
  • Great point. I was thinking it could convert it internally. Verified that it is performing a scan. – crokusek Apr 11 '16 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.