Memory usage

Hi,
We have a server with 4gb of memory, the server is configured to dynamically
allocate memory to the max yet consistently only uses 2gb.
We are using enterprise edition on 2k advanced server. Am I missing
something? Is there something else limiting the server to 2gb?
Any pointers appreciated
Ta
Simon
Have a look at
http://support.microsoft.com/default...274750&sd=tech
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>
|||See if the following link helps:
http://support.microsoft.com/default...b;en-us;274750
"skilla31" wrote:

> Hi,
> We have a server with 4gb of memory, the server is configured to dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>
>
|||Wow,
Thanks Uri. Does this really mean that by default sql server will only use
2GB? Unless you use AWE
The devil really is in the detail
Simon
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Have a look at
> http://support.microsoft.com/default...274750&sd=tech
>
>
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> dynamically
>
|||Set the /3GB switch in the Boot.ini file and it will be able to use 3GB. It
is usually best to leave the other 1GB for the OS and any other apps thay
may run on the server. You can use AWE to use some of the memory in between
3 and 4GB but it is usually not worth the effort for a few hundred MB's.
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
> Wow,
> Thanks Uri. Does this really mean that by default sql server will only use
> 2GB? Unless you use AWE
> The devil really is in the detail
> Simon
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>
|||This is true for ALL Windows server apps (excluding 64 bit servers.)
the OS for standard servers has 4 GB memory of which a max of 2 GB can be
allocated to ANY program..., so this is not a SQL issue, but an OS issue...
We can use the /3GB boot. ini switch and/or the AWE extensions to increase
memory...
64 bit editiions of the OS will open the door to greater memory ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
> dynamically allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>
|||Thanks Andrew,
We are using advanced server. I'm thinking of upgrading the server to 6GB
and allowing SQL Server to use up to 5GB.
So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is this
feasible
Simon
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
> It is usually best to leave the other 1GB for the OS and any other apps
> thay may run on the server. You can use AWE to use some of the memory in
> between 3 and 4GB but it is usually not worth the effort for a few hundred
> MB's.
> --
> Andrew J. Kelly SQL MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>
|||No that is not how it works<g>. You set the /3GB to allow sql server to
access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top of
that to gain access to memory above 4GB and that would leave 1GB for the OS.
So you need to do this:
Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
Set /PAE in Boot.ini (allows the OS to use more than 4GB)
Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
4GB.)
Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
it is not dynamic with AWE so you leave 1 GB for the OS)
http://sqljunkies.com/Tutorial/0D4FF...2FE2D58F6.scuk
Memory
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
> Thanks Andrew,
> We are using advanced server. I'm thinking of upgrading the server to 6GB
> and allowing SQL Server to use up to 5GB.
> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
> this feasible
> Simon
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>
|||Thanks again Andrew,
Sorry for being so dim, I think I've got this. So based on my desired
configuration (6 GB with 5GB available to sql server and 1GB left for the
OS)
I would
1. set the following switches in the boot.ini /3GB /PAE
2. Then run
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 5120
RECONFIGURE
GO
Interestingly on my test system I think max memory is displayed in KB, when
I try to set max memory in kb above 2GB I get a conversion error (decimal to
int). When I then set max memory in mb and run sp_configure it displays it
mb. Which makes me nervous.
Although I'm almost certain memory is the bottleneck can you point me in the
right direction for articles on memory sizing for db servers
Ta
Simon
PS: didn't get the "<g>" in previous message
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> No that is not how it works<g>. You set the /3GB to allow sql server to
> access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top
> of that to gain access to memory above 4GB and that would leave 1GB for
> the OS. So you need to do this:
> Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
> Set /PAE in Boot.ini (allows the OS to use more than 4GB)
> Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
> 4GB.)
> Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
> it is not dynamic with AWE so you leave 1 GB for the OS)
>
> http://sqljunkies.com/Tutorial/0D4FF...2FE2D58F6.scuk
> Memory
> http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
> http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000
> memory usage
> http://www.support.microsoft.com/?id=274750 Memory config
> http://www.support.microsoft.com/?id=283037 Large Memory Support
> Is Available in Windows 2000 (AWE)
> http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
> memory
>
> --
> Andrew J. Kelly SQL MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
>
|||Here are some links related to setting memory but there isn't much you can
do other than determine how much SQL Server will use. Basically give it all
you have after leaving some for the OS. If you run any other apps on the
machine you must account for them as well. If memory is your bottleneck you
most likely have poorly tuned queries or improperly indexed tables. Running
some traces should help to determine that pretty quickly.
http://sqljunkies.com/Tutorial/0D4FF...2FE2D58F6.scuk
Memory
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:u68zCw2OFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Thanks again Andrew,
> Sorry for being so dim, I think I've got this. So based on my desired
> configuration (6 GB with 5GB available to sql server and 1GB left for the
> OS)
> I would
> 1. set the following switches in the boot.ini /3GB /PAE
> 2. Then run
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 5120
> RECONFIGURE
> GO
> Interestingly on my test system I think max memory is displayed in KB,
> when I try to set max memory in kb above 2GB I get a conversion error
> (decimal to int). When I then set max memory in mb and run sp_configure it
> displays it mb. Which makes me nervous.
> Although I'm almost certain memory is the bottleneck can you point me in
> the right direction for articles on memory sizing for db servers
> Ta
> Simon
> PS: didn't get the "<g>" in previous message
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
>