I've implemented a covering index to avoid a key lookup:
CREATE INDEX IX_StatusHistory_Covering ON StatusHistory(ID)
INCLUDE (Status_ID, StatusComment, StatusReason_ID,
StatusReasonComment, UserEnteredStatusDateTime,
ChangeDateTime, ChangedBy_UserName, IMWBWagonMass)
WITH (ONLINE= ON)
But the key lookup is still happening.
Here is the query causing this:
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
The FullStatusHistoryLine view is:
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
And finally the 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]
Best Answer
Have a look at the Seek Predicate on
StatusHistory:You have to add TransportMode_Name and IsHeader columns to your new index so that it will really become covering: