Return to Answer

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:

• Performance Surprises and Assumptions : DATEADD()

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.

An incorrect cardinality estimation reproduces for me in all versions of SQL Server up to and including 2019 CU8 GDR (build 15.0.4083) when the 70 model cardinality estimator is used.

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

• Performance Surprises and Assumptions : DATEADD()

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:

• Performance Surprises and Assumptions : DATEADD()

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:

• Performance Surprises and Assumptions : DATEADD()

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:

• Performance Surprises and Assumptions : DATEADD()

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:

• Performance Surprises and Assumptions : DATEADD()