Why Are My SQL Server 2008 Queries with Date Expressions So Slow?

Query Optimization: Addressing Slow Query Performance with Date Expressions

When working with SQL Server 2008, it's crucial to understand the impact of date expressions on query performance. Consider the following query:

Where FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE)

This query runs exceptionally slowly when compared to a simpler version that uses a string literal:

Where FK.DT = '2013-05-01'

Cause of the Performance Issue

The slow performance is caused by a bug in SQL Server's cardinality estimator. The bug affects the accuracy of the estimates when using complex date expressions. In this case, the expression evaluates to the start of the current month ('1786-06-01'), misestimating the number of matching rows.

Solution

To address the performance issue, it is recommended to use the following expression instead:

Where FK.DT = cast(getdate() + 1 - datepart(day, getdate()) as date)

This expression calculates the first day of the current month, providing more accurate cardinality estimates and significantly improving query performance.

Additionally, enabling trace flag 4199 can resolve the bug and provide more accurate cardinality estimates for complex date expressions. However, it is important to note that this may have implications for other queries that rely on correct cardinality estimation.

Best Practices

For optimal query performance, consider using simpler date expressions or using string literals when possible. Keep in mind that complex date expressions can lead to misestimated cardinality and impact query optimization.

The above is the detailed content of Why Are My SQL Server 2008 Queries with Date Expressions So Slow?. For more information, please follow other related articles on the PHP Chinese website!