SQL Server Performance Forum – Threads Archive

Memory Settings for 3GB RAM

Hi, We’re running a dedicated sql server with 3GB ram. How do I go about allocating the optimal amount of ram for sql to use – only uses up to 2gb at the moment? Thanks.

If you are using SQL server standard version, it can not use more than 2 GB.

It’s Enterprise Edition

What version OS are you running? Are there other instances of SQL Server running?
I recommend you let SQL Server handle memory dynamically, but if you insist you set "max server memory" to the full limit under the server properties tab in SQL Server.
Raulie

Follow as suggested:
:> edit boot.ini Add the switches â€œ/3gb and /paeâ€ to this line as:
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Professional" /fastdetect /3gb /pae 3) Save the file and close the editor 4) c:> attrib +s +h boot.ini 5) Reboot the server Login and check the physical memory using right-click on "My Computer" icon on desktop and choose Properties. Click on General tab. Enabling AWE using the following commands:
Make sure that the user account the SQL Server service is running on is added to "Lock Page in Memory policy" of the "User Rights Assignments" in "Local Security Policy" of the computer. If this is not done, please follow these steps a) Click on Start and choose Programs
b) Choose Administrative Tools, and then select Local Security Policy.
c) Expand Security Settings
d) Expand Local Policies, and then click User Rights Assignments.
e) From the screen on the right, right-click Lock pages in memory,
and then click Security.
f) In the Local Security Policy Setting dialog box, click Add.
g) Click to select the account where the MSSQLSERVER service is running.
h) Click OK.
i) Restart the SQL Server service. k) Start SQL Query Analyzer
l) Use Master from the drop-down list box of database
m) sp_configure ‘show advanced options’, 1 and press F5
(function key F5)
n) RECONFIGURE and press F5 (function key F5)
o) sp_configure ‘awe enabled’, 1
p) RECONFIGURE and press F5 (function key F5)
r) Stop and Re-start all SQL Services (SQLServer, SQLAgent) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It’s also recommended to set a maximum memory size for SQL Server when running with AWE, to stop it starving other processes of RAM. (From BOL:- When awe enabled is set to 1, instances of SQL Server 2000 do not dynamically manage the size of the address space. SQL Server will reserve and lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. It is strongly recommended that you set a value for the max server memory option each time you enable AWE. Otherwise other applications or instances of SQL Server 2000 will have less than 128 MB of physical memory in which to run.
Tom Pullen
DBA, Oxfam GB

Oops.. sorry, Raulie had already mentioned that! Tom Pullen
DBA, Oxfam GB

Excellent work Satya. I would suggest that /3GB looked /userva extension in 2003. It helps a lot for system resources.

]]>