Maximum memory that SQL 2005 standard can use....

Hi,
We intend to get a new Windows 2003 standard server with 8GB Ram. We will
also be installing SQL 2005 standard on the same system.
We are aware that Windows 2003 standard server supports up to 4GB Ram and
SQL 2005 standard support up to the maximum ram the server has.
Our question is will SQL 2005 standard maximise all the 8GB ram available on
the server even though it is installed on a Windows 2003 standard server
supports only up to 4GB Ram?
Thanks.
Hi
I'm sure it is relevant to SQL Server 2005 too.
http://support.microsoft.com/default...274750&sd=tech
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.
|||On a 32bit Windows standard server you're limited to 3GB application address
space with the /3GB switch in boot.ini
On a 64bit Windows standard server system you're limited to 4GB RAM total
You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
There really isn't any point in putting more than 4GB in a standard server
as it'll never be used
Peter Lawton
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.
|||The x64 version of Windows Server 2003 standard supports 32GB of RAM.
32-bit is 4gb only, so if you are using 32-bit, you will need to get Server
2003 Enterprise. Note that SQL 2005 Standard supports the maximum that the
OS supports, 32-bit or 64-bit.
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.
|||This is not true for the x64 version of Windows Server 2003 Standard. Don't
know about Itanium.
http://support.microsoft.com/KB/888732
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uEV$ceTMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> On a 32bit Windows standard server you're limited to 3GB application
> address space with the /3GB switch in boot.ini
> On a 64bit Windows standard server system you're limited to 4GB RAM total
> You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
> There really isn't any point in putting more than 4GB in a standard server
> as it'll never be used
> Peter Lawton
> "Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
> news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
>

Maximum memory that SQL 2005 standard can use....

Hi,
We intend to get a new Windows 2003 standard server with 8GB Ram. We will
also be installing SQL 2005 standard on the same system.
We are aware that Windows 2003 standard server supports up to 4GB Ram and
SQL 2005 standard support up to the maximum ram the server has.
Our question is will SQL 2005 standard maximise all the 8GB ram available on
the server even though it is installed on a Windows 2003 standard server
supports only up to 4GB Ram?
Thanks.Hi
I'm sure it is relevant to SQL Server 2005 too.
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||On a 32bit Windows standard server you're limited to 3GB application address
space with the /3GB switch in boot.ini
On a 64bit Windows standard server system you're limited to 4GB RAM total
You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
There really isn't any point in putting more than 4GB in a standard server
as it'll never be used
Peter Lawton
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||The x64 version of Windows Server 2003 standard supports 32GB of RAM.
32-bit is 4gb only, so if you are using 32-bit, you will need to get Server
2003 Enterprise. Note that SQL 2005 Standard supports the maximum that the
OS supports, 32-bit or 64-bit.
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||This is not true for the x64 version of Windows Server 2003 Standard. Don't
know about Itanium.
http://support.microsoft.com/KB/888732
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uEV$ceTMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> On a 32bit Windows standard server you're limited to 3GB application
> address space with the /3GB switch in boot.ini
> On a 64bit Windows standard server system you're limited to 4GB RAM total
> You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
> There really isn't any point in putting more than 4GB in a standard server
> as it'll never be used
> Peter Lawton
> "Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
> news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
>> Hi,
>> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
>> also be installing SQL 2005 standard on the same system.
>> We are aware that Windows 2003 standard server supports up to 4GB Ram and
>> SQL 2005 standard support up to the maximum ram the server has.
>> Our question is will SQL 2005 standard maximise all the 8GB ram available
>> on
>> the server even though it is installed on a Windows 2003 standard server
>> supports only up to 4GB Ram?
>> Thanks.
>

Maximum memory that SQL 2005 standard can use....

Hi,
We intend to get a new Windows 2003 standard server with 8GB Ram. We will
also be installing SQL 2005 standard on the same system.
We are aware that Windows 2003 standard server supports up to 4GB Ram and
SQL 2005 standard support up to the maximum ram the server has.
Our question is will SQL 2005 standard maximise all the 8GB ram available on
the server even though it is installed on a Windows 2003 standard server
supports only up to 4GB Ram?
Thanks.Hi
I'm sure it is relevant to SQL Server 2005 too.
http://support.microsoft.com/defaul...;274750&sd=tech
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||On a 32bit Windows standard server you're limited to 3GB application address
space with the /3GB switch in boot.ini
On a 64bit Windows standard server system you're limited to 4GB RAM total
You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
There really isn't any point in putting more than 4GB in a standard server
as it'll never be used
Peter Lawton
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||The x64 version of Windows Server 2003 standard supports 32GB of RAM.
32-bit is 4gb only, so if you are using 32-bit, you will need to get Server
2003 Enterprise. Note that SQL 2005 Standard supports the maximum that the
OS supports, 32-bit or 64-bit.
"Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
> Hi,
> We intend to get a new Windows 2003 standard server with 8GB Ram. We will
> also be installing SQL 2005 standard on the same system.
> We are aware that Windows 2003 standard server supports up to 4GB Ram and
> SQL 2005 standard support up to the maximum ram the server has.
> Our question is will SQL 2005 standard maximise all the 8GB ram available
> on
> the server even though it is installed on a Windows 2003 standard server
> supports only up to 4GB Ram?
> Thanks.|||This is not true for the x64 version of Windows Server 2003 Standard. Don't
know about Itanium.
http://support.microsoft.com/KB/888732
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uEV$ceTMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> On a 32bit Windows standard server you're limited to 3GB application
> address space with the /3GB switch in boot.ini
> On a 64bit Windows standard server system you're limited to 4GB RAM total
> You'd need Windows 2003 Enterprise 32 or 64 bit to use all of your 8GB
> There really isn't any point in putting more than 4GB in a standard server
> as it'll never be used
> Peter Lawton
> "Mousehunt" <Mousehunt@.discussions.microsoft.com> wrote in message
> news:D994E07B-2082-4E4A-A1AF-B18CB9C630F4@.microsoft.com...
>

Maximum databases on SQL Server 2000

We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
This server is being used for heavy analysis by 10 heveyduty workstations.
We have more then 1000 databases on this server.
Should we limit the databases on this server ? we are having issues with
performance and SQL service stopping frequently..
Please advise
Probably. I'd be surprised you can keep all 1000 open and working at the
same time. Memory, disks, bus, etc... are all probably pretty upset with
their deplorable work conditions. :-)
Figure out which of your 1000 databases are most heavily used and move them
out first. Perfmon and Profiler are probably gonna be your new buddies if
they aren't already.
Btw, perf issues aside, 1000 databases in one instance is probably not much
fun to manage. Just expanding the object browser takes a while. What about
maintenance operations? Look up multi instance in BOL and give it some
serious thought. Life will probably be at least a little better that way.
joe.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:37CBC0C7-C1FA-4FE5-9851-B101A5E8E88D@.microsoft.com...
> We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
> This server is being used for heavy analysis by 10 heveyduty
> workstations.
> We have more then 1000 databases on this server.
> Should we limit the databases on this server ? we are having issues with
> performance and SQL service stopping frequently..
> Please advise

Maximum databases on SQL Server 2000

We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
This server is being used for heavy analysis by 10 heveyduty workstations.
We have more then 1000 databases on this server.
Should we limit the databases on this server ? we are having issues with
performance and SQL service stopping frequently..
Please adviseProbably. I'd be surprised you can keep all 1000 open and working at the
same time. Memory, disks, bus, etc... are all probably pretty upset with
their deplorable work conditions. :-)
Figure out which of your 1000 databases are most heavily used and move them
out first. Perfmon and Profiler are probably gonna be your new buddies if
they aren't already.
Btw, perf issues aside, 1000 databases in one instance is probably not much
fun to manage. Just expanding the object browser takes a while. What about
maintenance operations? Look up multi instance in BOL and give it some
serious thought. Life will probably be at least a little better that way.
joe.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:37CBC0C7-C1FA-4FE5-9851-B101A5E8E88D@.microsoft.com...
> We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
> This server is being used for heavy analysis by 10 heveyduty
> workstations.
> We have more then 1000 databases on this server.
> Should we limit the databases on this server ? we are having issues with
> performance and SQL service stopping frequently..
> Please advise

Maximum databases on SQL Server 2000

