I have a Windows Server 2008 R2 server running MS SQL Server 2005 and SSRS. The server has 48 cores and 128GiB of memory.

The IIS worker processes and reportingservicesservice.exe never get over 0.05% out of 100.00% of a CPU.

The waits for responses are long; 30 seconds and more are common.

What should I do next to troubleshoot this issue?

  • I would suggest that you look to perfmon to get information about the usage of your system, paying particular attention to your storage sub system (You don't mention your disks in your original question). – ITHedgeHog Mar 23 '12 at 14:09
  • You need to give us more information to help you. You don't say where you are seeing slow response times, is it just when browsing pages, when you start to generate reports? How much data are you trying to pull from SQL into your reports? What performance monitoring have you done so far? – Sam Cogan Mar 23 '12 at 14:09
  • 3
    Actually TomTom, the only useful thing you tried to tell him was incorrect. A fully-loaded single core on this machine would show up as 2.08% of the total amount of CPU being used. – MikeyB Mar 23 '12 at 14:37
  • 3
    TomTom, yes his question was poorly worded. Yes, he seems to be a bit out of his league. We all start at the bottom. That doesn't make it appropriate to be abusive. It only reflects poorly on you I'm afraid. – John Homer Mar 23 '12 at 14:39
  • 2
    @TomTom, There's something to be said for brutal honesty, but you were being unreasonable. This guy is clearly inexperienced and needs some expert advise. He's come to the right place, and all you could offer is how wrong he is. If you need more information, please see our FAQ: serverfault.com/faq – Kyle Smith Mar 23 '12 at 14:41

I would first determine if SSRS is indeed only using one processor. This is simple enough using Process Monitor, Task Manager, or Performance Monitor. If it's only using one thread, I would research the documentation and see if there is anyway to scale this out. Unfortunately, I don't know the answer.

The other thing I would look at is SQL Server. Use SQL Profiler to analyze the queries being run by SSRS and see if that's a bottleneck. This is a fairly advanced procedure, so you'll need to hit the docs. There's plenty of info out there on how to analyze and performance tune queries, but this is somewhat of a black art that DBAs spend their whole careers perfecting.

You may be better off pulling in some professional consulting help if you're feeling daunted.

  • I am a DBA, I am fully equipped in black arts of DBAs. The queries typically run in under 2 seconds on sql server or msas. – jerry Mar 23 '12 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.