OPTION (RECOMPILE) is Always Faster; Why? [duplicate]

Question

Despite having read botht hese 2 questions about procedure recompile below

Stored Procedure With Recompile

OPTION (RECOMPILE) is Always Faster; Why?

I am still having a problem - my query is generating a better plan when I leave the OPTION (RECOMPILE) on. If I remove that line I get a different query plan.

I would like to understand the reasons for this, on this particular situation. I will explain and give details, I am sure DBAs will benefit from it.

This is my query:

  DECLARE @DT DATETIME = GETDATE()

   SELECT SaleHour, 
            TodayOrderCount, 
                  PeriodAvgOrderCount, 
                  TodayOrderTotal, 
                  PeriodAvgOrderTotal, 
                  TodayOrderAverageValue, 
                  PeriodAvgOrderAverageValue
   FROM
   (

          SELECT 
                          [Hour], 
                          NoOfOrders AS TodayOrderCount, 
                               [Total$] AS TodayOrderTotal, 
                               SaleHour, 
                               [AOV$] AS TodayOrderAverageValue,
                 DATEDIFF(HOUR, dt, @DT ) AS Dif

          FROM   dbo.tblWOrderSummaryByHour
          WHERE  dt > @DT  - 1

   ) AS TD
   INNER JOIN
   (

          SELECT 
                          [Hour], 
                          AVG(NoOfOrders) AS PeriodAvgOrderCount, 
                          AVG([Total$]) AS PeriodAvgOrderTotal, 
                          SUM([Total$]) / SUM(NoOfOrders) AS PeriodAvgOrderAverageValue
          FROM   dbo.tblWOrderSummaryByHour
          WHERE  dt BETWEEN @DT -8 AND @DT -1
          GROUP BY [Hour]

   ) AV ON AV.[Hour] = TD.[Hour]

   WHERE TD.Dif < 4

This is my table definition - please note the calculated column:

CREATE TABLE [dbo].[tblWOrderSummaryByHour] ( 
[SaleHour]     VARCHAR(50)                      NOT NULL,
[NoOfOrders]   INT                                  NULL,
[Total$]       MONEY                                NULL,
[AOV$]         MONEY                                NULL,
[ItemsBought]  INT                                  NULL,
[Hour]         INT                                  NULL,
[dt]           AS (CONVERT([datetime],CONVERT([varchar](20),substring([SaleHour],(1),charindex('TO',[SaleHour],(1))-(1))),(103))) PERSISTED,
CONSTRAINT   [PK_tblWOrderSummaryByHour]  PRIMARY KEY CLUSTERED    ([SaleHour] asc))

These are my indexes: (please note I use the calculated column on indexes)

 ALTER TABLE [dbo].[tblWOrderSummaryByHour]
 ADD  CONSTRAINT [PK_tblWOrderSummaryByHour]
 PRIMARY KEY CLUSTERED (  [SaleHour] ASC  ) 
GO


CREATE NONCLUSTERED INDEX idx_tblWOrderSummaryByHour_dt
 ON [dbo].[tblWOrderSummaryByHour]
 (  [dt] ASC  )  
 INCLUDE ( [AOV$] , [Hour] , [NoOfOrders] , [Total$] ) 

GO


CREATE NONCLUSTERED INDEX idx_hour_dt_INCL
 ON [dbo].[tblWOrderSummaryByHour]
 (  [Hour] ASC  , [dt] ASC  )  
 INCLUDE ( [AOV$] , [NoOfOrders] , [SaleHour] , [Total$] ) 
 GO

this is the plan generated when I add OPTION(RECOMPILE) at the end of the query

these are the stats IO and TIME:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 2 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 16 ms, elapsed time = 21 ms.

(3 row(s) affected) Table 'tblWOrderSummaryByHour'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

this is the plan generated without any OPTION at the end of the query

these are the stats IO and TIME:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 37 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

(3 row(s) affected) Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tblWOrderSummaryByHour'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 15 ms, elapsed time = 9 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

It is a small table 45k rows the stats are updated in less than one second.

UPDATE STATISTICS dbo.tblWOrderSummaryByHour WITH FULLSCAN

My machine (it is a virtual box):

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Hardware information

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], affinity_type_desc, 
virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Here I show what I have inside the table:

select top 1000
* 
from dbo.tblWOrderSummaryByHour
order by dt desc

Question:

stats are updated, why I am getting different plans?

Can I avoid using OPTION(RECOMPILE)?