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




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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: