Skip to main content
2 of 6
Added Aaron's article from a comment
Paul White
  • 90.3k
  • 30
  • 424
  • 663

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.

Update: Aaron has now written an article about this for SQLperformance.com:

Paul White
  • 90.3k
  • 30
  • 424
  • 663