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: