Lately, we have a customer who reported that a query used to run 20 minutes in 2000 now run serveral hours in SQL 2008. After examining the plan, we discovered that the estimates were much more off in SQL 2008. Specifically, the query has a where condition like (DateColumn< DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())))

First of all, if you have a simple expression involving constant and DateAdd like (dateadd(month, -6, '2009-01-01')), SQL Server can optimize it and replace it with actual value like ('2008-07-01'). But if you use a complex expression like above, SQL Server cannot use the same otpimization. Estimates in general can be off.

In this specific scenario, SQL Server 2008/2005, ends up with low estimates. This will impact plan choice. Note that the issue is only happening when you combine datediff with dateadd. If you just use dateadd with getdate (dateadd(month, -6, getdate()), estimate is still reasonable.

If you have any expression like DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate()))) , you should manually compute it yourself and then pass it along with your true query.

Instead of doing "select COUNT (*) from t inner join t2 on t.c1 = t2.c2 where c1 < DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0) ", you create a procedure that takes a datetime parameter and then call the procedure and pass the actual value. Here is an example. Another approach is to use EXEC ('sql string') to achieve same effect.

create procedure p_test @begindate datetime

as

select COUNT (*) from t inner join t2 on t.c1 = t2.c2 where c1 < @begindate option (recompile)

go

declare @dt datetime

set @dt =DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)

exec p_test @dt

After customer took our advice using the procedure, his execution is cut down to 55 seconds (even much faster than 2000's 20-25 minutes).

As a general advice, if you have an expression like above that can be pre-computed, it's better you do so to let sql know the actual value so that it can do a better estimate.

Here is the repro that demonstrate low cardinality estimate in 2005 and 2008

use tempdb

go

create table t (c1 datetime)

go

set nocount on

begin tran

declare @k int

set @k = 0

while @k < 100

begin

declare @i int

set @i = 0

while @i < 365

begin

declare @dt datetime

set @dt = '2008-01-01'

set @dt = DATEADD (dd,@i, @dt)

insert into t values (@dt)

set @i = @i + 1

end

set @k = @k + 1

end

commit tran

go

create index indx on t (c1 )

go

set showplan_all on

go

--for 2008 and 2005, the index seek estimated just 1 row

-- for sql 2000, index seek estimates 10,950 rows

/*

select COUNT (*) from t where c1 <DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)

|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))

|--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))

|--Index Seek(OBJECT:([tempdb].[dbo].[t].[indx]), SEEK:([tempdb].[dbo].[t].[c1] < dateadd(month,datediff(month,'1900-01-01 00:00:00.000',dateadd(month,(-6),getdate())),'1900-01-01 00:00:00.000')) ORDERED FORWARD)

*/

select COUNT (*) from t where c1 <DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)

go

set showplan_all off

go

====================================================

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support