July 23, 2012 at 12:07 pm
Not sure if any of you have run into this before--
My client has SQL 2008R2 Enterprise running on Windows Server 2008R2 Standard. The server has 32GB of memory, and has a lot of memory pressure. We are considering adding more, and the quote we received from a new provider was for the exact same software, but with 48GB of memory. My understanding from reading Microsoft literature is that the maximum amount of memory that Standard version of Windows Server 2008R2 will support is 32GB. The SQL literature says that Enterprise SQL maxes out at 64GB, but I'm sure that's only when the OS supports it.
What happens if the server has 48GB of memory? Does the OS just ignore it, or is there some way that SQL Server can access it?
Thanks in advance for any advice --
SQLNYC
July 23, 2012 at 12:25 pm
Do not confuse OS memory limits with SQL Server memory limits. SQL Server Standard edition maxes out at 64GB of RAM. That means that is the most the SQL process can utilize. The OS limit is much higher (128 GB on a 64 bit Standard edition OS I think but you'll need to double-check).
Where are you seeing the memory contention? In the OS or in SQL Server. SQL should continue to use as much as it needs but release to the OS when it can. That doesn't always happen but it's rare that it doesn't behave as designed.
July 23, 2012 at 12:35 pm
Hi Scott,
Thanks for the quick reply.
I was referring to this document:
which states that the OS memory limit for Windows Standard 208R2 is 32GB, seemingly in conflict with what you wrote (128GB max for Windows Standard).
The server has 1GB allocated to Windows, and the rest (31GB) allocated to the SQL Buffer Pool. This is the first issue. But there are also people using Remote Desktop to access this server, in addition to at least two and possibly three SSD cards, also requiring memory. If we are constrained by the OS, then we'll have to upgrade. I'm trying to clarify what happens to memory above 32GB on Windows Server 2008R2 Standard. Thanks again for your feedback.
SQLNYC
July 23, 2012 at 12:38 pm
Here is my guess, you are constrained by the amount of memory the OS can use. If SQL Server could use 64GB, but the OS can only use 32GB, that is your constraint.
July 23, 2012 at 12:41 pm
The way I'm understanding it, the OS can only address at most 32 GB of memory. SQL Enterprise is much higher. So in order to address some of your contention, you could increase the max assigned to SQL Server (assuming you set this at the server level which it sounds like you did). You can also probably afford to assign some more to the OS.
1 seems a little low but it depends on the workload. If you have people connecting with RDP and doing other things, 1 might not be enough for the OS. Again, depends on their workload and how many users.
I stand corrected, don't know what kind of math I was doing but I'm pretty sure Lynn is correct.
July 23, 2012 at 1:43 pm
sqlnyc (7/23/2012)
What happens if the server has 48GB of memory? Does the OS just ignore it, or is there some way that SQL Server can access it?
Excellent q ... and I'm not sure of the answer. I don't know how Windows reacts to "excess" RAM and if SQL can use it for its own buffer pools outside of the O/S.
Hopefully someone can give us a definitive answer on that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2012 at 5:56 pm
Reply from Jonathan Kehayias of SQLSkills.com:
"SQL Server can only see memory that is available to the Windows OS version it is running on. If the server has 48GB installed, but the Windows version only supports 32GB, all that SQL Server will see is 32GB, the other 16GB are not addressable in any manner unless you change the SKU for your Windows version to Enterprise Edition."
Thanks to all who replied --
sqlnyc
July 23, 2012 at 6:18 pm
Backing Jonathan on this - yes SQL Server can only use what the OS is constrained to as a maximum.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply