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: