Poor performance: SQL Server 2000 & Analysis Services with 20% aggregations

I would really appreaciate some advice:

We use SQL 2000 and want to move new hardware but we're experiecing performance problems on the new hardware compared to the old hardware.

Old hardware spec: Entry level intel server, 64b Pentium, 4GB RAM, 1TB Raid 5 SATA , 64 bit Windows 2003

New hardware:HP Intel Zeon Server 3.4 GhZ * 2, 64bit Windows 2003, 6GB RAM, Fibre Optic SAN - 1TG Raid 5

Software on both the same:SQL Server 2000, sp4, 32bit

Data volumes: 15GB DB, 12Cubes, 4virtual, 20dimensions-biggest leave member count of 350000

Problem: Huge performance difference between the entry level server and the new hardware. Huge improvement in performance on the new hardware when populating the DB and running the DTS packages, however, the munite we try and use aggregation the servers comes to an halt. The design of the DB is not our concern as it has been tried and tested many times over and has always given us realatively good performance.

Building the cubes with No aggregation: 4h on new. Then using 20% Aggregation: Indefinately on new vs 19h on old

Hi Jeanne,

check the AS-Properties in the AS-Manager-Tab > Process-Tab

Read-Ahead-Buffer should be 16 MB (or more?)

Process-Buffer-Size is on my machine 2000 MB (!)

Under Environment-Tab

Max Threads: 8

Minimum reserved Ram: 1024

My machine:

2 Xeon 3,6 GHz Hyperthreading, 4 GB RAM, Windows 2003 Enterprise Sp1, SQL 2000 SP4 32Bit (I am working in a governmental institution and MS Germany installed this box).

check this link: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx#EJEAE (Appendix D)

hth

Bernhard