As you point out, implicit conversions on table columns (example, table column is VARCHAR(), lookup or join value is NVARCHAR()) is really bad especially for blocking. That, of course, is also going to help a whole lot with your deadlocking problem.
That might not be the only issue though.
An "illegal DELETE" might be at fault. This is where you have a DELETE that uses a JOIN but the table being deleted from isn't explicitly included in the FROM Clause. This can (and it's not predictable) cause a massive Halloweening problem that (for example) can literally take hours to resolve when it "goes bad" where in normally only take a minute or two or even a second or two to normally run.
The other problem is in the order of the deletes and what your using as the lookup column to do the deletes. Yes... you can tell it to do things like delete all (for example) rows that have an inactive status that have a last modified date before a given date and it will usually work just fine. A better way (IMHO), is to find all the rows that meet that condition and create a temp table of Clustered Index keys and then use that to identify which rows in the real table to delete. That will also allow easy "chunking" of the rows if necessary. The big key (no pun intended) is to delete the rows in order by the Clustering Key.
As for your question of "Is there a way to find out the root cause of the blocking retrospectively?", the answer is probably not without setting up some form of monitoring or alerting but I could be wrong. The default trace probably won't contain that info just because of a rapid turn over (it's too small, IMHO, and can't change its size) and I don't know if Extended Events or the "Server Health" has that kind of information in it because I've simply never used either. Hopefully, someone knows the answer to that question and this post will serve as a "bump" as well.
is pronounced "ree-bar
" and is a "Modenism
" for R
ow.First step towards the paradigm shift of writing Set Based code:________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
Helpful Links:How to post code problemsHow to Post Performance ProblemsCreate a Tally Function (fnTally)