当我有索引时获取 SORT 运算符

在 Azure SQL 数据库（SQL2019 兼容）上，我有一个 ETL 进程，它以 DeltaTrack 模式填充 HISTORY 表。

在 Proc 中，有一个对 HISTORY 表的更新，查询引擎正在使用 SORT，但我有一个应该覆盖它的索引。

此 UPDATE 的用例是针对现有行，自从该行首次添加到 HISTORY 表以来，我们已向摄取添加了额外的列。

这种排序会导致我们更大/更宽的表上的更新速度极其缓慢。

如何调整索引或查询以删除查询 3中的排序？

这是根据京东要求更新的 执行计划

这是 DDL。

DROP TABLE IF EXISTS dbo.STAGE;
GO
CREATE TABLE dbo.STAGE
(
    Id varchar(18) NULL,
    CreatedDate varchar(4000) NULL,
    LastModifiedDate varchar(4000) NULL,
    LastReferencedDate varchar(4000) NULL,
    [Name] varchar(4000) NULL,
    OwnerId varchar(4000) NULL,
    SystemTimestamp datetime2(7) NULL
)
GO

DROP TABLE IF EXISTS dbo.HISTORY;
GO
CREATE TABLE dbo.HISTORY
(
    HistoryRecordId int IDENTITY(1,1) NOT NULL,
    [Hash] binary(64) NOT NULL,
    [IsActive]  BIT NOT NULL ,
    ActiveFromDateTime datetime2(7) NOT NULL,
    ActiveToDateTime datetime2(7) NOT NULL,
    Id varchar(18) NOT NULL,
    CreatedDate datetime2(7) NULL,
    LastModifiedDate datetime2(7) NULL,
    LastReferencedDate datetime2(7) NULL,
    [Name] varchar(80) NULL,
    OwnerId varchar(18) NULL,
    SystemTimestamp datetime2(7) NULL
) 
GO
CREATE UNIQUE CLUSTERED INDEX [CL__HISTORY] ON dbo.HISTORY
(
    Id , 
    [ActiveToDateTime] ASC,
    [IsActive] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX__HISTORY_IsActive] ON dbo.HISTORY
(
    [Id] ASC
)
INCLUDE([IsActive],[ActiveToDateTime]) 
GO

DROP TABLE IF EXISTS #updates;
GO


WITH src AS (
  SELECT 
    CONVERT(VARCHAR(18), t.[Id]) AS [Id]
  , CONVERT(DATETIME2, t.[CreatedDate]) AS [CreatedDate]
  , CONVERT(DATETIME2, t.[LastModifiedDate]) AS [LastModifiedDate]
  , CONVERT(DATETIME2, t.[LastReferencedDate]) AS [LastReferencedDate]
  , CONVERT(VARCHAR(80), t.[Name]) AS [Name]
  , CONVERT(VARCHAR(18), t.[OwnerId]) AS [OwnerId]
  , CONVERT(DATETIME2, t.SystemTimestamp) AS SystemTimestamp
  , dgst.[Hash]
  , CONVERT(DATETIME2, SystemTimestamp) AS [ActiveFromDateTime]
  , RN = ROW_NUMBER() OVER ( 
            PARTITION BY 
                t.[Id] 
                ORDER BY CONVERT(DATETIME2, SystemTimestamp) DESC
        ) 
  FROM dbo.STAGE t
    OUTER APPLY (
      SELECT 
        CAST(HASHBYTES('SHA2_256',
          COALESCE(CAST([CreatedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastModifiedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastReferencedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([Name] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([OwnerId] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST(SystemTimestamp AS NVARCHAR(4000)), N'')
        ) AS BINARY(64)) AS [Hash]
      ) dgst
), tgt AS (
  SELECT *
  FROM dbo.HISTORY t
  WHERE t.[ActiveToDateTime] > GETUTCDATE()
  AND 1 = 1  
)
SELECT 
  tgt.HistoryRecordId
, src.*
INTO #updates
FROM src
  LEFT JOIN tgt 
    ON tgt.[Id] = src.[Id] WHERE src.RN = 1;  
GO

--Create index on temp table (#updates) 
CREATE NONCLUSTERED INDEX NCCI_#updates__Kimble_HISTORY_ForecastStatus 
    ON #updates ( [Id] , ActiveFromDateTime, [Hash] );
GO  


    UPDATE  tgt 
    SET
      tgt.[Hash]        = src.[Hash] 
    , tgt.IsActive      = 1
    , tgt.[CreatedDate] = src.[CreatedDate]
    , tgt.[LastModifiedDate]    = src.[LastModifiedDate]
    , tgt.[LastReferencedDate]  = src.[LastReferencedDate]
    , tgt.[Name]            = src.[Name]
    , tgt.[OwnerId]         = src.[OwnerId]
    , tgt.SystemTimestamp   = src.SystemTimestamp
    FROM dbo.HISTORY tgt
      INNER JOIN #updates src   
            ON tgt.[Id] = src.[Id]
            AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] 
            AND tgt.[Hash]  <> src.[Hash] ; 
GO