Dynamic memory setting

I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via droptable.com wrote:

>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>
|||Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server

> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:

>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other process
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixed
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better, I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monitor
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>
>
|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:[vbcol=seagreen]
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:

>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship between
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>
>