I have two stored procedures. This one is incredibly fast (~ 2 seconds)
CREATE PROCEDURE [schema].[Test_fast]
@week date
AS
BEGIN
declare @myweek date = @week
select distinct serial
from [schema].[tEventlog] as e
join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
where s.SourceName = 'source_name'
and (e.EventCode = 1 or e.EventCode = 9)
and cast(@myweek as datetime2(3)) <= [Date]
and [Date] < dateadd(day, 7, cast(@myweek as datetime2(3)))
END
And this one runs slow (~ 2 Hours):
create PROCEDURE [schema].[Test_slow]
@week date
AS
BEGIN
select distinct serial
from [schema].[tEventlog] as e
join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
where s.SourceName = 'source_name'
and (e.EventCode = 1 or e.EventCode = 9)
and cast(@week as datetime2(3)) <= [Date]
and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))
END
The only real difference is the line (using the local variable @myweek):
declare @myweek date = @week
Here are the execution plans. The first plan is from [schema].[Test_fast] and the second is from [schema].[Test_slow]:
My question is: Why does SQL Server 2012 get a much better execution plan (faster) when I take the parameter and store it in a local variable, and then use this local variable. Is there something broken with the statistics or the indexes? (I also wonder why the second execution plan is not using any kind of parallel execution).
UPDATE:
I give the 2 SPs the same Parameter and started them in the same time (nearly 2s time diff) the is not auto update of statistic in this DB.
example:
EXEC [schema].[Test_fast]
@week = '2016-02-08'
EXEC [schema].[Test_slow]
@week = '2016-02-08'
Here is the Execution Plan:
https://gist.github.com/anonymous/6e404f896d9613c2061a#file-sp_execution_plan-sqlplan
An additional update of the index does also have no effect.