0

we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.

Our setup is like this: A big data warehouse table gets data added to it every night. This is done through an SSIS Package, and in parallel. The data can be separated by column "country", and we run the same package for several countries at the same time to speed up things. One package alone simply does not use all resources of the machine.

With a non-partitioned table, we saw deadlocks once every few weeks. Not too bad, but too often to ignore.

We decided to partion the table by country, hoping to get rid of the deadlocks. I followed what I think is best practice, with an empty partition to the left and right, and one partition for each country. The country is encoded in an NVARCHAR(3) datatype. We recreated the clustered index using the partitioned column, and finally set lock escalation for the table to auto.

I can verify that all partitions are loaded with data belonging to it only, so all seems to be working fine.

However, we still keep getting deadlocks. I would have expected that my "write operation" (quotes will be explained next) would each focus on just one partition, and the parallel running SSIS packages could not interfere.

First question: Am I right in this expectation? Should I not be getting deadlocks now?

I think I have an answer to why they still appear: the "write operation" mentioned in quotes is somewhat complicated, it is an own SSIS component written in c# before my time with my employer. Each package creates and writes ALL DATA into it's own temp table, and then executes a

MERGE WHEN MATCHED UPDATE WHEN NOT MATCHED INSERT

from it's temp to the final table. When building this MERGE-Statement, the component uses a construction to compare NULLs, i.e.

WHERE @given IS NULL and col is NULL OR @given is not null and col = @given

and it does this also for the country-column, which is part of the clustered index and partition key.

I suspect, that by having a query relating NULL to the partition key, I trigger a "search and lock every partition" operation. Is this the case, does something like this exist? Or does the description point to another reason why we still get deadlocks?

Thanks a lot for reading and your suggestions!

Improve this question
2
  • 3
    Add the deadlock XML to your question. Deadlocks are captured by the system_health XE trace so recent ones can be extracted from the file target using the query in this answer. Adding OPTION(RECOMPILE) to the MERGE query may help optimize the query by evaluating the actual @given value at compile time, possibly avoid deadlocks with better plans for both the NULL and NOT NULL cases.
    – Dan Guzman
    Sep 3, 2019 at 9:59
  • And your table DDL, including all indexes.
    – David Browne - Microsoft
    Sep 3, 2019 at 13:40

1 Answer 1

Reset to default
1

There are a number of "features" of merge statements that can lead to issues like this. For a relatively comprehensive list of issues take a look at the following link:

Use Caution with SQL Server's MERGE Statement

I've seen similar issues occurring myself, the solution was either to re-write the code to avoid MERGE altogether or in once case where it was unavoidable, dropping indexes on the target table before the MERGE and then restoring them afterwards resolved the issue.

Improve this answer
1
  • Thanks a lot for that link. Frightening!
    – Ralf
    Sep 4, 2019 at 12:42

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.