Better execution plan if parameter is stored in local variable

Question

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.

Answer 1

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

From the execution plan provided, this is exactly what happened in your case.

When a local variable is used, the value in the variable cannot be sniffed:

Notice the blank "Compiled Value". The query optimizer estimates a higher number of rows based on the average distribution of values in the Date column, (or possibly a complete guess) leading to the parallel plan.

When the stored procedure parameter is used directly, the value of @week is sniffed:

The optimizer estimates the number of rows that will match the query predicates using the value '2016-02-08', plugged into:

and cast(@week as datetime2(3)) <= [Date] 
and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))

It comes out with an estimate of one row, leading to the choice of a serial plan with the key lookup. The predicates above are not very friendly for cardinality estimation, so the 1-row estimate may not be very accurate. You could try enabling trace flag 4199 but there's no guarantee the estimate will improve.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options

In general, it is also possible that the initial run of the stored procedure occurs with a very selective value for @week, with only a small number of rows expected. Another possible cause of problems occurs when a very recent value of @week is used on the initial call, before statistics had been updated to cover this range of values (this is the Ascending Key Problem).

A very selective sniffed value for @week may cause the query optimizer to choose a non-parallel plan with an index seek and a key lookup. This plan will be cached for reuse for future executions of the procedure with different parameter values. Where a later execution (with a different value for @week) selects many more rows than originally, the plan is likely to perform poorly, because seek + key lookup is no longer a good strategy.