I’ve been hacking away at a process we run at work where we generate files through a series of store procedures.
When the process started a couple days ago, it was taking far too long. There have been no changes to the systems or procedures used in the process (or at least no one is fessing up), so it required a bit of troubleshooting.
I started checking the status of the processes running (you can use sp_whoX or whatever you need), and I saw that the process in question was almost always in a suspended state. It would also stay in this suspended state for a LONG time. From there I started checking on blocking. I found that the process was indeed being blocked…by its own session id. Pretty sweet huh.
Through various other troubleshooting, I observed an extremely high CXPACKET use. Googling, and incantations to summon SQL Server Demons from Hell, I was able to come to the conclusion that parallelism was an issue.
MAX DOP was set to 0, and this was not working at all. I changed MAX DOP to 4 (8 cores / 2) and it got a little better, not by much, but enough that people were optimistic. I finally disabled parallelism completely with MAX DOP 1 because I noticed that the processes that were being blocked were also generating multiple threads. I was not able to observe any kind of resource usage outside of a high cpu utilization.
MAX DOP at 1 instantly fixed the issue.
So, if you ever find yourself wondering why SQL seems fine, but some procedures are sucking, check your MAX DOP and consider whether parallelism is hurting your system or making it totally sweet. In this case it was not sweet, but in fact…sucked.