Better execution plan if parameter is stored in local variable
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)) &lt;= [Date] 
and [Date] &lt; 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.