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?
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.
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.