We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
This server is being used for heavy analysis by 10 heveyduty workstations.
We have more then 1000 databases on this server.
Should we limit the databases on this server ? we are having issues with
performance and SQL service stopping frequently..
Please adviseProbably. I'd be surprised you can keep all 1000 open and working at the
same time. Memory, disks, bus, etc... are all probably pretty upset with
their deplorable work conditions. :-)
Figure out which of your 1000 databases are most heavily used and move them
out first. Perfmon and Profiler are probably gonna be your new buddies if
they aren't already.
Btw, perf issues aside, 1000 databases in one instance is probably not much
fun to manage. Just expanding the object browser takes a while. What about
maintenance operations? Look up multi instance in BOL and give it some
serious thought. Life will probably be at least a little better that way.
joe.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:37CBC0C7-C1FA-4FE5-9851-B101A5E8E88D@.microsoft.com...
> We have a Dell 2650 server with 3 GB of ram ( SQL Staandard edition )
> This server is being used for heavy analysis by 10 heveyduty
> workstations.
> We have more then 1000 databases on this server.
> Should we limit the databases on this server ? we are having issues with
> performance and SQL service stopping frequently..
> Please advise

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
Travis Tan
If this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize Sql Server 2005 Memory.

Hi ,
I just installed my new server with spec show below ,
1. Win 2003 R2 32 bit
2. Sql Server 2005 32 bit
3. 4 Processor 2800 Mhz
4. 8 GB of RAM.
As I know , enable an AWE will maximize the memory usage of the Sql
Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
My intention is simple , is to make sure Sql Server 2005 fully untilize
the memory since this server only have one instance and with no other's
application sharing with it.
Please advise how is the proper configuration. Many thanks.
--
Travis TanIf this is a brand new server you might have been better off going with the
x64bit versions of the OS and SQL Server (assuming the hardware is x64 bit
capable) so AWE would have been unnecessary. But in this case you do need to
turn on AWE and PAE (PAE may be turned on by the OS automatically) for the
OS in order to get the most out of this. Then set the MAX Memory of SQL
Server to no more than 7GB to allow the OS to have enough memory to operate
properly.
--
Andrew J. Kelly SQL MVP
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:EFA5E09C-B8C5-4B9F-B665-B71509CB41C5@.microsoft.com...
> Hi ,
> I just installed my new server with spec show below ,
> 1. Win 2003 R2 32 bit
> 2. Sql Server 2005 32 bit
> 3. 4 Processor 2800 Mhz
> 4. 8 GB of RAM.
> As I know , enable an AWE will maximize the memory usage of the Sql
> Server. Is it true ? Before this , I need to set the "Lock Page Memory" ?
> My intention is simple , is to make sure Sql Server 2005 fully untilize
> the memory since this server only have one instance and with no other's
> application sharing with it.
> Please advise how is the proper configuration. Many thanks.
> --
> Travis Tan

Maximize memory usage on active-active sql cluster

we have an active-active sql cluster configured as follow:
server A - 4gb ram - sql server instance #1
server B - 4gb ram - sql server instance #2
currently each sql instance is configured to use at most 2gb of ram.
in case of failover, both instances switch on one server, effectively
usinge all 4gb without paging.
but it's quite a waste. under normal condition each server is not
using 2gb.
one option we are evaluating is configuring each server to use
min=1gb and max=3gb. and _disabling_ tha paging file.
disabling tha paging file should overcome the problem that, in case of
failover, the two instaces will start paging effectively rendering the
server unusable.
do you think it could work?
do you have any other suggestions?
thanks in advance,
cheers
/ettore
Hi
If you disable the paging file (which you can't as there is always some
kernel code paged out), then the other instance could not fail over as there
would be no RAM for it. So a failover cluster would be useless.
With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
should be your limit per instance to cater for failover.
Regards
Mike
"Ettore Pancini" wrote:

> we have an active-active sql cluster configured as follow:
> server A - 4gb ram - sql server instance #1
> server B - 4gb ram - sql server instance #2
> currently each sql instance is configured to use at most 2gb of ram.
> in case of failover, both instances switch on one server, effectively
> usinge all 4gb without paging.
> but it's quite a waste. under normal condition each server is not
> using 2gb.
> one option we are evaluating is configuring each server to use
> min=1gb and max=3gb. and _disabling_ tha paging file.
> disabling tha paging file should overcome the problem that, in case of
> failover, the two instaces will start paging effectively rendering the
> server unusable.
> do you think it could work?
> do you have any other suggestions?
> thanks in advance,
> cheers
> /ettore
>
|||thanks for answering Mike,
so, let's forget about disable paging. i understand it wont work.
and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
of.
but, from BoL, reading from this page:
Dynamically Managing Memory Between Multiple Instances
exactly from this sentence "Once the amount of memory reaches the
point where only 4 MB to 10 MB is free, the instances begin competing
with each other for memory." and forward on. it seems that when in
competition, instances could free memory based on workload.
so suppose this scenario:
- instance configures as min=1.5GB, max=3.0GB
- normally each intance expand their usage to 3gb
- then one node fails
- instance wich was on the failing node is moved
- the two instances start competing
- the other instance starts to free memory
do you think it could work?
regarding paging, in the same article, BoL states that the dynamic
memory management algorithm will "ensures that the overall amount of
allocated memory remains under the level that would generate paging"
cheers, Ettore

