I got to work with deadlocks quite a bit recently. There were quite a few interesting ones that came up that I had the chance to research. Since I like easy, I’ll start with the one that I forgot to grab the deadlock details for.
Well, maybe not all of the details… in this case as the title states I was looking at deadlocks with the events exchangeEvent and threadpool. I managed to come across a post from Bart Duncan that went through and deciphered this deadlock. The long and the short of it… parallelism deadlocks.
Bart does a better job explaining this than I can do here, especially since I didn’t take the time to grab the deadlock details for review. Maybe I’ll have that one the next one…
Fortunately, a large part of the issue that I was reviewing for the client had to do with parallelism and so solving this issue actually occurred as a side effect of dealing with parallelism issues. But I will share my little secret that I used to resolve this and most of the parallelism…
There I said it. True, you can have too many indexes. But no indexes is too few. No clustered indexes can lead to too many scans. I could pulpit here on indexes and making sure that you have them, but I’ll save that for another time.
Overall, I used Bart’s Workaround #1. Hopefully this helps… direct you to a post that is more prescriptive.