SQL Server 2005 memory support vs max memory setting

mkal 2015-02-05 13:26:03

I have a question concerning how much memory SQL Server 2005 Standard (x64) – SP4 supports vs the max memory setting.

I've read the information provided by this link https://technet.microsoft.com/en-us/library/ms143685(v=sql.90).aspx. According to this it supports 32TB of memory but, some comments below suggest this is a typo and it supports 32GB or 512GB of memory.

If the later is true and it only supports 32GB (for the sake of my question) of memory and the SQL instance (2005 std x64/SP4) is installed on a Windows Server (2008 R2/SP1) that has 128GB of RAM installed and I set the max memory to 114GB my assumption here is that SQL Server can only use up to the 32GB of RAM. If the latter is true and it's 32TB of RAM then SQL Server will use memory up the max memory setting.

Is my thinking correct here?

Lori 2015-02-05 14:18:48
SQL Server 2005 Standard Edition (x64) does support up to 32TB of memory. https://technet.microsoft.com/en-us/library/ms143685%28v=sql.90%29.aspx. And yes, SQL Server will use up to the max memory setting, so it is best practice to set the max memory setting to something like total memory – 1GB to save memory for the OS.
mkal 2015-02-05 17:28:18
I just find it odd the SQL SERVER 2008R2 only supports 64GB for the Standard Edition and 2014 supports 128GB. 32TB for SQL 2005 seems like it might be a typo which could lead one to believe the max server settings they are configuring above 32GB will not end up getting used if it is indeed a misprint.
Aaron Bertrand 2015-02-10 19:25:50
It is not a typo, and in the scenario you describe in your question, SQL Server 2005 will be able to use more than 32 GB of memory (and in fact the max memory setting controls a lot less than it does today, so chances are you could push SQL Server to use well beyond 114 GB).

(Though, that all said, I highly recommend against using SQL Server 2005 at this point, particularly for a new deployment, since it is no longer in support.)

They introduced more prohibitive memory limitations (IMHO) as an additional measure to get customers using Enterprise. With SQL Server 2014 they relaxed this from 64 GB (the limit in 2008 R2 & 2012) to 128 GB, which is a little better for most Standard Edition customers, but there are still many who believe memory shouldn't be limited in Standard Edition either).

In 2005 and 2008 the effective cap was essentially the OS maximum. See BOL for 2008, for example. Why they stated an actual limit of 32TB back when they wrote the 2005 docs, I have no idea. It's not like anyone on earth had machines with 32TB of RAM 10 years ago.

February 5, 2015
No Comments