memory usage??

Hello:
I have a server with almost 4 Gigs of RAM. Though SQL
Server's memory config option allows me to allocate all of
the available memory, I understand that Std. Ed. can only
address upto a max. of 2 GB.
So, I set this option to 2 GB and lo and behold, once the
application floodgates were opened, SQL's target and total
server memory consumption rose to and continues to hover
around the 1.7 GB mark.
At this time, I try to run a 'select' against a view and
after a minute of execution, I get error 701 (There is
insufficient system memory to run this query).
My understanding is that, while SQL will take as much
memory as it can and will probably hold on to it as well,
it will release chunks of memory back to the OS for other
processes. In this though, the process call was from
within SQL Server during a low peak application usage time.
To make my long story short, I'm not sure if SQL Server is
holding on to something, which is avoiding this query to
be executed successfully... OR, if the application has
some lingering processes against SQL, which coul be
causing it grief.
I hope this makes sense. And thank you in advance for all
your responses.to be more precise, SE allows 2GB of address space, which
can be mapped to physical memory, but not always.
by default, s2k allocates 256MB of address space to memory
structures other than the buffer cache. In many cases,
only a small amount of the 256M of address space for other
structures actually gets mapped to physical memory.
hence the common observation of 1.74GB of memory actually
being used by sql svr.
what is your query doing?
does it access a very large number of rows?
does it involve a large amount of intermediate data?
it could be your query requires a very large amount of the
memory structures other than the buffer cache.
does your query run immediately after SQL starts up,
before other users drive up memory usage (to the buffer
cache) ie, does this error message only happen after a
very large amount of memory is used by SQL.
if so, then your app would probably run best on a full 64-
bit OS/APP, the AWE is of no use
>--Original Message--
>Hello:
>I have a server with almost 4 Gigs of RAM. Though SQL
>Server's memory config option allows me to allocate all
of
>the available memory, I understand that Std. Ed. can only
>address upto a max. of 2 GB.
>So, I set this option to 2 GB and lo and behold, once the
>application floodgates were opened, SQL's target and
total
>server memory consumption rose to and continues to hover
>around the 1.7 GB mark.
>At this time, I try to run a 'select' against a view and
>after a minute of execution, I get error 701 (There is
>insufficient system memory to run this query).
>My understanding is that, while SQL will take as much
>memory as it can and will probably hold on to it as well,
>it will release chunks of memory back to the OS for other
>processes. In this though, the process call was from
>within SQL Server during a low peak application usage
time.
>To make my long story short, I'm not sure if SQL Server
is
>holding on to something, which is avoiding this query to
>be executed successfully... OR, if the application has
>some lingering processes against SQL, which coul be
>causing it grief.
>I hope this makes sense. And thank you in advance for all
>your responses.
>.
>

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
SimonHave a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;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.aspx?scid=kb;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.aspx?scid=kb;en-us;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
>>
>|||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...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;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
>>
>>
>|||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...
>> 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.aspx?scid=kb;en-us;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
>>
>>
>>
>|||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/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.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...
>> 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...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;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
>>
>>
>>
>>
>|||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/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.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...
>> 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...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;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
>>
>>
>>
>>
>>
>|||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/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.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...
>> 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/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.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...
>> 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...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;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
>>>
>>>
>>
>>
>>
>>
>>
>