创建覆盖索引后仍在进行键查找

我已经实现了一个覆盖索引来避免键查找：

CREATE INDEX IX_StatusHistory_Covering ON StatusHistory(ID) 
       INCLUDE (Status_ID, StatusComment, StatusReason_ID,
       StatusReasonComment, UserEnteredStatusDateTime, 
       ChangeDateTime, ChangedBy_UserName, IMWBWagonMass) 
       WITH (ONLINE= ON)

但是密钥查找仍在进行中。

执行计划

这是导致此问题的查询：

DECLARE @default_tare DECIMAL(18,2) = 19.94
SELECT      TOP 100  *,         
        [Gross (WI)] -  ISNULL([Tare (WX)],@default_tare) AS [Arrived Nett (WI-WX)],
        [Gross (WN)] - ISNULL([Tare (WT)],@default_tare) AS  [Tipped Nett (WN-WT)],         
        [Client Weight] - ([Gross (WI)] -  ISNULL([Tare (WX)],@default_tare)) AS [Arrived Variance],            
        [Client Weight] - ([Gross (WN)] - ISNULL([Tare (WT)],@default_tare)) AS [Tipped Variance]

FROM        (SELECT     CASE WHEN LoadedWeight > 9999 THEN LoadedWeight/1000 ELSE LoadedWeight END [Client Weight]
                    ,CASE WHEN his.Status_Code IN ('WH','WI') THEN his.IMWBWagonMass END AS [Gross (WI)]
                    ,CASE WHEN his.Status_Code ='WN' THEN his.IMWBWagonMass END AS [Gross (WN)]
                    ,CASE WHEN his.Status_Code = 'WX' THEN his.[IMWBWagonMass] ELSE NULL END AS [Tare (WX)]                 
                    ,CASE WHEN his.Status_Code = 'WT' THEN his.[IMWBWagonMass] ELSE NULL END AS [Tare (WT)]
                    ,dp.Description AS Commodity
                    ,dp.Grade                       
                    ,Header_Client_Name AS Client
                    ,Header_Destination_Name AS Destination
                    ,Header_Origin_Name AS Origin
                    ,Header_Product_Name AS [Commodity Name]
                    ,Header_ProductSubCategory_Name AS [Commodity Group]
                    ,his.StatusComment AS [Status Comment]
                    ,LEFT(LOWER(Rcd.Header_LastUpdatedByUser_UserName),CASE WHEN Header_LastUpdatedByUser_UserName <>'System' THEN CHARINDEX( '@',Rcd.Header_LastUpdatedByUser_UserName)-1 ELSE 999 END) AS [Last updated by]
                    ,ISNULL(Header_StatusDateTime, Header_CreatedDateTime) AS [Last Updated]
                    ,st.Name  AS [Status Name]
                    ,StatusReasonComment AS [Reason Comment]
                    ,StatusReasonName AS [Reason Name]
                    ,UserEnteredStatusDateTime AS [Status Actual Event Time]
                    ,ChangeDateTime AS [Status System Change Time]
                    ,ChangedBy_UserName AS [Status Changed by]
                    ,st.Code  AS [Status Code]
                    ,sr.Code AS [Reason Code]
                    ,his.ID AS [Status History ID]
                    ,his.StatusReasonDateTime  AS [Reason Time]
                    ,his.IMWBWagonMass AS [Wagon Status History Mass]
                    ,rcd.ID AS [Wagon Line ID]
                    ,Wagon_Number AS [Wagon Number]
                    ,GrossWeight AS [Gross (Most Recent)]
                    ,TareWeight AS [Tare (Most Recent)]
                    ,Tarps
                    ,DelayDescription AS [Delay Description]
                    ,rcd.StatusComment AS [Current Wagon Status Comment]
                    ,rcd.StatusDateTime AS [Current Wagon Status Date Time]
                    ,rcd.Status_Code AS [Current Wagon Status Code]
                    ,rcd.Status_Name AS [Current Wagon Status]
                    ,RailConsignment_ID
                    ,Header_Number AS [Consignment]
                    ,Header_DepartureFromClientDateTime AS [Departure Time]
                    ,Header_EstimatedArrivalDateTime AS [Estimated Arrival Time]
                    ,Header_IsInbound
                    ,Header_PermitNumber AS [Permit Nr]
                    ,Header_RailAccountNumber AS [Rail Account Nr]
                    ,Header_Sender_Name AS [Sender]
                    ,Header_SenderSidingNumber AS [Sender Siding Nr]
                    ,Header_Status_Code AS [Train Current Status Code]
                    ,Header_Status_Name AS [Train Current Status]
                    ,Header_StatusDateTime AS [Train Current Status Actual Event Time]
                    ,Header_Train AS [Train]
                    ,Header_CreatedDateTime AS [Created On]
                    ,rcd.dw_timestamp
                    ,CreatedDateTime AS [Wagon Created On]
                    ,Header_Contract_Number AS [Contract Nr]
                    ,rcd.IMWBWagonMass AS [Wagon Mass (Most Recent)]
                    ,Header_WeeklyRailPlan_ID AS [NWB ID]
                    ,NettWeight AS [Nett (Most Recent)]
                    ,CASE WHEN rcd.Status_Code = 'WA' THEN rcd.Header_StatusDateTime ELSE Header_WAChangeDateTime END AS [Wagons Uncoupled From Locomotive]
                    ,CASE WHEN rcd.Status_Code = 'WF' THEN rcd.Header_StatusDateTime ELSE Header_WFChangeDateTime END AS [Consignment Finalised]
                    ,CASE WHEN rcd.Status_Code = 'WG' THEN rcd.Header_StatusDateTime ELSE Header_WGChangeDateTime END AS [Consignment at the Gate]
        FROM        RailConsignmentDetails AS  rcd WITH(NOLOCK)
        JOIN        FullStatusHistoryRailLine AS his WITH(NOLOCK) on his.ID = rcd.ID
        JOIN        DimProduct AS dp WITH(NOLOCK) ON dp.ID = rcd.Header_Product_ID
        LEFT JOIN   DimConsignmentStatus AS st  WITH(NOLOCK)ON st.ID = his.Status_ID
        LEFT JOIN   DimStatusReasons   AS sr WITH(NOLOCK)  ON st.ID = his.StatusReason_ID
        WHERE       Header_Deleted = 0
        AND         rcd.Deleted = 0
        AND         ISNULL(Header_StatusDateTime, Header_CreatedDateTime) BETWEEN CAST(DATEADD(MONTH,-2,GETDATE()) AS DATE) AND GETDATE()+ 1) AS t

FullStatusHistoryLine 视图是：

CREATE VIEW [dbo].[FullStatusHistoryRailLine]
    AS
        SELECT      sh.PK,
                    sh.ID,
                    CAST(sh.Number AS VARCHAR(255)) AS Number,
                    sh.Status_ID,
                    sh.Status_Name,
                    sh.StatusComment,
                    sh.StatusReason_ID,
                    sh.StatusReasonComment,
                    dsr.Name AS StatusReasonName,
                    sh.UserEnteredStatusDateTime,
                    sh.ChangeDateTime,
                    sh.ChangedBy_Id,
                    sh.ChangedBy_UserName,
                    sh.dw_timestamp,
                    dcs.code AS Status_Code,
                    sh.StatusReason_Code,                       
                    prev.StatusReasonDateTime AS FromStatusReasonDateTime,
                    sh.StatusReasonDateTime,
                    sh.IMWBWagonMass
        FROM        StatusHistory AS sh
        INNER JOIN  DimConsignmentStatus AS dcs ON sh.Status_ID = dcs.ID
        LEFT JOIN   DimStatusReasons AS dsr ON dsr.ID = sh.StatusReason_ID
        OUTER APPLY (SELECT TOP 1 StatusReasonDateTime 
                    FROM    StatusHistory
                    WHERE   ID = sh.ID
                    AND     Number = sh.Number
                    AND     IsHeader = sh.IsHeader
                    AND     TransportMode_Name = sh.TransportMode_Name
                    AND     StatusReasonDateTime < sh.StatusReasonDateTime
                    ORDER BY StatusReasonDateTime DESC) AS prev
        WHERE       sh.TransportMode_Name = 'Rail'
        AND         sh.IsHeader = 0
        UNION ALL
        SELECT      CAST(rcd.ID AS VARCHAR) + '_' + CAST(ISNULL(CONVERT(VARCHAR(64),rcd.StatusReasonDateTime,126),CONVERT(VARCHAR(64),rcd.LastUpdatedDateTime,126)) AS VARCHAR) + '_L' AS PK,
                    rcd.ID,
                    CAST(rcd.Wagon_Number AS VARCHAR(255)) AS Number,
                    rcd.Status_ID,
                    rcd.Status_Name,
                    rcd.StatusComment,
                    rcd.StatusReason_ID,
                    '' AS StatusReasonComment,
                    rcd.StatusReason_Name AS StatusReasonName,
                    rcd.StatusDateTime AS UserEnteredStatusDateTime,
                    rcd.StatusSavedDateTime AS ChangeDateTime,
                    rcd.LastUpdatedByUser_Id AS ChangedBy_Id,
                    rcd.LastUpdatedByUser_UserName AS ChangedBy_UserName,
                    rcd.dw_timestamp,
                    rcd.Status_Code,
                    rcd.StatusReason_Code,
                    prev.StatusReasonDateTime AS FromStatusReasonDateTime,
                    rcd.StatusReasonDateTime,
                    rcd.IMWBWagonMass
        FROM        RailConsignmentDetails AS rcd
        OUTER APPLY (SELECT TOP 1 StatusReasonDateTime 
                    FROM    StatusHistory AS sh
                    WHERE   sh.ID = rcd.ID
                    AND     Number =  CAST(rcd.Wagon_Number AS VARCHAR(255))
                    AND     IsHeader = 0
                    AND     TransportMode_Name = 'Rail'
                    AND     StatusReasonDateTime < rcd.Header_StatusReasonDateTime
                    ORDER BY StatusReasonDateTime DESC) AS prev

