SSAS 2005: Slow Database Operation
I have an SSAS 2005 database with 10 cubes. When I create cubes from XMLA script, it is fast and I can view cubes from SSMS as I should.
After processing cubes at night, eveyrthing starts working VERY slowly. It takes a few minutes to open the list of cubes in SSMS, and that's all the time. What could be causing this?
source to share
This should be in response to Adam's comment.
If you are using SQL Server 2005 sp2 or higher you can try using the prealocate setting.
Be sure to read this white paper first to fully understand the implications of this: SSAS memory preallocation
source to share
Check server usage. A lot of times this can be due to the fact that your cubes caused a memory burst and it didn't release it. This will happen if your cubes are processing a lot of information during the night.
Try setting SSAS memory limits so that you reserve ~ 2GB of RAM for the OS. If you can't save 2GB for the OS, you'll need more RAM.
source to share