If the server is a dedicated SQL Server machine, then yes. I would set
this value to '1'. If you are going to set your min and max values be
sure to set the 'set working set size' SQL Server option to 1.
Patrick Gallucci
MCT, MCDBA, MCSE, CCIE
www.patrickgallucci.com
214-642-0524
[Email Address Removed]
-----Original Message-----
From: Noel Tan [mailto:[Email Address Removed]
Sent: Monday, December 02, 2002 11:15 PM
To: '[Email Address Removed] 'LazyDBA.com Discussion'
Subject: RE: SQL Server memory
Do you recommend checking the reserve physical memory for SQL Server?
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed]
Sent: Tuesday, December 03, 2002 9:43 AM
To: 'Noel Tan'; 'LazyDBA.com Discussion'
Subject: RE: SQL Server memory
From
http://www.sql-server-performance.com/sql_server_configuration_settings.
asp
For best SQL Server performance, you want to dedicate your SQL Servers
to only running SQL Server, not other applications. And in most cases,
the settings for the "maximum server memory" and the "minimum server
memory" should be left to their default values. This is because the
default values allow SQL Server to dynamically allocate memory in the
server for the best overall optimum performance. If you "hard code" a
minimum or maximum memory setting, you risk hurting SQL Server's
performance.
On the other hand, if SQL Server cannot be dedicated to its own physical
server (other applications run on the same physical server along with
SQL Server) you might want to consider changing either the minimum or
maximum memory values, although this is generally not required, and I
don't recommend it.
Let's take a closer look at each of these two settings.
The "maximum server memory" setting, when set to the default value of
2147483647 (in MB), tells SQL Server to manage the use of memory
dynamically, and if it needs it, to use as much RAM as is available
(while leaving some memory for the operating system).
If you want SQL Server to not use all of the available RAM in the
server, you can manually set the maximum amount of memory SQL Server can
use by specifying a specific number that is between 4 (the lowest number
you can enter) to the maximum amount of RAM in your server (but don't
allocate all the RAM in your server, as the operating system needs some
RAM too).
When "maximum server memory" is set to the default value, as mentioned
before, memory use is adjusted dynamically. What this also means is that
if you are running other applications other than SQL Server on a
physical server, that SQL Server will "play nice" and give up some of
its memory if other applications need the use of sum. So in most cases,
there is no reason to change this setting from its default value. Only
in rare occasions when SQL Server doesn't appear to "play nice," or when
you want to artificially keep SQL Server from using all of the RAM
available to it, would you want to change the default value. For
example, if your "other" application(s) are more important than SQL
Server's performance, then you can restrain SQL Server's performance if
you want.
There are also two potentially performance issues you can create if you
do attempt to set the "maximum server memory" setting manually. First,
if you allocate too much memory to SQL Server, and not enough for the
operating system, then the operating system may have no choice but to
begin excessive paging, which will slow performance of your server.
Also, if you are using the Full-Text Search service, you must also leave
plenty of memory for its use. Its memory is not dynamically allocated
like the rest of SQL Server's memory, and there must be enough available
memory for it to run properly.
The "min server memory" setting, when set to the default value of 0 (in
MB), tells SQL Server to manage the use of memory dynamically. This
means that SQL Server will start allocating memory as is needed, and the
minimum amount of RAM used can vary as SQL Server's needs vary.
If you change the "min server memory" setting to a value other than the
default value of 0, what this means is not that SQL Server will
automatically begin using this amount of minimum memory automatically,
as many people assume, but that once the minimum amount is reached
(because it is needed) that the minimum amount specified will never go
down below the specified minimum.
For example, if you specify a minimum value of 100 (MB), then restart
SQL Server, SQL Server will not immediately reserve 100 MB of RAM for
its minimal use. Instead, SQL Server will only take as much as it needs.
If it never needs 100MB, then it will never be reserved. But if SQL
Server does exceed the 100 MB amount specified, then later it doesn't
need it, then this 100 MB will then become the bottom limit of how much
memory SQL Server allocates. Because of this behavior, there is little
reason to change the "min server memory" setting to any value other than
its default value.
If your SQL Server is dedicated, there is no reason to use the "min
server memory" setting at all. If you are running other applications on
the same server as SQL Server, there might be a very small benefit of
changing this setting to a minimum figure, but it would be hard to
determine what this value should be, and the overall performance
benefits would be negligible.
Hope this helps
Patrick Gallucci
MCT, MCDBA, MCSE, CCIE
www.patrickgallucci.com
214-642-0524
[Email Address Removed]
-----Original Message-----
From: Noel Tan [mailto:[Email Address Removed]
Sent: Monday, December 02, 2002 6:46 PM
To: LazyDBA.com Discussion
Subject: SQL Server memory
Hi all! In SQL Server memory, what is the ideal dynamically
configuration for min(MB) and max(MB) if you have 2 gigabytes of memory?
thanks
MS Sql Server LazyDBA home page