Memory 32bit vs 64bit, /PAE, AWE yes/no/maybe

Question

The facts:
64bit Windows 2003 Ent Ed with 64 gb of memory.
No /PAE in the boot.ini file.
32bit Windows SqlServer Ent Ed.

I have scanned blogs, msdn, this forum and other places trying to get an handle on this. I'm not a Win Server guy, but I have a little bit of knowledge on Sql Server.

It appears to me that:
WinServer2003 sees 64 gigs as shown in task manager.
SQLServer according to xp_msver sees 64 gigs
SQLServer has topped out at ~3.7 gigs according to DBCC_MEMORYSTATUS.
SQLServer Activity Monitor shows a lot of CX_PACKET, LOGFILEBUFFER, and various IO type waits.
I currently have Max Degree of Parallelism set to 2, the box has 4 dual core processors.
Below are listed the outputs the various folks have asked for when attempting to help others with similar questions.
Mine is simple, how cam I get SQLServer to make use of more than 3.7 gigs?

Thanks to all.

Bruce


Results of xp_msver are:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.4035.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.4035.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 264437760 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 8 8
17 ProcessorActiveMask 255 000000ff
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 65531 65531 (68714168320)
20 Product ID NULL NULL

dbcc memorystatus shows
Memory Manager
VM Reserved 3687448
VM Committed 3686432
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

Memory node Id=0
VM Reserved 3683288
VM Committed 3682424
AWE Allocated 0
MultiPage Allocator 11640
SinglePage Allocator 206016

Server Properties/Memory
Use AWE is not checked
Min Server Memory = 0
Max Server Memory = 2147483647
Index Creation memory = 0
Minimum memory per query = 1024


Performance counter Private bytes instance=sqlservr average = 3834818560
Performance counter Working Set instance=sqlservr average = 38286604928 

Answer 1

It sounds like you have everything set up right.... there's no need for pae or awe on 64-bit win in order for SQL to see and use the ram.  I think you may just need to load some big data sets in order for SQL to start consuming RAM.  It will only take what it needs.

Answer 2

Take a look at perfmon memory counters sql server total server memory and target server memory and let us know what you see there.  Those tell what sql is set to consume and what it is consuming.   Also, as Tome said, it may be the case that you just haven't loaded things into memory yet.  In perfmon, what's the buffer cache hit ratio look like?

Also, did you enable lock pages in memory for the sql server service account--nothing to do with this but something you should be aware of.  

Answer 3

Hmmm, it seems I have no SQLServer counters in perfmon. I'll try and see what the problem could be.
SQL runs under a user called gfmbservice, it's part of the admin group, which has Lock Pages In Memory rights. I did however add that specific user to the list, just in case it makes a difference.

I'll get back after I figure out how to enable the SqlServer counters.

EDIT Got my SQL counters back.

Total Server Memory (KB) 3604480  All the values are the same Last, Ave, Min, Max.
Target Server Memory (KB) 3604480.
Buffer cache hit ratio 99.671 average.


Bruce

Answer 4

I'm pretty sure since you are running 32 bit SQL you need to enable AWE to use more memory.  Otherwise 32bit SQL can't reference beyond the 4GB limit of that architecture.  You really need to run 64bit SQL to get fully use of that memory.

AWE is essentially the programming API that allows 32bit SQL to access memory beyond th 4GB limitation of 32bit system.  PAE was the 32bit implementation that allow the Hardware to access beyond the 4GB limitation of a 32bit system.  PAE is not needed on 64bit systems.

Essentially a 32bit processor is limited by 2 to the 32nd power (2^32 = 4GB)  as all the memory it can reference.  Thus the workaround mention above.

Answer 5

The OS might be 64-bit, but the process concerned is 32-bit, so it will be running under WOW64 and constrained to a 4 GB address space.
Please see Overview of the compatibility considerations for 32-bit programs on 64-bit versions of Windows Server 2003 and Windows XP and Performance and Memory Consumption Under WOW64.

Just wondering why awe hasn't been enabled?

---

ajmer dhariwal || eraofdata.com

Answer 6

eee gads... I didn't notice that in the original post... you should definitely run 64-bit sql.

Answer 7

I was just thinking about this some more.... I can't think of a reason you would want to keep 32-bit SQL on 64-bit Windows, but you should be able to just enable AWE to have SQL see and use the extra RAM.

Answer 8

OK Two followup questions.

To enable AWE I select the Use AWE to allocate memory check box. Should I then set min and max memory, I read some posts which say yes, other say no. I assume I'll need to restart the server for it to take effect.

Is is possible to upgrade my 32bit sql to 64bit, or do I need to unistall 32bit and then install 64bit? We're looking to move to SQL2008 next year and one of our problems we need to solve is no 64bit driver for MSACCESS. Are we facing the same problem if we go to 64bit SQL2005?

Thanks for all the help and information, I'm sort in over my head right now just in case you couldn't tell.


Bruce

Answer 9

In general you only need to set min and max memory if you have issues or run in a cluster.  But my experience has been that on heavily used systems you may want to so you can reserve enough memory for the OS.

Read these articles for more information on SQL memory architecture
Read the section Dynamic Memory Management and Memory Architecture of SQL 2005
http://networkadminkb.com/kb/Knowledge%20Base/SQL/SQL%20Error%20A%20significant%20part%20of%20sql%20server%20process%20memory%20has%20been%20paged%20out.aspx


How to configure SQL Server 2005 to use more than 2GB memory - the was written for 32bit OS, but the information about AWE is valid as it applies to the 32bit SQL...the information after step 5) is most usefull to you
http://networkadminkb.com/kb/Knowledge%20Base/SQL/How%20to%20configure%20SQL%20Server%202005%20to%20use%20more%20than%202GB%20memory.aspx

Configuring SQL 2005 Maximum Server Memory
http://networkadminkb.com/kb/Knowledge%20Base/SQL/Configuring%20SQL%202005%20Maximum%20Server%20Memory.aspx

You can probably install a second instance of SQL 2005 64bit...as far as i know they can run on the same server.

Answer 10

If you're enabling AWE then it's advisable to cap the memory usage to ensure the OS has room to breathe.

You can't of course upgrade over an existing 32-bit installation with 64-bit, but you'll be able to carry out a side-by-side install and move the database(s) either using backup/restore or detach and attach, without any issues.

Your 64-bit access driver problem will remain if you choose to upgrade, however, unless MS (or a third party) release a 64-bit driver.

---

ajmer dhariwal || eraofdata.com

Answer 11

Thanks to all who have helped me through this. I enabled AWE and rebooted SQL this morning. Now my target server memory shows 55377920, and right now total server memory is sitting at 10374400(K).

Thanks again,

Bruce