w WARN: Thread pool pressure. Using current thread for a work item.
The skill set needed by a DBA can be quite diverse at times and a run in with SSRS 2005 probably illustrates the point quite well. I don't have skills in IIS although I was responsible for the design and deployment of an online mortage application site some years ago.I had to get hands on with IIS5, firewalls, intrusion systems, ISA Server, dmzs, NAT, IP and lots of other acronyms so I have an understanding of these things but never had to do anything other than set up and configure IIS - no tuning.
So back to SSRS 2005, it uses IIS and we have a pair of servers which can serve up over 120k reports a day, and I don't mean a simple one page reports here, this is a billing system. The SSRS servers are x64 and have a substantial amount of grunt.
As is often the case a maybe slightly unrelated production issue forced a review of the servers, the SQL side was fine but my colleague discovered a large number of the warnings about thread pool pressure.
" “w3wp!runningjobs!11!4/26/2010-11:16:37:: w WARN: Thread pool pressure. Using current thread for a work item.”
Some searches brought back some information suggesting increasing the number of worker threads but other than a description of what the warning meant not much else.
There was one post which suggested one process per socket, that would equate to 4 on our servers, but that didn't seem much change especially as one of our servers was set to 1 and the other to 5.
I suggested we should set this to at least 1 per core which would give us 16 and then monitor carefully. My colleague had done some research about queues and put up some performance counters to watch this alongside process explorer to actually see how many processes fired up.
Fortunately I have extensive monitoring on the SSRS Servers so I can see a vast number of real time stats - also displayed in SSRS, but that's another post coming eventually - along with the historical data for the last couple of years, granularity is 5 secs and 5 minutes depending on the data set.
So what happened?
The immediate reaction was that 16 w3wp processes started up and stayed active with little impact on cpu or memory.
The warnings went away, so that was good and our reports output was now good, previously we'd been having timeouts ( Essentially we raise invoices as pdf documents, at peak times this is the bulk of the output )
Performance appears considerably enhanced, the number of active connections from SSRS to the production server has quadrupled, viewing the collected perfmon counters and comparing to previous periods we can see we now have throughput and utilisation at +400% .
Execution times would also appear to reduced,most reports completing within a second.
The Question?
Nowhere could we find any recommendations on how to calculate how many worker threads might be required - answers on a postcode to the usual address.......
It doesn't seem that any SSRS books cover IIS from the view of performance tuning.
SSRS 2008 does away with IIS, and yes we're planning an upgrade but it will take time, where do we start with performance tuning the engine in SSRS 2008, I believe some part of the IIS engine is still present under the hood - you'll have to excuse my lack of knowledge here, I've installed and used SSRS 2008 but not been forced to apply any tuning.
Footnote:
The path to performance tuning SSRS was to scale out, separate the parts, but with minimal resource utilisation on our combined boxes this seemed somewhat strange, even now my x64 box only averages 10% cpu and is using barely 50% of available memory, in fact I can't find any signs of straining resource - makes me wonder.
I'd be very grateful of any feedback directing me to what I've been unable to find and advice on tuning the IIS behind SSRS.
References:
There was one kb but this was a way back and we're a service pack and number of CUs forward.
The best explanation probably comes here http://performancepointblog.com/2008/06/sql-reporting-services-what-does-that-thread-pool-pressure-warning-mean/