Last few days I played with the Process Buffer size (in Analysis Manager, check the server properties, Processing Tab). This option, besides many other performance tips, is described in excellent article Microsoft SQL Server 2000 Analysis Services Performance Guide by Carl Rabeler, Len Wyatt and Dave Wickert.

After few tests, I got the best performance when I changed the default value of 32 MB to 256 MB. I was really surprised by the processing time improvement. Amazingly, the processing time (full process) for a quite large cube went down from 3 hours 58 minutes to 1 hour 14 minutes! Really amazing – processing is now more than three times faster by just adjusting a single option. After this experience, I would suggest to everyone who is using OLAP cubes in production to start testing the process buffer size, especially if you are close to the limit of the time window available for processing. The best actual size might vary depending on your HW and cubes, but you sure want to increase the default value.

Dejan Sarka