About Performance if we increase buffer size | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

About Performance if we increase buffer size

In MSOLAP services for building cubes, if we increase the buffer size, will the performance be increased? Ifso, please specify the maximum buffer size in MSOLAP. Thanks & Regards,
Moorthy E
Process buffer size: Set how much data is processed in memory before an I/O is performed. The larger the value, the fewer the I/Os. Default: 4 MB. To change the process buffer size, in the Process buffer size box, specify the buffer size in MB. Only integers are valid.
Unfortunately tweaking the processing variables in Analysis Services is a black art and really the only way to find out if increasing (or indeed decreasing) it helps is by testing, testing, testing. See the following link for an explanation of what they do. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx In the work I’ve done here I’ve found that we get a performance gain by increasing the process buffer size and not much from the read ahead buffer size. All you can do to assess what is best for your server is change it, test it, change it, test it and you’ll find if you make either of the variables too large it will slow down your loads hugely. Regards, Robert.

What is the available physical memory on SQL Server analysis server? Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.