title: Queries with identical execution plan have significant execution time differences
body: <p>I have two queries on the same fact table that feed two tables, one with daily granularity and one with hourly granularity. </p>
  
  <pre><code>select 
      t1.date
      ,sum(t1.col1) as col1
      ...
      ,t1.coln
      ,t2.col1
      ,t3.col1
  from table1 t1
  left join table2 t2
  on t1.id = t2.id
  left join table3 t3
  on t1.id = t3.id
  where 
  t1.date between '20180210' and '20180211'
  group by t1.date
           ,t1.col1
           ....
           ,t1.coln
           ,t2.col1
           ,t3.col1
  
  
  select 
      t1.date
      ,datepart(hh,t1.datetime) as hour
      ,sum(t1.col1) as col1
      ...
      ,t1.coln
      ,t2.col1
      ,t3.col1
  from table1 t1
  left join table2 t2
  on t1.id = t2.id
  left join table3 t3
  on t1.id = t3.id
  where 
  t1.date between '20180210' and '20180211'
  and t1.cola = 'value1'
  and t2.colb in ('value2','value3')
  group by t1.date
           ,datepart(hh,t1.datetime) 
           ,t1.col1
           ....
           ,t1.coln
           ,t2.col1
           ,t3.col1
  </code></pre>
  
  <p>The columns are generic but everything else is exactly like my production queries. The query plan involves one table scan in table 1 which is 96% of the cost. 
  These queries are scheduled to run early in the morning (5:00AM) and for daily data (the queries have to process around 200.000 rows of table1 which is the fact table) execution time is ~1min for daily and ~1,5min for hourly data.</p>
  
  <p>In the rare occasion that my SSIS jobs fail, if i try to re-run the hourly query, according to sp_who2 and server monitoring, it takes extremely high cpu time, consumes all server's cpu (usage goes up to 99-100%) and query never runs. If I do not update the data and let SSIS job to run next day and load the two previous days, everything runs smoothly. 
  I do not understand why the addition of datepart() function makes so much of a performance decline, where the plan and estimated cpu and I/O costs for both plans are exactly the same. </p>
  
  <p>Any ideas of what I should search for? </p>