SQL Server Performance Forum – Threads Archive
slow cube process
We have a development Analysis Services server. Windows 2003 Server, v2000 Analysis Services + SP3a. It’s a PC only, with 1 IDE disk and a 2GHz processor and 1GB of RAM. Not really a beast, but it should be doing better than it is. We’re trying to test processing our cubes on it. These take 2 to 3 hours on the live server which frankly is getting a bit long in the tooth and isn’t spec’d very highly either. However on this development server they are taking an eternity (and this is a recurring problem, not a one-off, but is Windows 2000 as opposed to Windows 2003 – not that I think that matters – they were still very slow before I rebuilt it with Windows 2003.) Processing cubes on the development PC server is taking way too long. Any ideas, anyone? Tom PullenDBA, Oxfam GB
Are any other application processes running during Cube processing? What Storage Mode are you applying? Do you have any aggregations set, if so what percentage level did you set? Aggregations cause serious delay in processing as you may know already. Having only one physical disk on your box could also slow thing down. If you can upgrade to a physical RAID array it would really help, consider using a RAID 0 on your dev box. Remember though to run routine back ups if your development data is important (which Im assuming is) since RAID 0 does not offer any fault tolerance. You can test out the different setting in the envirements tab in Analysis services if you box is just dedicated to Analsis Services set the Minimum allocated memory to about 90%, this one is very important "Process buffer size" increasing the size will boost processing performance the defualt is 32MB, you can increment these amounts to test performamce. Also use System Monitor to record any potential bottlenecks, memory, cpu, I/O etc. As far as your production Cubes I would consider partitions if you feel the processing times are going to get worst. Hope this helps Raulie
hp
Thanks very much for the reply, Raulie. Sadly there’s no chance of RAID or additional disks, and I am going to be replacing this server shortly with a proper server with multiple disks, more RAM, 4 processors etc. I don’t really know how high to set the Process Buffer? It’s 32MB currently, not enough I suppose? Backups are not needed because at any time this data can be recreated from live (although I do do backups manually when needed). There are no other applications running on it, the Repository has been migrated to SQL Server on another box. The level of aggregations is the same as the live data, can’t remember the exact level but it isn’t excessive.. and the live cubes process fine. How do you set minimum allocated memory? Is it a registry key? TIA Tom Pullen
DBA, Oxfam GB
P.S., storage mode is MOLAP. Tom Pullen
DBA, Oxfam GB
Ok thanks very much, I’ll try it. Tom Pullen
DBA, Oxfam GB
Go to properties environments tab in analysis services to change Minimum allocated memory. Increment the process buffer to about 256 MB, test, test, test.
Raulie
hp
]]>