why does SQL Server ignore index?
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
Thank you very much for answering this question and your talk at SQLBits.
<< effectively, MinDataID > 6.8 billion
Unfortunately this is not correct. I have two watermarks; a) new rows where VMP10MindataID between 6.80 billion and 6.81 billion, b) updated rows where I want 1500 rows between 4.3 billion, but only if VMP10mindataID is less than 6.8 billion.
I do understand that this might result in two sets of key lookup. This normally performs reasonably well.
Best regards,
Henrik
Do you have any guesses about why SQL Server would pick such a brute force plan, instead of the usual plan?
This situation occurs quite regular, and after Easter holidays (that starts in 10 minutes) I'll have a bunch of stats logged to disk, and then I'll check to see if there are any clues as to why it picks such an expensive plan.
Thank you very much for your help.
I've added two new plans, that show a huge difference between estimated and actual plan. The estimated plan warns about Residual IO, where the Actual plan is just fast.
I also note that the plan looks more complicated when I rely on implicit casting, as opposed to explicit casting.
Can you explain the difference between estimated and actual plan?