SQL Server Cube processing performance degrades over time

Question

Hi All,

We have a server with SQL Server 2005 (DB + SSAS + SSRS + SSIS), and we have a cube being processing completely every night. We have noticed that the cube processing time is about 2 hours the day of the reboot, but then the performance seems to degrade over time to 4.5-5 hours. We are unable to identify the reason for this. Any help is really appreciated. 

Machine configuration:

System is a Dell 2950 server w/ 32gigs, two quad-core 2.33ghz E5345 CPUs, internal PERC5/i RAID controller plus external RAID box attached via Adaptec SCSI controller installed by Dell.

Software is Windows Server 2003 Standard x64 R2 SP2, and SqlServer 2005 standard edition x64 w/ SP2 (rev shows as 9.0.3073)

Nothing else usually runs on that box but SqlServer.   Sqlservr.exe is currently using up 29gigs of RAM

Thanks in advance!

---

Sandeep

Answer 1

Hi Sandeep,

SIince your question is SSAS specific you can ask your question on SQL Server Analysis Services forum.


Regards

---

Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)

Answer 2

Sandeep,

This would be rather hard to address specific cause but i suppose that you have properly done partitions and aggregations and
Other optimization technique to your cube.
Also, Is this full processing mode or Incremental processing option.

Those are thoughts comes in mind right now.
Thanks

---

Dibyant S U padhyay