Memory configuration of SQL-Server 2005:

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

RECONFIGURE

GO

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;

RECONFIGURE;

GO

sp_configure 'max server memory', 9144

RECONFIGURE

GO

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