64-bit topped out at 4 GB used

Question

Post reply

64-bit topped out at 4 GB used

Alan Spillert

SSCommitted

Points: 1964
More actions
August 12, 2010 at 1:11 pm

#227731

I am moving databases to a dedicated server running:
Windows Server 2003 R2 Enterprise Edition 64-bit with 24 GB installed (all patches installed)
SQL Server 2005 Standard 64-bit SP3 (9.00.4053.00)
After a couple of days use SQL Server topped out at 4.1 GB, according to Task Manager. In trying to figure out why I’ve found that Windows Server 2005 R2 Enterprise Edition 64-bit supports 1 TB memory, and SQL Server 2005 Standard 64-bit should support up to 2 TB memory.
Any idea why we have struck a low ceiling? What parameter or setting is missing? I’ve been looking at TECHNET and MSDN and SQLServerCentral without much luck.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Can you post the results of the following;

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Also

SELECT @@version

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 3

David,

This is a neat way of capturing that information:

select name,

value,

value_in_use

from sys.configurations

where name in

(

N'min server memory (MB)',

N'max server memory (MB)',

N'awe enabled'

);

Looks like 32-bit SQL Server running under WOW64 to me.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Paul White NZ (8/13/2010)
David,
This is a neat way of capturing that information:
select name,
value,
value_in_use
from sys.configurations
where name in
(
N'min server memory (MB)',
N'max server memory (MB)',
N'awe enabled'
);
Looks like 32-bit SQL Server running under WOW64 to me.
Paul

Definitely like the view. Thanks!

Out of curiosity what makes you think this is a WOW configuration even though he stated that both Windows and SQL Server were 64-Bit?

As long as you are willing to share info, I'll take it in. 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

David Benoit (8/13/2010)
Out of curiosity what makes you think this is a WOW configuration even though he stated that both Windows and SQL Server were 64-Bit?

4GB is the maximum amount of memory a 32 bit app can use running on a 64 bit OS.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

GilaMonster (8/13/2010)
4GB is the maximum amount of memory a 32 bit app can use running on a 64 bit OS.

Thanks Gail. Hate to think that this is what they did but I can see where you would come to that conclusion.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Paul White SSC Guru Points: 150467 More actions · Answer 7

David Benoit (8/13/2010)
Out of curiosity what makes you think this is a WOW configuration even though he stated that both Windows and SQL Server were 64-Bit?

SQL Server x86 gets a 4GB virtual address space when running under WOW:

http://blogs.msdn.com/b/slavao/archive/2006/03/12/550096.aspx

None of the alternative configurations I can think of would produce the memory usage reported by Task Manager (AWE requires locked pages, and non-paged memory isn't reported in TM usage).

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

David Benoit (8/13/2010)
GilaMonster (8/13/2010)
4GB is the maximum amount of memory a 32 bit app can use running on a 64 bit OS.
Thanks Gail. Hate to think that this is what they did but I can see where you would come to that conclusion.

I had a recent situation with a client of mine, they told me they were running SQL 2005 x64. I was doing a perf tuning exercise and, from the data I had it was clear that SQL needed more memory. I checked perfmon and SQL was using 4GB out of 16 GB. I checked the SQL error log. Right at the top:

Microsoft SQL Server 2005 - 9.00.3042 (X86)

Person who had installed SQL had installed the 32 bit instead of 64 bit, and no one had checked.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 9

Thanks Paul and Gail. Definitely great observations. Not sure if the OP is even following the thread but I certainly appreciate it. 😛

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Paul White SSC Guru Points: 150467 More actions · Answer 10

GilaMonster (8/13/2010)
Person who had installed SQL had installed the 32 bit instead of 64 bit, and no one had checked.

It's all too easily done -the install media doesn't exactly shout the difference.

There was a phase when some people recommended it. (Not me).

My apologies for duplicate/late postings tonight - I'm having issues with the SSC website.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

OP might just have SQL Server configured to not use more that 4 GB of memory.

Or the databases are small enough that SQL Server doesn't need more than 4 GB of memory.

Paul White SSC Guru Points: 150467 More actions · Answer 12

Michael Valentine Jones (8/13/2010)
OP might just have SQL Server configured to not use more that 4 GB of memory.
Or the databases are small enough that SQL Server doesn't need more than 4 GB of memory.

Could be. I guess we'll see from the configuration output David asked for.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alan Spillert SSCommitted Points: 1964 More actions · Answer 13

After posting I changed the server memory max and min to 16384 and 20480 respectively; it had been the default zero to 2 petabytes. AWE is not enabled - value is 0. (Is this my problem?)

Version info from @@version:

Paul White SSC Guru Points: 150467 More actions · Answer 14

Alan Spillert (8/13/2010)
AWE is not enabled - value is 0. (Is this my problem?)

No - the AWE setting is ignored by x64 SQL Server. What about Michael's suggestion? Are the databases just not big enough to ever need more than 4GB of memory?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi