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.
>.
>