September 5, 2006 at 11:20 am
I have a server running SQL Server 2005 and SSAS - recently bumped it up to 6 gigs of memory, I have the /pae switch enabled, but not AWE yet. The error that was happening prior to the added memory (something stating a lack of memory, assumed with SSAS) no longer exists, but users are still complaining about poor performance...REAL bad even.
The thought was to look at moving the SSAS off the SQL Server onto its own box, but I cannot seem to find anything about this. I have run into this exact issue at a former employer, my understanding is this issue went away when the severe developing had completed.
Has anyone run into this? Any one have suggestion / fixes / things to look at?
Questions will be propmtly responded to
TIA
Cory
-- Cory
September 5, 2006 at 9:10 pm
Hi Cory,
Can you please provide a bit more data on your environment? is this a 32bit/64bit server? Where did you place the data folder for SSAS? What are exactly the performance issues you’re experiencing? Is it while users are querying the cube or during cube processing? How big is the cube, how many partitions?
Just a few more parameters will make this question more "answerable".
Regards,
Yaniv
September 6, 2006 at 7:03 am
Yaniv,
I would love to give more details.
32 bit W2K3
SSAS is quite likely a default install (done before I was employed here)
The performance issues we have seen: A user said he was trying to browse a cube (I believe in excel) and he said he needs to wait 10 min's between hitting "Page down". We believe this may be related to memory issues we have now and again. Recently we went from 4 gigs of ram to 8 gigs, and turned on the /PAE switch at the OS. This user said that after we rebooted the BSM Server (different server, houses the web side of MS's BSM, similar set up), everything appeared to run very well. Long story short, it appears the slow down is mostly when users query the cube. Processing does not seem to be that bad. We do re-populate our fact table every 5 min's.
As far as cube size, I am not sure exactly what you are looking for, so I hope I hit this:
| Rows:1113558 |
Dims: 16
Partitions: Currently 57, one for each month from 2002 - current, although right now, some partitions are not populated, because we are still testing.
Some stats that I thought might be of interest, we just rebooted 2 hours prior to these stats,
Service Memory usage as of 2 hours after reboot (Light usage)
SQLServer.exe 1,680,916 k
msmdsrv 779,652 k
devenv 60,432 k
SQLWB 51,646 k
I hope this helps!
-- Cory
September 6, 2006 at 5:19 pm
Cory,
From the looks of it, the cube you have is not that big at all, and from the stats you provide, the msmdsrv service does not even begin to utilize the amount of memory available.
This may be an issue with aggregation design. Did you set up the dimensions' hierarchies with the proper attribute relationships? Without proper attribute relationships the SSAS engine will not be able to design efficient aggregations, hence - poor performance will follow while browsing the cube.
Have a look at the following blog post, to make myself clear:
http://markiehillas.blogspot.com/2005/08/sql-server-2005-aggregation-confusion.html
Let me know if this helped you in any way!
Yaniv
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply