2

I have one of those weird questions, those ones that are nearly unsolvable (the only way to really solve them is to throw out the technology and buy new ones). I've had a number of experts (about 10 consultants) in to come and troubleshoot my issue - with no solution - hence my reason to post it here. [Please do not shoot me because of the way it has been setup by the so-called-experts!! I'm busy redesigning it in 64-bit :) ]

The issue I have is the following:

Environment:
Windows Server 2003 x86 Enterprise (32-bit)
16 GB RAM
Fiber Channel 4GB EMC SAN Storage (500GB Partitions, OS split from Data)
SQL Server 2000 SP4 x86 Enterprise (32-bit)
SAP BusinessObjects XI 3.1 (Java, Apache, Tomcat)
"business databases, with universes and business reports"
McAfee Anti-Virus Enterprise

Config:
/PAE /3GB = boot.ini
SQL = Use AWE, Minmem = 4gb, Maxmem = 12gb
Latest patches loaded, including the one where SQL address more memory
sqlserver.exe is displaying 130,123 kb in Task Manager (red lights!!)

Issue: I can pull the server down, using all cores, maximum out to 100% (see screenshot), by pulling one report. Currently we have about 200 users connecting by means of various client-database programs, as well as BO reports.

I picked up that in ProcessExplorer the Interupts on Hardware Interrupts are 80% when this query run. I also picked up that SQL Server is showing the sysprocesses that is in a running state to have PAGELATCH_SH

I have tried "Google" for the last two weeks with little results. I personally think that the underlying database and queries BO produces (Universes built on relational structures) are causing this huge amount of datasets that's transferred from SQL to BO. The BO report is a one pager.

Screenshot: Terminal Sessions Screenshot (2560x1440 resolution)

I would be ever greatful for the person who could give some valuable information why this is happening. We've had this issue for the last 3 months now, and the databases just keep on increasing in size.

Thank you

Riaan
  • 145
  • 1
  • 1
  • 6

1 Answers1

2

sqlserver.exe is displaying 130,123 kb in Task Manager (red lights!!)

That's normal with x86 machines using AWE. Use Perfmon to look at how much memory SQL Server is actually using.

Look at the execution plan for the query that BO is running and see if indexes need to be added to tables to improve performance.

When the CPU is running at 100% what's the kernel CPU % running at (numbers from task manager are fine)?

I just saw the screenshot, when Windows is using 80-90% of the CPU time for kernel memory, that means that it's spending all its time managing the extended memory.

mrdenny
  • 27,174
  • 4
  • 41
  • 69