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...