request advice on judging memory usage/allocation

Hello,
I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
with the following specs:
Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
license
2.8 GHz Xeon, 2 GB RAM, about 2 years old
Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
size < 5 GB? I don't know exactly.
Other apps are the OS, some Access MDBs accessed by Access front ends
via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
migrated to SQL Server, the Abra client is on Citrix servers.
We have a monitoring system through our support consultant. This system
tells us now and then that SQL has used up all its physical memory, but
these alerts occur at odd times like midnight on a Sunday (to be fair,
this is around the time when SQL does its maintenance, backup, etc), or
540 pm when the main GP users have all gone home and are not doing
anything in GP. I don't know yet if SQL is paging a lot, but no
specific end user complaints have appeared yet, so we have time to be
pro-active.
I've recently found some information on which PerfMon items to check,
and will try these.
My questions:
1. If we install 4 GB of RAM do I need to learn about doing anything
special like set PAE or AWE parameters or boot.ini parameters or
anything' Most of the MS links I saw on the list are for Windows 2000
Server, not Windows 2003 Server.
2. If we put in 4 GB of RAM, it seems to me that setting SQL to 2.5 GB
max and leaving the remainder for the OS and Abra Suite would work
until Abra Suite's DB is upgraded to SQL 2005, in which case I would
change it to 3 GB for SQL and the remaining 1 GB for the OS and IIS,
which is our most likely upgrade path. Does this make sense'
Right now SQL is set to dynamic memory allocation and I would like to
leave it this way if possible but I want to learn about preventing
future issues down the road related to virtual or physical memory,
especially to prevent paging, this server has to last a while!!
3. Any other forum threads or websites or advice would be most welcome.
Thank you for reading this,
Tom"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||Thank you for responding...I put in two more short questions below.
David Browne wrote:

> No. In a small setup like this SQL Server will use < 2GB of ram (that's S
QL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the othe
r
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.

> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't instal
l
> more ram, you should change SQL Server's max memory setting to leave enoug
h
> memory for the other applications to run..SQL Server will run just fine wi
th
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go t
o
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom|||Thank you for responding...I put in two more short questions below.
David Browne wrote:

> No. In a small setup like this SQL Server will use < 2GB of ram (that's S
QL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the othe
r
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.

> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't instal
l
> more ram, you should change SQL Server's max memory setting to leave enoug
h
> memory for the other applications to run..SQL Server will run just fine wi
th
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go t
o
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom