Sql-server – SSAS and SQL Server optimal settings

configurationsql serversql server 2014ssas

I have 4 cubes running on the SSAS server. The CPU seems to peg and we had memory issues with the 4th cube during loads.

We have 4 CPUs with MAXDOP set at 2, cost threshold for parallelism = 70. We added server memory 68GB to 96GB, with max server memory from 33GB to 44GB.

I had max SQL Server memory higher, but looks like the OS and other processes were causing memory issues, so I lowered it to 44GB. Is there a great article to help with SSAS and optimal settings? I would like to adjust them so we don't throw un-needed CPUs and memory at it if possible.

Best Answer

There are several whitepapers on optimizing SSAS that you can find on James Serra's blog here - http://www.jamesserra.com/archive/2011/12/top-ssas-white-papers/

I would particularly look at:

The SSAS performance guide in the settings and configuration sections https://technet.microsoft.com/en-us/library/dd542635(v=sql.100).aspx

and for memory management http://sqlcat.com/technicalnotes/archive/2008/07/16/running-microsoft-sql-server-2008-analysis-services-on-windows-server-2008-vs-windows-server-2003-and-memory-preallocation-lessons-learned.aspx

I'm not sure what your cost limitations are, but if you're hosting SQL Server Service and the Analysis Service on the same box, I would advise against it if you have fairly large cubes.

Related Question