why does SQL Server ignore index?

In our data warehouse, we have a fact that looks like this:

 CREATE TABLE [fct].[VMP10MinData](
     [VMP10MinDataID] [bigint] IDENTITY(1,1) NOT NULL,
     [DT1TurbineID] [int] NOT NULL,
     [DT1TurbineEventID] [int] NOT NULL,
     [TTimeStamp] [smalldatetime] NOT NULL,
     [TTimeStampUTC] [smalldatetime] NOT NULL,
     [Gen_RPM_Max] [numeric](9, 1) NULL,
     ...
     [Grd_InverterPhase1_Temp_AvgA] [smallint] NULL,
     [UpdatedDate] [datetime] NOT NULL,
     [IsCorrected] [bit] NOT NULL,
     [TRATransformJobID] [int] NOT NULL,
     [TTimeStampLocal] [smalldatetime] NOT NULL,
     [UpdatedID] [bigint] NULL,
 CONSTRAINT [PK_VMP10MinData] PRIMARY KEY CLUSTERED 
 (
     [DT1TurbineID] ASC,
     [TTimeStamp] ASC
 )WITH (DATA_COMPRESSION = ROW ON PARTITIONS (207 TO 253),
 DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 206)) ON [PS_FctVMP10MinData1]([TTimeStamp])
 ) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO
 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_DT1TurbineID_PCTimeStamp_TTimeStamp] ON [fct].[VMP10MinData]
 (
     [DT1TurbineID] ASC,
     [PCTimeStamp] ASC,
     [TTimeStamp] ASC
 )WITH (DATA_COMPRESSION = ROW) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO
 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_DT1TurbineID_TTimeStamp_TTimeStampUTC] ON [fct].[VMP10MinData]
 (
     [DT1TurbineID] ASC,
     [TTimeStamp] ASC,
     [TTimeStampUTC] ASC
 )
 INCLUDE (     [DT1TurbineEventID],
     [Amb_LaPM_ActSectorInPeriod],
     [Amb_WindSpeed_Avg],
     [Amb_Temp_Avg],
     [Grd_Prod_PsblePwr_Avg],
     [Grd_Prod_Pwr_Avg],
     [Grd_Prod_Pwr_InternalDerateStat],
     [Grd_Sets_ActPwr_Source10Min],
     [Grd_Sets_ActPwr_ReferenceValue10Min],
     [HCnt_Avg_Tot],
     [HCnt_Avg_Gen1],
     [HCnt_Avg_Gen2],
     [HCnt_Avg_GrdOn],
     [HCnt_Avg_GrdOk],
     [HCnt_Avg_TrbOk],
     [HCnt_Avg_AlarmAct],
     [HCnt_Avg_Run],
     [HCnt_Avg_SrvOn],
     [HCnt_Avg_AmbOk],
     [HCnt_Avg_WindOk],
     [HCnt_TotAccumulated_Gen1],
     [HCnt_TotAccumulated_Gen2],
     [HCnt_TotAccumulated_GrdOn],
     [HCnt_TotAccumulated_WindOk],
     [Nac_Direction_Avg],
     [Prod_TotAccumulated_TotActPwr],
     [Sys_Logs_FirstActAlarmPar1],
     [Sys_Logs_FirstActAlarmPar2],
     [TTimeStampLocal],
     [VMP10MinDataID]) WITH (DROP_EXISTING = OFF, ONLINE = OFF, DATA_COMPRESSION = ROW) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO
 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_TTimeStamp_DT1TurbineID] ON [fct].[VMP10MinData]
 (
     [TTimeStamp] ASC,
     [DT1TurbineID] ASC
 )WITH (DATA_COMPRESSION = ROW) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO
 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_UpdatedID_DT1TurbineID_TTimeStamp] ON [fct].[VMP10MinData]
 (
     [UpdatedID] ASC,
     [DT1TurbineID] ASC,
     [TTimeStamp] ASC
 )
 INCLUDE (     [UpdatedDate]) 
 WHERE ([UpdatedID] IS NOT NULL)
 WITH (DATA_COMPRESSION = ROW) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO
 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_VMP10MinDataID_TTimeStamp] ON [fct].[VMP10MinData]
 (
     [VMP10MinDataID] ASC,
     [TTimeStamp] ASC
 )WITH (DATA_COMPRESSION = ROW) ON [PS_FctVMP10MinData1]([TTimeStamp])
 GO

We have seen this issue previously, when we rebuild one partition, and the statistics is invalidated, because of different sample rates. But I thought it was fixed with this CU.

The query want to do two complete table scans, despite the good indexes. Why does it prefer such a brute force plan?

Best regards, Henrik Staun Poulsen

link textlink text