11

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.

2 Answers 2

9

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.

3
  • I think perhaps we should restart SQL after SASS processing too - since it does large read (Data warehouse). Nov 28, 2012 at 5:01
  • Actually thinking about it - won't the restarting of SQL server actually impact all the SQL processing? Nov 28, 2012 at 10:15
  • 1
    @Preet yes, isn't that the point? Nov 28, 2012 at 12:56
7

Do SQL and SSAS ever need to run at the same time? If no, are you 100% sure?

I would look at setting the memory setting for SQL and SSAS to see if they can cooperate with one another.

We'll need to leave some memory for the OS, other processes, and multi-page allocations. Perhaps 4G?

Try something like this...

On the SQL side, set Max Server Memory to 12G and Min Server Memory to 6G.

On the SSAS side, set TotalMemoryLimit to 12G and the LowMemoryLimit to 6G.

Also, do not enable Lock Pages In Memory.

Try a few iterations, adjusting these numbers up or down.

If this just doesn't work, and you know for a fact SQL and SSAS do not need to run at the same time, then I would try Aaron's suggestion of scheduling jobs to turn services off and on.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.