SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Summary. Due to the improvements in the Windows Server 2008 memory manager related to the change in the algorithm for scanning pages in memory, SQL Server 2008 Analysis Services performed equally well during both partition and dimension processing with or without memory preallocation when running on Windows Server 2008. However, SQL Server 2008 Analysis Services performed substantially better during both partition and dimension processing with the use of the memory preallocation configuration setting when running on Windows Server 2003. Specifically, the processing performance of Analysis Services 2008 without memory preallocation on Windows Server 2008 was virtually identical to the processing performance of Analysis Services 2008 on Windows Server 2003 with memory preallocation. With the hardware that we used in our tests and with the Analysis Services objects that we processed, we observed a performance benefit of approximately 100% during partition processing and a performance benefit of between 30-40% during dimension processing.

If you use memory preallocation with SQL Server 2008 (or SQL Server 2005), use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services (use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).

Note: While we did not specifically test SQL Server 2005 Analysis Services for this technical note, the results discussed here apply to Analysis Services 2005 as well as to Analysis Services 2008 because the underlying code base for the portion of Analysis Services 2008 that we tested has not changed.