September 30, 2008 at 1:54 pm
In http://blogs.msdn.com/psssql/archive/2008/04/05/sql-server-2005-memory-limits-and-related-questions.aspx it is stated that:
The following table specifies the maximum memory support for each edition of Microsoft SQL Server 2005.
SQL Server 2005 edition: standard
Maximum memory supported (32-bit): OS maximum
(This edition of SQL Server 2005 will support the maximum memory supported by the operating system.)
Then in http://msdn2.microsoft.com/en-us/library/aa366778.aspx it is stated that:
Physical Memory Limits: Windows Server 2003
The following table specifies the limits on physical memory for Windows Server 2003. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.
Version: Windows Server 2003 R2 Enterprise Edition
Limit in 32-bit Windows: 64 GB
Does this mean I can configure a SQL Server instance (standard Ed., 32-bit) to access a max of 64 GB, with no AWE enabled (provided I'm on that OS level)?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 30, 2008 at 2:47 pm
yes, you can use 64GB, but you need to enable AWE for 32 bit.
September 30, 2008 at 2:50 pm
as long as you are using 32 bit SQL server, you will need to enable AWE to access memory over 4GB
---------------------------------------------------------------------
September 30, 2008 at 2:51 pm
Adam Angelini (9/30/2008)
yes, you can use 64GB, but you need to enable AWE for 32 bit.
Thank you, I've always thought so too, but AWE is not mentioned anywhere in these links...
I wonder if AWE is indeed necessary in this case.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 30, 2008 at 2:53 pm
I know for a fact that it is necessary to use AWE - I've tried it without doing so.
September 30, 2008 at 2:53 pm
george sibbald (9/30/2008)
as long as you are using 32 bit SQL server, you will need to enable AWE to access memory over 4GB
Could it be that not mentioning AWE is simply a careless omission?
I have to ask that because I don't know what else to think...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 30, 2008 at 2:56 pm
Adam Angelini (9/30/2008)
I know for a fact that it is necessary to use AWE - I've tried it without doing so.
Thanks, I'm also quite incredulous about this...
I'm just stunned at how unreliable and misleading the information in these links can be...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 30, 2008 at 3:23 pm
george sibbald (9/30/2008)
as long as you are using 32 bit SQL server, you will need to enable AWE to access memory over 4GB
and don't enable the /3GB switch in the boot.ini either
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 30, 2008 at 3:25 pm
correct, the only things necessary is the AWE option and the lock pages in memory permission for your SQL account.
September 30, 2008 at 3:30 pm
Adam Angelini (9/30/2008)
correct, the only things necessary is the AWE option and the lock pages in memory permission for your SQL account.
Yes, I have been using this link to configure AWE:
Enabling AWE Memory for SQL Server - SQL Server 2005 Books Online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c44df11e-2b75-424c-bc14-56646169a56f.htm
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 1, 2008 at 3:35 am
Marios Philippopoulos (9/30/2008)
george sibbald (9/30/2008)
as long as you are using 32 bit SQL server, you will need to enable AWE to access memory over 4GBCould it be that not mentioning AWE is simply a careless omission?
I have to ask that because I don't know what else to think...
I have found microsoft like to accentuate the positive, and will just say yes you can do things, they often fail to mention the complications and pitfalls awaiting you. 🙂
---------------------------------------------------------------------
October 1, 2008 at 3:39 am
Perry Whittle (9/30/2008)
george sibbald (9/30/2008)
as long as you are using 32 bit SQL server, you will need to enable AWE to access memory over 4GBand don't enable the /3GB switch in the boot.ini either
to start up old discussions again, use the 3GB switch if you have up to 16GB physical memory, don't if you have more than that. (So the OS has the extra GB to handle the extra memory allocating).
If in doubt,test it with and without.
---------------------------------------------------------------------
October 1, 2008 at 7:07 am
What's stunning about this is that AWE is not even the ideal solution for SQL Server accessing more memory: only the buffer cache benefits from it.
So when it is stated in these sites that SQL Server can access all the memory offered by the OS, where do they get this info? It's totally false!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 2, 2008 at 2:50 am
The more you read about how 32-bit Windows uses memory above the 4GB line, the more you will realise that the information quoted in your original post is correct.
All other 32-bit OS that I know of that give access to memory above the 4GB line use the same technique (but with different names), and all other DBMS that I know of that exploit the OS technique only use the additional memory for buffer pools because of the OS limitations.
Can I suggest you do some research on this issue (start with SQL Server Books Online). I have always found it helpful to me to understand a bit about how the operating system works. This issue of access memory above the 4GB line clearly explins to me why a 64-bit OS is by far the best solution to the problem.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 2, 2008 at 7:57 am
EdVassie (10/2/2008)
The more you read about how 32-bit Windows uses memory above the 4GB line, the more you will realise that the information quoted in your original post is correct.All other 32-bit OS that I know of that give access to memory above the 4GB line use the same technique (but with different names), and all other DBMS that I know of that exploit the OS technique only use the additional memory for buffer pools because of the OS limitations.
Can I suggest you do some research on this issue (start with SQL Server Books Online). I have always found it helpful to me to understand a bit about how the operating system works. This issue of access memory above the 4GB line clearly explins to me why a 64-bit OS is by far the best solution to the problem.
I can't see how the information I quoted in the original post is correct.
I find it misleading, to say the least.
AWE is nowhere mentioned, and, unless it is true that AWE is not necessary to achieve memory access over the 4-GB limit in 32-bit, the information is plain incorrect.
I don't know how else to put it.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply