How to balance SQL Server memory with SQL Server Analysis Services memory

We have many systems that have the same configuration

• One Server (virtual or physical)

• Running SQL Server (SQL) and SQL Server Analysis Server (AS)

• multiple cores

• 16gb RAM

Each night the SQL Server will do about 2-3 hours processing, followed by 2-3 hours of AS processing. Then throughout the day only the AS are queried.

Assuming this is a dedicated server, and no other apps are of concern, and that the two sets of processing are completely synchronous - no overlaps always one after the other - how can I best set the SQL and AS server memory limits.

The reason for asking is that if I don't set a limit for SQL it will grab all the memory it can. However - my understanding is that SQL will happily relinquish this memory if:

-
It's not using it and

-
Another service/program requests it.

So from a logical perspective I believe allow SQL to take as much as it needs, but I'm not so sure about AS' TotalMemoryLimit. I'm not sure if AS will relinquish it's memory. In fact reading more leads me to believe that it is wrong to let it take it all.

Does this mean that I need actually set limits for both? I'm confused as to what the best practises should be, and what we need to be measuring considering the processes don't overlap.

Hope this makes sense.

I think it is feasible to force both services' hands by having a scheduled task or service that:

(a) before SQL Server's nightly processing, shuts down the SSAS service, and increases the memory allotted to SQL Server

(b) after SQL Server has done its processing, reduces max server memory and restarts SQL Server

(c) starts SSAS

This assumes that SSAS isn't required to be online while SQL Server is processing. Otherwise change (a) from "shuts down" to "restarts"... as long as SSAS isn't actively doing anything while SQL Server is processing, there shouldn't be any concern that it will steal memory back.

This also assumes that you want each service to overlap memory consumption, and use the same memory at different times - I'm sure there are ways with affinity / NUMA etc. to completely segregate their operations space or, in the worst case, to host them on different machines.

StackExchange Database Administrators Q#29403, answer score: 9