As written in my question, I have a problem with my execution plan as you can see in the title, a group of queries that was taking around 30-40 seconds to complete now have incorporated this section in the execution plan:
While the execution plan for that part was like this before this morning:
Do you know what might have caused this when none of the involved tables seem to have changed substantially?
Some more detailed context: at work we have different .csv from different suppliers which contains all the products they can sell to us with all the data we might need.
Since we very frequently use only a fraction of the data (ProductName, BrandId, Price, Availability), and each supplier has his data formatted differently, we push the data from the .csv into our SQL Server database and then we merge data from these sub tables (ranging from 5k to 200k rows) to our master table (around 1500k rows).
The simplified query is very similar to this for all our procedures:
We have a first part selecting from the subtables the data to get a common structure I can actually load to my db. Some filters might be applied in this phase.
WITH query(Supplier, ProductCode, Brand, Price, RefreshDate, Quantity) AS
(
SELECT
'SUPPLIERANAME' AS Supplier,
supplierData.ProductCode AS ProductCode,
catalogo_mapping_produttori.nome AS Brand,
supplierData.Price AS Price,
GETDATE() AS RefreshDate,
supplierData.Quantity AS Quantity
FROM
SupplierA_Table AS supplierData
)
Then I simply merge this data to my master table:
MERGE Catalogo_Offerte AS T
USING query AS S
ON (T.ProductCode = S.ProductCode AND T.Brand = S.Brand AND T.Supplier = S.Supplier)
WHEN MATCHED
THEN UPDATE
SET
T.Price = S.Price,
T.RefreshDate = S.RefreshDate,
T.Quantity = S.Quantity
WHEN NOT MATCHED BY TARGET
THEN INSERT(Supplier, ProductCode, Brand, Price, RefreshDate, Quantity)
VALUES(S.Supplier, S.ProductCode, S.Brand, S.Price, S.RefreshDate, S.Quantity)
-- We allow some tolerance before deleting
WHEN NOT MATCHED BY SOURCE AND T.RefreshDate <= DATEADD(dd, -7, GETDATE())
THEN DELETE;
I can edit with more data as needed, and thanks in advance to all who take their time answering and discussing the question.
Edit #1: the query plan, sadly is the plan after we had to restore the db for the second time so it doesn't present the problem sadly: https://www.brentozar.com/pastetheplan/?id=SJqOL2lTz
About indexes: the master table works on (ProductCode, Brand, Supplier) as unique index, we also have an identity key index that doesn't appear in the table and that's the one where the spool seems to happen now that I notice.
As for the sub tables they have different indexes, some are set up to have kind of an autoincrementing key set by the supplier while others have the perfect key for us (brand, productCode). Mostly we get these two cases, obviously in the first case we have to normalize the data to make it compatible with our system.
