Em um banco de dados SQL do Azure (compatibilidade com SQL2019), tenho um processo ETL que preenche tabelas HISTORY em um padrão DeltaTrack.
No Proc, há um UPDATE na tabela HISTORY que o mecanismo de consulta está usando um SORT, mas tenho um índice que deve cobri-lo.
O caso de uso para este UPDATE é para linhas existentes onde adicionamos colunas adicionais à ingestão desde que a linha foi adicionada pela primeira vez à tabela HISTORY.
Este SORT está fazendo com que os Procs em que as atualizações estão em nossas tabelas maiores/mais largas sejam dolorosamente lentos.
Como ajusto o índice ou consulta para remover o SORT na consulta 3 ?
Aqui está o plano de execução atualizado conforme solicitado por JD
Aqui está o 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
