3

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
0

3 Answers 3

7

The Id column in your temporary table is unique, but you're not telling the optimizer about that.

Replace the existing nonclustered index on the temporary table with:

CREATE UNIQUE CLUSTERED INDEX CCI_#updates__Id
ON #updates ([Id]);

Note the index is UNIQUE and CLUSTERED.

This will remove the Hash Match Aggregate from the plan (choosing arbitrary row values per undeclared key). This aggregate was slow because it spilled to disk for lack of memory, but the main point is the aggregate was only needed because SQL Server couldn't be sure that one row from the HISTORY table would match at most one row from the temporary table. Adding a uniqueness guarantee to the temporary table resolves that problem and removes the aggregate.

Now add a FORCESEEK hint to the final update:

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   
    WITH (FORCESEEK) -- NEW!
    ON tgt.[Id] = src.[Id]
    AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] 
    AND tgt.[Hash]  <> src.[Hash]; 

You should get a plan with no sorts or hashing like this:

Expected plan

The Eager Table Spool is required for Halloween Protection because you're updating a clustering key (IsActive).

You may find this plan shape works best. You're not updating a ton of rows.

The original sort was introduced to present rows to the Clustered Index Update operator in key order. This helps produce a sequential access pattern rather than seeking into the clustered index for each update. The plan above relies on preserving that key order, so no sort is needed.


I know you said you're following some pattern or other, but many aspects of your script seem redundant, inefficient, or unsafe.

  1. The history table doesn't have a unique constraint on the identity column.
  2. The hash calculation could use CONCAT_WS.
  3. The hash calculation doesn't use style formats for date conversions.
  4. The HistoryRecordId column saved to the temporary table is never used.
  5. It's unclear if you're saving anything with the hashes over comparing columns directly.
  6. Your final update changes the cluster key column IsActive unconditionally, requiring Halloween Protection. You could look into not doing that, or only doing it when absolutely necessary, perhaps in a separate update. This all rather depends on what that column means and what your process guarantees.
0
5

cluster it

The main problem with your query plan is the use of a Batch Mode sort. The reason this drags you down so much is because unless they're a child operator of a Window Aggregate, all rows end up on a single thread:

NUTS

The reason your index on the #updates table is ineffective is because it doesn't get used. SQL Server doesn't want to do 2 million lookups to get all the requested columns that aren't part of the nonclustered index.

NUTS

You may have better luck creating a clustered index on the #updates table, which would order the data by the key columns, and include all of the other columns in the table.

CREATE CLUSTERED INDEX 
    NCCI_#updates__Kimble_HISTORY_ForecastStatus 
ON 
    #updates 
    ([Id], ActiveFromDateTime, [Hash])
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

However! You may still get a Batch Mode plan, and it will probably use a Hash Join, since that's the only join type that Batch Mode can use. Since Hash Joins don't preserve order (Merge and certain types of Nested Loops do), you would likely still end up with a Sort operator.

Your options would be to use OPTION(MERGE JOIN) to force that join type, or disable batch mode for the query with OPTION(USE HINT('DISALLOW_BATCH_MODE')).

0
2

The last query seems to be mostly waiting for BSORT (easiest to see in XML view): enter image description here

Almost 4 minutes spent waiting. This points to batch mode sort. And indeed the sort in this query is in batch mode:

enter image description here

Also of note is that CPU time is only 10 seconds, while Actual (wall clock) time is 46 seconds.

All those point to problem with the batch mode. This article suggests using trace flag 9358 and other workarounds. But it is for SQL Server 2016, so not sure how it works in 2019. I personally would try forcing query to run with lower comatibility level. Meaning adding OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')) at the end of the query. The batch mode on rowstore (which we see here) is available starting from compatibility level 150, so using a lower one should disable it.

As a side note the index [IX__HISTORY_IsActive] ON dbo.HISTORY seems redundant with the clustered index. It has same first key column and the other ones are included. I would consider dropping it. That would speed up any updates on the table and save space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.