Recommendation for Right Memory Settings (AWE, PAE, 3GB)

I am looking for a recommendation or an overview for the memory settings in the OS and SQL-Server in different versions. What do I have to activate in the boot.ini when I have 4 GB or 8GB Memory, when I have Windows-Server 2003 Standard, Enterprise in 32 or 64 bit. What do I have to avtivate in SQL-Server2005. Are there any risks? You find a lot, sometimes different statements in the net. Does somebody have a general overview about these settings?

Thanks,

Johannes

Hi!

Welcome to Dynamicsusers.net! [<:o)]

Well, first of all: you DON’T want to run a SQL Server 2005 with just 4GB - consider 8GB as absolute minimum (forget about any “marketing talk” stating differently).

32bit Systems:

< 4GB: /3GB (boot.ini)

4GB <=16GB: /3GB /PAE (boot.ini) AWE (SQL Server)

16GB: /PAE (boot.ini) AWE (SQL Server)

Also regard the correct settings of AWE (Min/Max Memory, Lock Pages).

64bit System:

No special configuration required.

Of course, it also depends on the limitations of the Operating System and the SQL Server Edition, please refer to the relevant documentation to look up these limit.

Best regards,

Jörg

The correct setting, is to upgrade to 64 and keep it all nice and simple.

hi

i have the following, but sql is not able to utilize more than 4 GB…

64 bit machine with 16 GB ram

Windows 2003 server 64 bit enterprise

Sql Server 2005 standard

as mentioned by this post creator, there is lot of info available, but a lot contradicting

You should get your NAV partner involved and have them help you review your system. If your NAV partner does not have the resources to do this, you should find another NAV partner.

On a 64bit machine SQL Server gets normally Physical RAM - 2 GB by Windows defaults, hence about 14GB. How do you know it just allocates/utilizes just 4GB?

With Windows Perfmon you could check the SQL Server Target (= Maximum) and Total (= Currently Used) Memory. In any case I recommend to set the SQL Server “Max Server Memory” to 14000 (=14GB).

If your target is below these 14GB, you’re probably not using SQL Standard Edition but Workgroup or lower.

PS: No, it’s not contradicting - it’s just a slighty complex thingy [;)]

That depends on whose posts you want to believe [;)]

i am using Sql Server 2005 standard 32 bit version…no doubt about it…but my target is below 14 GB

OK, to make it short:

  • Dedicated windows account for sql services
    • Assign rights (local policies): Lock Pages in Memory; Perform Volume Maintenance Tasks
  • Boot.ini: enable /3GB and /PAE switches
  • Restart server
  • Enable AWE in SQL Server
  • Set Server Max. Memory to 14000 MB