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:
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
/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.