November 15, 2017 at 9:53 am
Hi
Seeking expert opinion to know what is the preferred option / value for SQL Server Min Memory?
Thanks.
November 15, 2017 at 10:42 am
I have always left the Min Memory value set to 0.
November 15, 2017 at 12:24 pm
I have always set min and max to the same value... basically we are saying we dont want SQL Server to dynamically manage memory. Basically, i'm defining a set memory size ... yes, I know it will start low and build to that and yes I know it will not yeild back memory to the OS. In this configuration it's critical that plenty of memory is left for the OS and anything else other than SQL Server that might run on the server. Typically, we allocate about 70% of memory to SQL Server (and there is huge amounts of memory total and nothing else running on the server except the agent and a few other small consumers)
November 15, 2017 at 1:59 pm
Does Microsoft reveal any best practice to know which one they prefer?
Thanks.
November 15, 2017 at 2:02 pm
I strongly recommend against setting the min to the max. Something unexpected allocating memory (like a remote connection) and suddenly SQL's memory is in the page file and performance is abysmal. If you're going to do it, you must make absolutely sure that you know that the box will not come under memory pressure.
0 is usually fine unless you expect external memory pressure and don't want SQL to give up memory (at risk of it getting paged out if other things don't give up memory)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2017 at 10:49 am
GilaMonster - Wednesday, November 15, 2017 2:02 PMI strongly recommend against setting the min to the max. Something unexpected allocating memory (like a remote connection) and suddenly SQL's memory is in the page file and performance is abysmal. If you're going to do it, you must make absolutely sure that you know that the box will not come under memory pressure.0 is usually fine unless you expect external memory pressure and don't want SQL to give up memory (at risk of it getting paged out if other things don't give up memory)
" suddenly SQL's memory is in the page file and performance is abysmal " .... is that still the case with lock pages in memory set? If memory serves, what happens when min and max are set to the same value and locked pages is set and another application tries to consumer more memory than is available, is that other application gets an out of memory error (not enough memory available- something to that effect)
November 16, 2017 at 12:43 pm
NJ-DBA - Thursday, November 16, 2017 10:49 AMGilaMonster - Wednesday, November 15, 2017 2:02 PMI strongly recommend against setting the min to the max. Something unexpected allocating memory (like a remote connection) and suddenly SQL's memory is in the page file and performance is abysmal. If you're going to do it, you must make absolutely sure that you know that the box will not come under memory pressure.0 is usually fine unless you expect external memory pressure and don't want SQL to give up memory (at risk of it getting paged out if other things don't give up memory)
" suddenly SQL's memory is in the page file and performance is abysmal " .... is that still the case with lock pages in memory set?
No. In that case, depending on what needs the memory, and whether or not the OS can get memory back from something else, either things throw errors or the server crashes (seen that a couple times)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply