MERGE statement produces delete/insert's on update

Question

question

alepoy asked Mar 3, '21

MERGE statement produces delete/insert's on update

Hi,

I have this issue with a Merge going on and I am really stuck and got out of clues.

I have a stored procedure that merges data from a staging table to its production companion.

The target table contains many records (a couple of milion), and it is succintly defined like this:

Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
, Discriminator TINYINT
, ProductId TINYINT
, Timestamp DATETIME
[more fields]

A Unique Key defined on the Discriminator and ProductId fields, and it is used in the ON statement of the merge (although tests using the Id PK have been done as well, with same issue).

ProductId is a foreign key to a master table.

Some more info:

The merge does not modify any of the fields that are parte of the unique key (nor the primary key of course).
The merge is quite a classic one: inserts when not matched by source, updates when matched and the two timestamps are different, deletes when not mached by target.
When needed, almost all the fields of the record are updated.
The target table is replicated (but I get the same behaviour without replication, this has been tested).
The source and target tables belong to two different databases (on the same instance).

Now, the issue I'm getting is that when the merge meets the update condition it produces insert/delete's instead of a single update.

The only way to avoid this, so far, was to drop the delete section from the merge statement (thus, leaving it with just the insert and update logic) and run the delete's as another merge (or simply a delete): executing the merge without deleting records produces normal updates.

Another test that proved to produce the updates instead of delete/insert has been to rebuild the clustered index and/or set the fill factor to a empirical value of 70/80: in this case I think that it's just a matter of time before the behaviour gets back.

My guess was initially on Halloween Protection (the execution plan has more than one eager spool's actually) but the updates are very simple and straight...

Another guess is that this behaviour has something to do with page allocation and low level storage, but I have no real ideas why this is happening.

Since my target table is replicated this behaviour produced a lot of side effects, we activated CDC and detected the issue, but this problem could be affecting more tables and I really need to understand why...

Any ideas?

Thank you!

Alessandro

update merge hierarchy

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ · Mar 03, 2021 at 04:51 PM

can you post the actual merge statement?

0 ·

Kev Riley ♦♦ · Mar 05, 2021 at 02:26 PM

and when you say it 'produces inserts/deletes' what do you mean by that? where are you seeing these?

0 ·

Show more comments

Answer 1 · 2021-03-03T17:56:26Z

alepoy answered Mar 3, '21

Hi @Kev Riley

Use [Stage]
GO

MERGE  [Prod].[dbo].[ProductDetail] AS [Target]
USING (
SELECT 
  plg.Discriminator,
  pm.Id AS [ProductId],
  [More fields...]
  TS_StockLastModified,
  TS_LastModified,
  TS_Inserted,
  TS_SourceLastModified,
FROM [dbo].[ProductDetail] AS [plg]
INNER JOIN [dbo].[ProductIds] AS [pm] ON pm.Code = plg.Code AND ...
WHERE ...
) AS [Source]
ON (
[Target].[Discriminator] = [Source].[Discriminator]
AND [Target].[ProductId] = [Source].[ProductId]
)

WHEN MATCHED AND (
[Source].[TS_LastModified] <> [Target].[TS_SourceLastModified]) THEN
UPDATE SET 
  < [Target].*All-The-Fields-ExceptOnCondition* = [Source].*All-The-Fields-ExceptOnCondition* >
 [Target].[TS_LastModified] = GETDATE(),
 [Target].[TS_SourceLastModified] = [Source].[TS_LastModified]

WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Discriminator],
[ProductId],
[...])
VALUES (
[Source].[Discriminator],
[Source].[ProductId],
...
GETDATE(),
GETDATE(),
GETDATE(),
[Source].[TS_LastModified])

WHEN NOT MATCHED BY SOURCE THEN DELETE
;

The joined table in the source is used to get the referenced product id in the master table, since in staging we have to work with natural keys coming from an ERP.

Thanks
a.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2021-03-04T08:35:08Z

alepoy answered Mar 4, '21

Just a couple of facts more:

sql server is a 2016 Standard edition (sp2 cu14)
even though when I wrote "execute merge without deletes" I meant the merge statement is written without the "when not matched by target" section, it must be said that, when troubleshooting, the state of the staging table was modified before executing the merge just in order to update records (by changing one field and the timestamp); but the merge statement did act "wrong" with the delete section even though there were no records to actually delete: this made mi think of some query optimizer logic that gets enabled by the presence of either *potential* update and deletes

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2021-03-05T15:31:09Z

Kev Riley answered Mar 5, '21

Related to Bounded Updates?

https://repltalk.com/2011/10/04/sql-server-transaction-replication-bounded-update-or-why-my-update-was-transformed-into-an-deleteinsert-pair/

also https://docs.microsoft.com/en-US/troubleshoot/sql/replication/update-statements-replicated-as-delete-insert

I know that talks about replication, but CDC uses a log capture process that is similar.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question