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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: