Memory configuration of SQL-Server 2005:
All of you knew that, now a day, hardware cost is going down and down, expectation of performance is going up and up. Today, even personal computers are having more than 2GB memory, SQL-Server by default uses 2GB memory but there is a way you can use more memory for SQL-Server and release it whenever you wish and re-allocate it to OS,
Cause memory management is set to dynamic in SQL-Server 2005.
Thanks to AWE application which is a heart of memory management. First of all you will have to make this application enable by following setting.
sp_configure ‘awe enabled’, 1
Value “1” is for enabling AWE and “0” is for disabling. BTW, “0” is default value.
Once, you enable AWE option. You can set minimum and maximum memory for SQL-Server by following script.
SP_CONFIGURE ‘min server memory’, 256;
sp_configure ‘max server memory’, 9144
Please be informed that, you have to give value in MBs in above commands. You can get more information about AWE from latest SQL-Server book online, you can download it from Microsoft website, topic is “MIN AND MAX SERVER MEMORY”.
Reference: Ritesh Shah