August 12, 2010 at 1:11 pm
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.
August 12, 2010 at 2:03 pm
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
August 13, 2010 at 4:59 am
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
August 13, 2010 at 5:28 am
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
August 13, 2010 at 6:37 am
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
August 13, 2010 at 6:40 am
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
August 13, 2010 at 6:43 am
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
August 13, 2010 at 6:46 am
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
August 13, 2010 at 6:56 am
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
August 13, 2010 at 6:59 am
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
August 13, 2010 at 7:07 am
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
August 13, 2010 at 7:19 am
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.
August 13, 2010 at 8:01 am
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
August 13, 2010 at 8:20 am
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:
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
August 13, 2010 at 8:26 am
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply