January 3, 2012 at 7:24 am
Hello Everyone, happy new year!
I have a quick question regarding how SQL Server 2008 uses memory. If a server has 64gb of memory and for arguments sake the OS takes 10GB of that, will SQL Server take whatever is left (so the memory always appears maxed out) or only what it requires?
Sorry if this is a bit general.
Kind Regards,
D.
January 3, 2012 at 7:29 am
January 3, 2012 at 7:31 am
johnitech.itech (1/3/2012)
:hehe:Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE
AWE is useless on 64-bit SQL
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
January 3, 2012 at 7:34 am
SQL Server will consume the memory specified in MIN memory configuration at start up. It will keep consuming more RAM if SQL Server needs for its operation & it will stop demanding more RAM when reaches MAX memory configuration.
SQL server is bit lazy in releasing consumed memory. So it won’t until OS is in Memory pressure.
PS. OS consumes 2 GB of RAM. Assigning 10 GB for OS operation is not a good configuration for dedicated SQL Server.
January 3, 2012 at 7:35 am
Duran (1/3/2012)
I have a quick question regarding how SQL Server 2008 uses memory. If a server has 64gb of memory and for arguments sake the OS takes 10GB of that, will SQL Server take whatever is left (so the memory always appears maxed out) or only what it requires
High level summary. SQL will take what it needs, up to the value of max server memory. If that value's not set it can and will take more than it should, potentially causing problems with the OS. The OS can request that SQL reduce its memory usage and SQL will do so, down to the value of min server memory. The OS is also able to swap SQL's memory out to the page file unless locked pages is set.
That was very high level.
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
January 3, 2012 at 7:36 am
Dev (1/3/2012)
SQL Server will consume the memory specified in MIN memory configuration at start up.
No, it does not. Min server memory is that value that once allocated SQL will not drop below. It is not the amount allocated on startup.
SQL server is bit lazy in releasing consumed memory. So it won’t until OS is in Memory pressure.
It's not lazy. It's that there's no point in releasing memory if the OS is not under memory pressure. Unused memory is wasted memory
PS. OS consumes 2 GB of RAM.
Err, no.
2GB is the default size of the kernel portion of VAS on 32-bit servers, not the amount of memory that the OS takes. The amount of memory that the OS takes is variable, depending on what the OS is (what version and edition) and how much memory there in in the server and likely a whole lot of other factors too.
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
January 3, 2012 at 7:50 am
GilaMonster (1/3/2012)
Dev (1/3/2012)
SQL Server will consume the memory specified in MIN memory configuration at start up.No, it does not. Min server memory is that value that once allocated SQL will not drop below. It is not the amount allocated on startup.
Thanks for correction!
SQL server is bit lazy in releasing consumed memory. So it won’t until OS is in Memory pressure.
It's not lazy. It's that there's no point in releasing memory if the OS is not under memory pressure. Unused memory is wasted memory
Why I say it lazy because if I run a RAM consuming job (outside the SQL Server's boundaries) it won't release memory immediately. It will take some time thus lazy.
PS. OS consumes 2 GB of RAM.
Err, no.
2GB is the default size of the kernel portion of VAS on 32-bit servers, not the amount of memory that the OS takes. The amount of memory that the OS takes is variable, depending on what the OS is (what version and edition) and how much memory there in in the server and likely a whole lot of other factors too.
Hmmm... Shouldn’t we count kernel’s consumption in OS’s consumption? Especially if server is idle (or dedicated as database server).
January 3, 2012 at 7:56 am
Firstly, a big thank you to everyone that has replied. I have 3 64bit servers, two of those servers have a 2 to 3 instances on them and are maxed out, but I have a third with 1 instance on it and this server has 10gb free, so if SQL consumes all the free memory available, why would I have one server that is not doing that. The max memory setting is at its default. How much additional memory per instance would one normally add?
Regards,
D.
January 3, 2012 at 8:41 am
Dev (1/3/2012)
PS. OS consumes 2 GB of RAM.
Err, no.
2GB is the default size of the kernel portion of VAS on 32-bit servers, not the amount of memory that the OS takes. The amount of memory that the OS takes is variable, depending on what the OS is (what version and edition) and how much memory there in in the server and likely a whole lot of other factors too.
Hmmm... Shouldn’t we count kernel’s consumption in OS’s consumption? Especially if server is idle (or dedicated as database server).
VAS != allocation. The kernel portion of VAS on 64-bit servers is 8 exabytes. No one's going to make a case that because of that the OS's memory usage exceeds 8 exabytes.
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
January 3, 2012 at 8:46 am
Duran (1/3/2012)
Firstly, a big thank you to everyone that has replied. I have 3 64bit servers, two of those servers have a 2 to 3 instances on them and are maxed out, but I have a third with 1 instance on it and this server has 10gb free, so if SQL consumes all the free memory available, why would I have one server that is not doing that.
Because SQL does not consume all the memory available. It consumes all that it needs, up to the max server memory or max memory in the server. If it's not using all the memory on the server, that means it does not need all of it
The max memory setting is at its default.
That's a problem right there. That needs to be set on 64-bit, doubly so if locked pages are set, triply so if there are other apps or instances on the server
How much additional memory per instance would one normally add?
How long is a piece of string?
That's not a question that has a general, always applicable answer. If SQL needs more memory, add more or limit memory and deal with the effects of memory bottlenecks.
May I suggest you read chapter 4 (at least) of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
January 3, 2012 at 9:36 am
Thank you, Gail.
I will read the link right away, thank you for that. In regards to...
That's a problem right there. That needs to be set on 64-bit, doubly so if locked pages are set, triply so if there are other apps or instances on the server
Do you have any advice in this area?
Regards,
D.
January 3, 2012 at 9:37 am
Yes, read the book!
She's not lazy, it's not something she can answer in 2 words.
January 3, 2012 at 9:41 am
Duran (1/3/2012)
Do you have any advice in this area?
Yes. Set a sensible max server memory. What's sensible, well, that depends on the OS, the amount of memory in the server and what else is running on the server. Low enough that SQL doesn't starve the OS or other apps.
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
January 3, 2012 at 9:43 am
Ninja's_RGR'us (1/3/2012)
Yes, read the book!She's not lazy, it's not something she can answer in 2 words.
Just to put things in perspective, the chapter that I'm referring you to is 28 pages long and took me several days to properly tech-edit.
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
January 3, 2012 at 9:46 am
GilaMonster (1/3/2012)
Ninja's_RGR'us (1/3/2012)
Yes, read the book!She's not lazy, it's not something she can answer in 2 words.
Just to put things in perspective, the chapter that I'm referring you to is 28 pages long and took me several days to properly tech-edit.
Wow, you guys are so underpaid for this!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply