April 1, 2008 at 12:28 pm
Hi, all - in looking at blocking and waiting on my production server, occasionally I see numerous (100+) waits with wait_type = THREADPOOL. I can't find a definition of these anywhere, and while I suspect them to be a wait for an OS thread to be available to process a SQL Server worker thread, I can't confirm this.
What are they, and how do I resolve them?
Thanks in advance!
Will
But boss, why must the urgent always take precedence over the important?
April 2, 2008 at 2:04 pm
Anyone?
Seriously - this is a hard one to answer, isn't it? And it's hard to research, too!
To spur your thinking, here's more detail:
My database is a high transaction environment, and is tuned as such. However, our UI has some administrator functionality and occasionally sees some reporting activity. The reporting is fairly light, and for short time periods, and we've tuned for it pretty well.
However, with yesterday being the first business day after a month end and a quarter end, we saw some general slowness for the heart of the day (about four hours).
Memory was very good at the time - 99.8% Buffer Cache Hit ratio, page life expectancy in the 190000 second range (yes, two days!). We're well stocked in this department.
I/O did not seem to be an issue, either. Our infrastructure team noted very little I/O activity as always, thanks to plenty of RAM, no doubt!
The only thing I could correlate this to THREADPOOL waits of 100ms to 400ms. There were occasional CXPACKET waits, easily correlated to queries running in parallel at the time.
So after 24 hours, my theory is this:
1 - The increase in parallel queries consumes more threads.
2 - SQL Server allocates more threads from Windows.
3 - However, the parallel queries all execute fairly quickly. This keeps SQL Server from reacting strongly enough to allocate enough threads to effectively combat the slowdown.
4 - A less than ideal equilibrium is reached where a lot of queries wait a little extra time to execute.
5 - Our web servers have an increase in busy threads as they wait for DB results to continue.
And for four hours, the IT staff nervously discusses many courses of action while the end users experience a slow, but still fully functional web site.
Ideas, anyone? Am I full of conjecture, half truths, lies, and statistics?
Or am I on to something?
But boss, why must the urgent always take precedence over the important?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply