Windows 2003 - allocate available physical memory

Question

Windows 2003 - allocate available physical memory

irene.leung

Mr or Mrs. 500

Points: 502
More actions
August 16, 2006 at 8:23 pm

#169397

Since Windows 2003 dynamically allocates physical RAM to sql server, does that mean 'available phyical memory' will be dynamically allocated by sql server and the administrator can initially set a small value to this 'available phyical memory' ?
Thanks for any suggestion.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Irene

You can set the max server memory and the min server memory on SQL Server, and SQL Server will allocate itself memory dynamically based on those constraints. The min server memory is 0 by default, and I suppose you could set max server memory to a small value, although I can't think of a reason why you'd want to.

Once you start using AWE (for memory above 4GB) then memory is no longer dynamically allocated.

John

ARUL-285958 Ten Centuries Points: 1081 More actions · Answer 2

also look up for the win version that you run..

Get busy living ....or get busy dying....

Harris-358031 Old Hand Points: 362 More actions · Answer 3

Its not only the property of the Windows Server 2003 to allocate pysical memory depending on the need but also the property of SQL Server to gain or loose memory from the server depending on its need and the requirements of other applications.

There are two memory options for SQL Server max server memory and min server memory.

The max server memory option is the maximum memory that SQL Server can get from the server.

.....The maximum value you can put for it is 64GB(i suppose).

.....The default value is 2147483647.

.....The minimum value for this is 4MB.

The min server memory option is minimum amount of memory allocated by to the SQL Server.

.....The default value is 0.

The main use of these options is to configure/reconfigure the amount of memory(in MB) in the buffer pool used by an instance of SQL Server.

Suman

Harris-358031 Old Hand Points: 362 More actions · Answer 4

When SQL Server is using the memory dynamically it queries the system periodically to get the amount of free phisical space available.

SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity.

If there is less memory free it releases the memory itis holding in the buffer cache to the system.

If there is more memory available it commits to get some memory only when there requirement for the SQL Server.

Typically we use max server memory to prevent the SQL Sever to use some memory for its requirements,not grabing from the system that is used by other applications.

Use of min server memory gaurantees the SQL server to be allocated that amount of memory by the system.

Also SQL Server is not given the amount specified in the min server memory at start up.

And if the load on the server is such that it can't allocate min server memory, the SQL Server runs with low memory.

We cofigure these options using the sp_configure.

Suman/