March 27, 2012 at 6:35 pm
As per BOL Max Memory (buffer pool) is upper limit of memory that Sql Server can acquire. Let say total server memory is 8 GB, Sql Server Max memory is 7 GB (i.e. 1 GB left to OS and other processes). At some point Sql Server uses all 7 GB, but OS (plus others) also requires more than 1 GB. What happens in this case- OS will "borrow" extra memory from 7 GB specified in Sql Server Max memory or cannot do it (i.e. OS will suffer from memory pressure)? Read some info but still not clear. Thanks
March 28, 2012 at 5:43 am
Depends on the memory settings.
If SQL's min server memory is lower than the max, the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result.
If SQL's max server memory and min server memory are set to the same, the OS will ask SQL to reduce its memory usage, SQL will refuse, the OS will then page SQL Server's memory to disk. SQL's performance degrades terribly as a result.
Worst case, SQL's max server memory and min server memory are set to the same value and locked pages in memory is enabled. In that case SQL will not reduce its memory usage and the OS cannot page it to disk. This can result in OS instability and even server crashes.
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
March 28, 2012 at 6:34 am
GilaMonster (3/28/2012)
Depends on the memory settings.If SQL's min server memory is lower than the max, the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result.
If SQL's max server memory and min server memory are set to the same, the OS will ask SQL to reduce its memory usage, SQL will refuse, the OS will then page SQL Server's memory to disk. SQL's performance degrades terribly as a result.
Worst case, SQL's max server memory and min server memory are set to the same value and locked pages in memory is enabled. In that case SQL will not reduce its memory usage and the OS cannot page it to disk. This can result in OS instability and even server crashes.
Gail, sorry but I am still not clear with my case- close to your #1 (we have 7/1 GB as Max/Min mmeory and Locked pages in memory enabled- 32 bit server) Will sql reduce its memory usage by OS request or not? Thanks
March 28, 2012 at 7:13 am
Yuri55 (3/28/2012)
GilaMonster (3/28/2012)
Depends on the memory settings.If SQL's min server memory is lower than the max, the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result.
If SQL's max server memory and min server memory are set to the same, the OS will ask SQL to reduce its memory usage, SQL will refuse, the OS will then page SQL Server's memory to disk. SQL's performance degrades terribly as a result.
Worst case, SQL's max server memory and min server memory are set to the same value and locked pages in memory is enabled. In that case SQL will not reduce its memory usage and the OS cannot page it to disk. This can result in OS instability and even server crashes.
Gail, sorry but I am still not clear with my case- close to your #1 (we have 7/1 GB as Max/Min mmeory and Locked pages in memory enabled- 32 bit server) Will sql reduce its memory usage by OS request or not? Thanks
As I said, if SQL's min server memory is lower than the max (and 1 is lower than 7), the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result. No impact from locked pages here.
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
March 28, 2012 at 7:26 am
"the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result. No impact from locked pages here."
This is what I thought (I mean "No impact from locked pages here") but why "No one suffers" as Sql was forced to release some memory it needs- as I started in my post Sql needs (and uses) 7 GB of memory. Thanks again for your help
March 28, 2012 at 7:39 am
Yuri55 (3/28/2012)
"the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result. No impact from locked pages here."This is what I thought (I mean "No impact from locked pages here") but why "No one suffers" as Sql was forced to release some memory it needs- as I started in my post Sql needs (and uses) 7 GB of memory. Thanks again for your help
This is by design. This way you avoid the paging which can be slow. If you do not want that then configure your min memory equal to max memory in addition to Lock pages in memory.
March 28, 2012 at 7:41 am
No one suffers meaning that SQL has voluntarily reduced its memory usage and manages with less. Compare that to the case where it thinks it still has 7GB of memory and uses all 7GB but some of that is actually swap file and far, far slower than memory.
Yes, you may see more physical reads, more compiles because the memory is smaller, or maybe SQL manages things just fine. Depends on the app and the usage.
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
March 28, 2012 at 7:43 am
Usman Butt (3/28/2012)
Yuri55 (3/28/2012)
"the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result. No impact from locked pages here."This is what I thought (I mean "No impact from locked pages here") but why "No one suffers" as Sql was forced to release some memory it needs- as I started in my post Sql needs (and uses) 7 GB of memory. Thanks again for your help
This is by design. This way you avoid the paging which can be slow. If you do not want that then configure your min memory equal to max memory in addition to Lock pages in memory.
In which case, if the OS needs memory it won't be able to get it and may crash. That's a risky memory config (max = min and locked pages). Use it only if you're absolutely sure that the OS will never ever need more memory than has been allocated for it.
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
March 28, 2012 at 7:57 am
GilaMonster (3/28/2012)
Usman Butt (3/28/2012)
Yuri55 (3/28/2012)
"the OS will ask SQL to reduce its memory and SQL will do so, down to min server memory at the lowest. No one suffers, this is the best possible result. No impact from locked pages here."This is what I thought (I mean "No impact from locked pages here") but why "No one suffers" as Sql was forced to release some memory it needs- as I started in my post Sql needs (and uses) 7 GB of memory. Thanks again for your help
This is by design. This way you avoid the paging which can be slow. If you do not want that then configure your min memory equal to max memory in addition to Lock pages in memory.
In which case, if the OS needs memory it won't be able to get it and may crash. That's a risky memory config (max = min and locked pages). Use it only if you're absolutely sure that the OS will never ever need more memory than has been allocated for it.
I totally agree with that. Since that was the one of the key points in your earlier response, I thought not necessary to mention it again 🙂
Since you are active on this, I am availing the opportunity to ask about your view on "Locking pages in memory" even for 64 bit servers. Do you use it on your servers? If yes, then any special steps taken before tasks like copying 1 GB file etc. Thanks in advance.
March 28, 2012 at 8:04 am
I do like locked pages, especially on a dedicated server. I set the max memory carefully, leave the min at 0.
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
March 28, 2012 at 8:11 am
"No one suffers meaning that SQL has voluntarily reduced its memory usage and manages with less"
OK, make sense- like as during robbery one asked you for money and you voluntarily gave it to him to avoid more harm 🙂
To resume- OS always rules memory distribution (only exception- sql MAX = MIN settings and Locked Pages in memory enabled)
Thanks everybody for help, Gail- always pleasure to have you as answerer. Yuri
March 28, 2012 at 8:20 am
Also note that just because SQL is using 7GB of memory doesn't mean it absolutely has to have 7GB to work. SQL's a memory hog, it will use whatever it can get in terms of 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
March 28, 2012 at 8:25 am
Yuri55 (3/28/2012)
Gail- always pleasure to have you as answerer. Yuri
Congratulations for being one of the ever increasing admirers. No doubt, one of my favorite mentors.
March 28, 2012 at 8:32 am
GilaMonster (3/28/2012)
Also note that just because SQL is using 7GB of memory doesn't mean it absolutely has to have 7GB to work. SQL's a memory hog, it will use whatever it can get in terms of memory.
One more point. My understanding is, SQL Server while reducing the memory, would keep the more frequent used pages and flush out not much used pages. Is it correct?
And better would not be to set min memory to a reasonable number like in this case 3 GB instead of 0?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply