Solve deadlocks by creating an index

· Uncategorized
Authors

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: