Getting a SORT operator when I have an index

3 votes
3 answers
504 views
sql-server query-performance execution-plan azure-sql-database sort-operator
                          On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern.

In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a SORT but I have an index that should cover it.  

The use case for this UPDATE is for existing rows where we've added additional columns to the ingest since the row was first added to the HISTORY table.

This SORT is resulting in the Procs where the updates are on our larger/wider tables to be painfully slow.

How do I adjust the index or query to remove the **SORT** in **query 3**?

Here's the **updated** execution plan  as requested by J.D.

Here's the 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




                        
Asked by Geezer (513 rep)
Sep 11, 2023, 08:26 AM
Last activity: Sep 28, 2023, 09:59 AM
Getting a SORT operator when I have an index

Related Questions
