KB2481274 - FIX: A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2

Symptoms

Assume that you run a query on an instance of Microsoft SQL Server 2005 or Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2 that meets the following conditions:

• The query contains a relational operator. For example, the less than (<) operator.

• There is a DATEDIFF function in the operand of the relational operator.

In this situation, SQL Server may select a suboptimal query plan to run this query. Therefore, the query may run slowly.

Cause

This issue occurs because the SQL Server optimizer underestimates the number of rows that will be returned when you use the DATEDIFF function.

Resolution

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Workaround

To work around this issue, rewrite the query so that the query plan does not use the DATEDIFF function in predicates. For example, the following may run slowly:create procedure proc_test @date datetime
as
select COUNT (*) from t where c1 <DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6,@date)), 0) However, you can rewrite the query as the following to work around the issue:create procedure proc_test2 @date datetime
as
declare @datestr nvarchar(50), @sqlstr nvarchar(1024)
set @datestr = CAST (DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6,@date)), 0) as nvarchar(50))
set @sqlstr ='select COUNT (*) from t where c1 < ''' + @datestr + ''''
exec (@sqlstr)

References

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499 New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates