Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
I didn't think about that. Thank you. –  Preet Sangha Nov 28 '12 at 4:55
    
I think perhaps we should restart SQL after SASS processing too - since it does large read (Data warehouse). –  Preet Sangha Nov 28 '12 at 5:01
    
Actually thinking about it - won't the restarting of SQL server actually impact all the SQL processing? –  Preet Sangha Nov 28 '12 at 10:15
1  
@Preet yes, isn't that the point? –  Aaron Bertrand Nov 28 '12 at 12:56
add comment

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.

share|improve this answer
    
thank you for this. –  Preet Sangha Jan 23 '13 at 21:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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