> If you disable the paging file (which you can't as there is always some
> kernel code paged out), then the other instance could not fail over as there
> would be no RAM for it. So a failover cluster would be useless.
> With the /3GB switch, user addressable memory limit is 3GB. The OS keeps 1Gb
> for itself. Even with 2Gb, you are over committing memory. 1.5Gb maximum
> should be your limit per instance to cater for failover.
> Regards
> Mike
>
|||That's true, they can both live on the same node and compete for the
same 3GB of memory (one of my clusters is doing that at the moment in
fact). However, if they're both fairly active instances then data pages
will get swapped in and out of memory more often and the average page
life expectancy will be substantially reduced. My 2 instances are
normally fairly sedate in terms of memory allocation when on separate
nodes. At the moment they're allocating & deallocating pages in memory
like there's no tomorrow.
That's good if what you're after is to use all of your memory all of the
time. But if you're after a nice stable buffer cache then it would be
better not having them compete with each other for the same RAM.
Note bene, if you enable AWE memory in SQL (to allow a virtual address
space of more than 4GB for SQLServer; remember the kernel address space
is alway 1 or 2GB depending upon whether you enable /3GB or not) then
dynamic memory management is automatically turned off. This is because
to use physical memory above 4GB the process needs to lock pages in
memory and cannot swap pages out to disk. This means that SQL Server's
dynamic memory management goes out the window and you have to set a max
server memory limit for all instances that may reside on that node
(presumedly all nodes in the cluster have the same amount of physical
RAM) and the sum of those max server memory settings should be less than
the total physical RAM in presented to the O/S.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Ettore Pancini wrote:
[vbcol=seagreen]
>thanks for answering Mike,
>so, let's forget about disable paging. i understand it wont work.
>and, thanks for pointing out the /3GB switch stuff wich I wasn't aware
>of.
>but, from BoL, reading from this page:
>Dynamically Managing Memory Between Multiple Instances
>exactly from this sentence "Once the amount of memory reaches the
>point where only 4 MB to 10 MB is free, the instances begin competing
>with each other for memory." and forward on. it seems that when in
>competition, instances could free memory based on workload.
>so suppose this scenario:
>- instance configures as min=1.5GB, max=3.0GB
>- normally each intance expand their usage to 3gb
>- then one node fails
>- instance wich was on the failing node is moved
>- the two instances start competing
>- the other instance starts to free memory
>do you think it could work?
>regarding paging, in the same article, BoL states that the dynamic
>memory management algorithm will "ensures that the overall amount of
>allocated memory remains under the level that would generate paging"
>cheers, Ettore
>
>

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server is
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
Henry
This depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server i
s
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
HenryThis depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max. Ram addressed by SQL Server

Dear Sir,
Currently, I have a clustered SQL 2000 Server with 16GB ram on each Win2003
instance. I already enabled the AWE and set the max. memory used by SQL
Server at 14GB. But somebody said the max. ram can addressed by SQL Server is
less than 14GB actually, is that true? If yes, what is the max. ram can be
addressed by SQL 2000?
Also, I am planning to upgrade the platform to 64-bit Win2003 too, then what
is the max. ram can be addressed by SQL 2000 64-bit version?
Thanks a lot!
From,
HenryThis depends on the SQL Server edition and OS edition used
Windows Server 2003, Standard Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 3GB
Windows Server 2003, Enterprise Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 32GB
Windows Server 2003, Datacenter Edition (32-bit)
SQL Server 2000, Standard Edition (SE) 2GB
SQL Server 2000, Enterprise Edition (EE) 64GB
Windows Server 2003, Enterprise Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 64GB
Windows Server 2003, Datacenter Edition (64-bit)
SQL Server 2000 EE, 64-bit Edition 512GB
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:D3BFB45F-5B70-4905-A452-5110644C1191@.microsoft.com...
> Dear Sir,
> Currently, I have a clustered SQL 2000 Server with 16GB ram on each
> Win2003
> instance. I already enabled the AWE and set the max. memory used by SQL
> Server at 14GB. But somebody said the max. ram can addressed by SQL Server
> is
> less than 14GB actually, is that true? If yes, what is the max. ram can be
> addressed by SQL 2000?
> Also, I am planning to upgrade the platform to 64-bit Win2003 too, then
> what
> is the max. ram can be addressed by SQL 2000 64-bit version?
> Thanks a lot!
> From,
> Henry

