0

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?

MikeyB
  • 39,291
  • 10
  • 105
  • 189
jerry
  • 111
  • 1
  • 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
  • My answer was deleted. Well, Contact ChrisS if you want to know what you are doing wrong - I wont argue with someone obviosusly thinks poloitical correctness beats being factually correct. Well, the new way - as long as you are freindly, who cares if you are right. – TomTom Mar 23 '12 at 14:31
  • 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: http://serverfault.com/faq – Kyle Smith Mar 23 '12 at 14:41
  • The queries are easy 1 and 2 second queries. The disk-io is fine, I'm a DBA so I know the queries/sql server are not the bottle neck. The data I'm pulling back is typically 100 to 1000 cells worth. – jerry Mar 23 '12 at 18:24
  • btw, in configuring iis, and http, I'll freely admit that it is not my area of expertise. As I stated in the original question, I doubt I can even ask the question in the 'right way'. I normally work as a DBA. There I normally look for some hardware limit, and try to work around that limit. Here, it is clearly not a hardware limit. – jerry Mar 23 '12 at 18:27

1 Answers1

2

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.

John Homer
  • 1,313
  • 1
  • 10
  • 10
  • 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