最后是 DDL：

CREATE TABLE [dbo].[StatusHistory](
[PK] [varchar](99) NOT NULL,
[ID] [int] NOT NULL,
[Number] [varchar](255) NULL,
[Status_ID] [int] NULL,
[Status_Name] [nvarchar](max) NOT NULL,
[StatusComment] [varchar](255) NULL,
[StatusReason_ID] [int] NULL,
[StatusReasonComment] [varchar](255) NULL,
[UserEnteredStatusDateTime] [datetime] NULL,
[ChangeDateTime] [datetime] NULL,
[ChangedBy_Id] [nvarchar](128) NOT NULL,
[ChangedBy_UserName] [nvarchar](256) NOT NULL,
[IsHeader] [bit] NOT NULL,
[dw_timestamp] [datetime] NOT NULL,
[EventTime] [datetime] NOT NULL,
[StatusReason_Code] [nvarchar](max) NULL,
[StatusReasonDateTime] [datetime] NULL,
[TransportMode_Name] [varchar](5) NULL,
[IMWBWagonMass] [decimal](18, 2) NULL,
[RoadFirstWeight] [decimal](18, 2) NULL,
[RoadSecondWeight] [decimal](18, 2) NULL,
[DraftSurveyTons] [decimal](18, 2) NULL,
[StatusHistory_key] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [XPK_StatusHistory] PRIMARY KEY CLUSTERED 
(
[StatusHistory_key] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]