Recently an SSIS developer can to see me with a deadlock issue. People rarely come to see me for social reasons.
What immediately catched my attention was the “Exchange Event” process in the deadlock graph along with the deadlock chain argument: “Parallel query worker thread was involved in a deadlock”.
After some investigation parallelism was defined as the culprit so the MAXDOP 1 was my first bullet on the list. Off we were looking for the update statement that caused the deadlocks. The Update took place on a 700.000 record table updating exactly 1 record. So why would SQL Server use parallelism for this? More, the deadlock graph also pointed out that locks were all page locks.
Correct, the update was performing a table scan for the simple reason that there was no index on the table.
Creating a clustered index on the SARG of the update statement (by chance an identity-kind-of-column) solved the deadlock issue.