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 (no other apps are of concern) and that the two sets of processing are completely synchronous (no overlaps 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
a. It's not using it and b. 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.