I have read that SQL Server tries to keep atleast 4MB of
memory free for the OS. For a system with 4GB RAM, SQL
Server would use around 2GB and keep the remaining for
the OS. In that case, does it still try to keep just 4MB
free when the OS has 2GB of RAM to use ?
Also when the system has just 2GB of RAM, how much will
SQL try to reserve in this situation ? Please comment as
im a bit confusedHi!
Check the SQL 2000 Operations Guide how to set up memory and other things -
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops4.asp.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"AnonymousMan" <anonymous@.discussions.microsoft.com> wrote in message
news:048901c39b7d$1c6ba5e0$a401280a@.phx.gbl...
> I have read that SQL Server tries to keep atleast 4MB of
> memory free for the OS. For a system with 4GB RAM, SQL
> Server would use around 2GB and keep the remaining for
> the OS. In that case, does it still try to keep just 4MB
> free when the OS has 2GB of RAM to use ?
> Also when the system has just 2GB of RAM, how much will
> SQL try to reserve in this situation ? Please comment as
> im a bit confused
Showing posts with label memory. Show all posts
Showing posts with label memory. Show all posts
Wednesday, March 21, 2012
reserver memory should be fixed or dynamic?
Hello -
I want to use the reserve physical memory option to get test if this will
give me any added (faster) performance on a database.
If I use 'dynamic' with 'reserve physical memory', is the maximum amount of
memory allocated or minimum?
Should I use fixed with 'reserve' to set aside as much memory instead?
I guess I'm not sure what 'dynamic' with 'reserve physical' means. Any
clues?
Thanks,
bruceHave a look at BOL for "set working set size". You want to use a fixed
amount
"Do not set set working set size if you are allowing SQL Server to use
memory dynamically. Before setting set working set size to 1, set both min
server memory and max server memory to the same value, the amount of memory
you want SQL Server to use"
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"NO.bruceradtke.SPAM@.NO.earthlink.net.SPAM"
<bruceradtke@.NO.earthlink.net.SPAM> wrote in message
news:v3%Hb.14005$IM3.12306@.newsread3.news.atl.earthlink.net...
> Hello -
> I want to use the reserve physical memory option to get test if this will
> give me any added (faster) performance on a database.
> If I use 'dynamic' with 'reserve physical memory', is the maximum amount
of
> memory allocated or minimum?
> Should I use fixed with 'reserve' to set aside as much memory instead?
> I guess I'm not sure what 'dynamic' with 'reserve physical' means. Any
> clues?
>
> Thanks,
> bruce
>
I want to use the reserve physical memory option to get test if this will
give me any added (faster) performance on a database.
If I use 'dynamic' with 'reserve physical memory', is the maximum amount of
memory allocated or minimum?
Should I use fixed with 'reserve' to set aside as much memory instead?
I guess I'm not sure what 'dynamic' with 'reserve physical' means. Any
clues?
Thanks,
bruceHave a look at BOL for "set working set size". You want to use a fixed
amount
"Do not set set working set size if you are allowing SQL Server to use
memory dynamically. Before setting set working set size to 1, set both min
server memory and max server memory to the same value, the amount of memory
you want SQL Server to use"
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"NO.bruceradtke.SPAM@.NO.earthlink.net.SPAM"
<bruceradtke@.NO.earthlink.net.SPAM> wrote in message
news:v3%Hb.14005$IM3.12306@.newsread3.news.atl.earthlink.net...
> Hello -
> I want to use the reserve physical memory option to get test if this will
> give me any added (faster) performance on a database.
> If I use 'dynamic' with 'reserve physical memory', is the maximum amount
of
> memory allocated or minimum?
> Should I use fixed with 'reserve' to set aside as much memory instead?
> I guess I'm not sure what 'dynamic' with 'reserve physical' means. Any
> clues?
>
> Thanks,
> bruce
>
Friday, March 9, 2012
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|||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 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.
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 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.
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
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|||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 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.
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 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.
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
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
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
Subscribe to:
Posts (Atom)