Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seware
Starting Member
11 Posts |
 Posted - 2011-08-30 : 12:55:10
|
| I have a single server VM that is allocated 40 GB RAM and 6 cores. This server is running Server 2008 R2 Enterprise SP1. It has two instances of SQL Server 2008 R2 Standard. 1 is for SharePoint farm and the other is for BI. There is one Analysis Services db, 2 major SSIS packages deployed and 1 SSRS (sharepoint integrated) instance in use.We are a small shop without a DBA, so I am doubling as data analyst\ BI guy and DBA. (with no DBA experience)1. Suggestions on memory allocation? I currently have Max Server Memory for each SQL instance set to 10GB and processor affinity at 'automatic'. The Analysis Services db has Total Mem Limit set to 45 and Low Mem Limit set to 35.Is this reasonable? (My reasoning was 50% for the 2 SQL servers(split equally), 45% for SSAS and 5% for OS and everything else)2. Looking at the OS 'resource monitor", "used physical memory" has been sitting at 64% consistently since setting the config as described above. This represents what? SQL1 + SQL2 + OS? Can I tell if SSAS is using what was configured? It doesn't appear so, but yet I get "memory pressure" errors when trying to query the cube?A little advice please... thank you for your expertise. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-30 : 14:36:57
|
| I think you should go for 60% for Total Memory and 45% for Low Memory limit of the available memory i.e in your case 40 GB for SSAS.For SQL you can have Minimum to 40% and Max to 50% of the available memory.PBUH |
 |
|
|
seware
Starting Member
11 Posts |
Posted - 2011-08-30 : 15:36:40
|
please forgive if this is ignorant Sachin, but if I set 60% for SSAS total and 50% for Max on SQL instances, doesn't that equal 110% potentially allocated, leaving (less than) nothing for OS and other processes? confused by your answer... |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-31 : 01:58:46
|
Oops sorry I had my calculations wrong.Set the minimum memory for SQL Server to 35%.See the following settingsTotal Memory Limit for SSAS 60% of 40GB - 24 GB Low Memory Limit for SSAS 45% of 40GB - 18 GB Minimum Memory Limit for SQL 35% of 40GB -14 GB of MinMaximum Memory Limit for SQL SQL 50% of 40GB -20 GB Now the above memory settings will make sure that the memory available for SQL and SSAS will always be 24+14=38 GB leaving 2 GB for OS which I think is a fair deal and more than enough.If you leave Total Memory limit high then SSAS wont clear down memory in case other services need it and will grab the memory within its limits and if you set it to low it will clear the memory even in cases where it needs it.The reason for above calculations is that SSAS clears down memory in case of no memory pressure but not more than the Low memory limit level.This cleared memory will then be available to SQL.For SQL make sure that you have Lock page In memory settings if it is 64 bit or else SQL pages out its memory. This will make sure that the memory always available for SQL will be 14 GB.But of course all the above settings depends on your work load and database design.I have seen places where even 64GB memory on dedicated boxes have made performances down on its knees.PBUH |
|
|
|
seware
Starting Member
11 Posts |
Posted - 2011-08-31 : 09:13:58
|
| I believe what you're saying... but I still don't understand. (I'd like to learn in the process instead of blindly following).If I set SQL Max to 50% (20GB) and SSAS Total to 60% (24GB) can't I still find myself in situation where I am overcommitted and the OS is starved? (24 + 20 > 40) |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-31 : 13:11:24
|
| But you are not taking into account the memory which SSAS will clear when it does not need it which will be within its limits.So in case total memory for SSAS is 24GB there is a very high possibility that it will clear it down to 18 GB so this 6 GB cleared memory will be available for SQL.So if SQL is using say 16GB it can grab the extra 4 GB from the memory which SSAS has cleared out.Its not like 1 Apple + 1 Orange = Nothing or Error rather it is like 1 Apple + 1 Orange= 2 nice juicy FruitsPBUH |
|
|
|
|
|
|
BI SQL Server allocations