We are trying to troubleshoot a problem that seems to be the result of too many threads being allocated to queries. We came to this conclusion after seeing the THREADPOOL (poison) wait becoming more and more prominent as a SQL Server wait stat.
What is the actual problem that you're trying to troubleshoot? Is it performance or something else? I ask because I've found that things like "the THREADPOOL (poison) wait becoming more and more prominent as a SQL Server wait stat" is a symptom of a larger problem and trying to fix the symptom rather than the cause is usually a futile cause that sometimes causes more harm than good.
Hi Jeff,
the problem I am trying to troubleshoot is concerned with a dashboard that displays reliability statistics over a given period. Unfortunately it displays a red light as soon as a poison wait threshold is reached and in this case the wait was THREADPOOL.
Although performance is ok, we are beginning to express concern that as the environment grows, inefficient SQL will compound the problem. In end effect I am trying to proactively anticipate a potential problem and resolve it before I have to post an emergency HEEEELLLLPPPPP here!
From what Uwe has explained, reducing the size of the execution plan (at least the expensive components that need to be parallelised) might help. The next step from my side is to identify the most expensive plans and try to optimise them. Because the MAXDOP is only going to be applied to threads that need to be parallelised I am going to suggest raising the threshold of parallelism and reduce the MAXDOP.
It could be interesting....
Regards,
Kev
Heh... that's one of the things I don't like about a lot of dashboards. It's like a car... the check-engine light comes on for something as trivial as the gas cap being a bit loose or worn.
Not that it matters to anyone but my poor man's version of "Resource Governor" is to set system wide MAXDOP to no more that 1/4 of the total CPUs and never more than 8. If someone needs more than 8 CPUs for their junk to run, then they need to have a close up experience with the 3 banded pork chop launcher. 😉