Memory Configuration
-
Hello fellow DBAs
I have windows 2003 Enterprise x64 Edition, SQL 2000 64 bit 8.00.2039 SP4 Enterprise Edition
What is the maximum amount of memory SQL can address? The max that I am able to get is 8 gigs.
Actually I have 16 gigs on the server but sql server will only use a maximum of 8 gigs
Dan Pitta
-
If I'm reading you correctly, you want to know how much of the 8GB you should give to SQL Server? I would recommend giving SQL Server 7GB of the memory and leaving 1 GB for the OS. Just in case you're not 100% clear on what that means, you'll need to enable AWE, place the /PAE and /3GB switches in your boot.ini file, and set the Max. Server Memory (MB) setting in sp_configure to 7168. If your SQL Server is running on a non-system account, you'll also need to grant the 'lock pages in memory' privilege to that account.
-
Sorry Dan, don't know the answer to you question, but, thought you didn't need to use /PAE and /3GB for 64-bit OS and SQL John? Certainly you don't for 64-bit SQL2005. Happy to be corrected on this though.
Dan, I think you still have to initialise AWE in SQL2000, though - have you got it initialised?
-
This (http://www.sqlservercentral.com/columnists/rpearl/abitabout64bit.asp) seems to indicate you still need PAE and AWE. The data cache gets all your RAM, but the buffers for structures and objects are limited to 4GB.
Not a 64-bit guy, just what I read.
-
Sorry Steve, but I'm not reading the article like that...
As from http://www.sqlservercentral.com/columnists/rpearl/abitabout64bit.asp, Robert Pearl says...
'...Indeed, while 32-bit scalability scales well in most large transaction and data warehousing applications in areas such as supporting up to 32 CPU's and 64 GB of RAM, there are definitely limitations in the way it actually uses these vital resources. And that is precisely what 64-bit promises to solve.
For example, in the 32-bit SQL world, the maximum memory supported is 4GB. Out of this, the OS/Kernel reserves 2GB, and so SQL Server in reality only gets the other two. By setting the 3GB switch in the boot.ini, you can let SQL Server use 3GB, and the Kernel will get 1 GB.
Furthermore, in order to allow SQL Server to use up to 64 GB, one must configure the PAE switch in the boot.ini, as well as enabling AWE (Address Windowing Extension) using sp_configure. However, even this does not maximize the efficiency of your memory. Case in point, the additional RAM becomes available only to your buffer cache, and not your procedure cache, which is limited to only 4GB per instance. There are other 32-bit limitations affecting memory usage (which I won't go into detail), such page life expectancy, and memtoleave (used for out of process memory requests). So, basically the distribution of RAM in the SQL Server memory pool, is not "real" memory, and as some real-world memory stress tests have shown, can lead to memory contention, lower cache hit-ratio, and of course, slower performance. These limitations are no longer existent in the 64-bit world, and AWE is not used. ...'
If this is the passage you are refering to then I read it that Robert is saying you have to use /PAE and /3GB for the 32-bit version, but not for 64-bit, as per '...These limitations are no longer existent in the 64-bit world, and AWE is not used. ...'
Happy if there is anything out there to clarify this.
Rgds iwg
-
"Not a 64-bit guy, just what I read."
Me either. Maybe I shouldn't have answered this post!! Sorry if I caused anyone confusion.....
-
No problem John. IMHO thats how we all learn. I've got it wrong before and I'm sure I will again (and may even have now). Its good to have people discuss and critique. Glad you did comment, its made me do a bit more research on something that I worked with a while ago.
-
THX for everyones input...
Dan Pitta
-
From BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d308d9cb-bb85-46f6-93c6-e2ddd0fa01c3.htm
Note:
AWE is not needed and cannot be configured on 64-bit operating systems.
* Noel -
Noel...you might want to take a look at this article from Slav Oks's weblog http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx. This is where I originally got the info that using AWE on 64-bit is possible and may have benefits (taking into consideration the caveats mentioned).
Also, you may want to look at p106 of "Microsoft SQL Server 2005 Administrator's Companion" (under the heading Utilizing Large Memory with the 64-bit Version of SQL Server 2005), which states '...It is possible to enable the 'awe enabled' flag to guarantee that the memory allocated for SQL Server is not swappable...' I guess this is similar for the SQL2000 64-bit, as per Slav's blog above.
rgds iwg
PS It says nothing in the BOL link about not using AWE in 64-bit.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply