0

I Works as a DBA and "inherited" about 50 instances (all server has about 8GB-24GB of RAM), all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

  1. Should I let SQL server to acquire and release by itself (Min Server Memory = 0)?
  2. Is it possible to get CPU overload by MSSQL Dynamic Memory Manager?
  • For your first question - i have written it SQL Server Min Server Memory. Can you clarify a bit more on 2 ? Are you telling that the memory manager will or might overload cpu when its adjusting memory ? – Kin Shah Oct 16 at 12:34
  • Yes, I'm asking if it's possible when SQL server acquire or release memory can cause CPU to overload – ariel ferdman Oct 16 at 14:27
  • No .. acquire or release memory due to memory pressure does not have impact on CPU in a direct way. Now, due to memory pressure, your plans will be evicted and that might result in CPU usage due to recompilations but that is an indirect relation. – Kin Shah Oct 16 at 15:10
  • Just as a side note: that sounds like a 'best practice scenario': if you are running "SQL Server on a server and only SQL Server" then you leave 4GB to the OS and dedicate the rest to SQL by setting the min and max memory. So I would be curious if you have a reason to change this. (I am not saying you shouldn't, it's just a matter of 'professional curiosity'). – LadyBug1 Oct 20 at 9:13
1

all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

Barring few cases keeping min and max server memory same can be counter productive. In such configuration when low memory flag is signaled SQL Server will not trim down its memory consumption below min server memory value. If memory pressure is severe SQL Server process may be paged to disk causing immense SQL Server slowness. To make this configuration worse if you have min and max server memory same and you on top of that provide LPIM to SQL Server service account this would be tantamount to SQL Server saying "do what you can I am not going to release memory". Such configuration can cause paging of OS process which requested memory or caused severe memory pressure, and even may lead to unexpected shutdown of OS.

Should I let SQL server to acquire and release by itself (Min Server Memory = 0)?

Please leave it to default value. On a system having multiple SQL Server instances and if you are biased to one of the system and want that its memory consumption, once reached, should not go below certain value then you can set min server memory to this threshold. On all other cases let min server memory be default.

Is it possible to get CPU overload by MSSQL Dynamic Memory Manager?

No that I know, never heard or came across such scenario. The memory code works to balance memory across SQL Server process and this not overload CPU. Even when SQL Server is growing its memory consumption you would hardly notice CPU rising because of growth, it may be due to other processes running.

| improve this answer | |
  • Just to be on the safe side, I want to clarify each instance runs on individual machine. – ariel ferdman Oct 16 at 14:07
  • @ArielFerdman In that case leave it to default value – Shanky Oct 16 at 14:53
  • Did you mean to change is to 0, or leave it min = max? – ariel ferdman Oct 16 at 19:05
  • @ArielFerdman I mean that keep it to 0 this is also default value. Since you said you have dedicated instance you can let min server memory be zero. – Shanky Oct 17 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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