Max Worker Threads and User Connections

Hi

we run SQL 2005 Enterprise on W2003 Enterprise 32 bit on 8 dual core Xeon 7020 with 32Gb RAM.

The max worker thread settings is 0 now. But 3d party consultancy which monitors our servers on the POP is suggesting to make this figure to be 1200. They said - there are 1000 connections at peak time. I am going to check if it is correct...

But my question is - is it proper thing to do to set worker thread number that high? (BOL says - MS doesnt recommend more than 1024 on 32 bit systems)

Is it that useful to match number of connections with number of threads?

Thank you

P.S. that consultancy let us down several times. I dont trust then at all.. I need a real reason to change such a thing

Well, I probably wouldn't recommend it generally speaking.

First off, just because there are 1000 connections at a given time, definately doesn't imply that there are 1000 concurrent processing requests...connections are frequently idle in many types of systems processing 'think' time by end users, or just sitting idle in a connection pool (.NET or otherwise). If you are using connection pooling, I'm sure you'll find that many of your connections are idle a fair amount of the time...

In a 32-bit system, on startup Sql server will reserve enough memory to load it's own binaries in, then some to handle 8kb and greater memory requests (typically 128mb by default, but can be modified using a startup switch), then enough memory to be able to allocate stacks for the total number of threads possible to use, which is determined by your 'max worker thread' setting.

Each thread stack is uses about .5mb of memory, which means using a setting of 1200 will result in a reserved space of about 600mb for just stack allocation on a 32bit box. Bear in mind that on a 32bit box of this size (assuming you aren't using a /3gb or /USERVA switch...if you are you ain't going to get to use most of your physical memory), the maximum VAS for a user-mode process like Sql server is 2gb, so stack space is going to use over a 1/4 of that space alone.

By default, in Sql 2005 on a 32bit box, Sql Server will dynamically handle threading using a simple algorithm where with 4 procs or less, 256 max threads will be used, then an additional 8 threads per CPU...in the case of a 16-way machine (yours is an 8-way dual core, 16 schedulers), Sql will use a max thread dynamic setting of 352 on that machine. If you bump that to 1200, your allocating about 850 more than the dyamic configuration would.

Given what I mentioned earlier about the fact that many connections are typically idle a fair amount of time, connections will pool the thread use at the OS level, and typically the dynamic default configuration is hightly recommend.

I'd ask the consultants to provide you extensive documentation on why they think it would be beneficial...typically, recommending something like this would take some extensive testing, and intimate knowledge of a system...

HTH,

|||

Thank you Chad,

I have same ideas but just needed some authoritative information to argue with the consulters.

I really hate 'solutions' made from thin air. I refused to do any changes and requested concrete measures have been made before they decided on the advice. I think they have none.

Might be there is a chance they are right (that advice was given to resolve issues I addressed in my previous topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1). Fortunately these accidents havent repeated since I went through the application and put some NOLOCKs into critical places..

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA Poseur
Hmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
--
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

Max Woprked threads

We consistently have between 550 and 600 connections. The server has 8gb of ram and is a dediocated sql server (advanced and enterpirse editions so awe has been enabled) would performance inpro9ve if the mas threats is set to say 550?
Moses
I prefer using a default value of the max worker threads. Microsoft states
that it is best for most systems.
Before you changed the default value did you recieve the message from SQL
Server "The working thread limit of 255 has been reached."?
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?
|||You don't need anywhere near as many worker threads as you have users.
Unless you are seeing entries in the error log stating you are running out
of worker threads I would not touch that setting.
Andrew J. Kelly SQL MVP
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?

Max Woprked threads

We consistently have between 550 and 600 connections. The server has 8gb of ram and is a dediocated sql server (advanced and enterpirse editions so awe has been enabled) would performance inpro9ve if the mas threats is set to say 550?Moses
I prefer using a default value of the max worker threads. Microsoft states
that it is best for most systems.
Before you changed the default value did you recieve the message from SQL
Server "The working thread limit of 255 has been reached."?
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?|||You don't need anywhere near as many worker threads as you have users.
Unless you are seeing entries in the error log stating you are running out
of worker threads I would not touch that setting.
--
Andrew J. Kelly SQL MVP
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?