Wednesday, May 13, 2009

How to Tune the Process Buffer Size for SQL Server Analysis Server

Perform the following steps to tune the process buffer size on an Analysis server:
If you have 4 gigabytes (GB) or more of physical memory on the computer, you are running Microsoft Windows Advanced Server or Windows Datacenter Server, and either large dimensions or large process buffers are causing memory concerns, then enable the /3GB switch for the operating system and enable Analysis Services to use this additional memory.
Set Performance Monitor to collect the Temp file bytes written/sec counter for the Analysis Services:Proc Aggs object. By using Analysis Manager, configure the Analysis server properties to assign the Temporary file folder (on the General tab of the Server Properties dialog box) to an unused physical drive, and configure the process buffer size (on the Processing tab) to a minimal value, such as 32 megabytes (MB).
Restart Analysis Services and then use Performance Monitor or Windows Task Manager to determine what the virtual memory usage stabilizes at for the Analysis Services process (msmdsrv.exe).
Process the cube or partitions under consideration and observe the Temp file bytes written/sec counter you added to Performance Monitor. Once the aggregation calculation phase starts, you will start to see I/O to the Temporary files.
Gradually increase the process buffer size and re-process (restarting the Analysis Services service each time) until the Temp file bytes written/sec counter shows that the Temporary file is not being used. Then increase the number by 10 percent. If the virtual memory allocation for the Analysis Services service exceeds the HighMemoryLimit threshold, increase that value as well.
Repeat these steps for any large partitions (or groups of partitions) to determine the best system-wide process buffer size.

No comments:

Post a Comment

Any Comments: