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 Limits

What is the difference between the MemoryLimit and MaximumMemoryLimit
settings in the RSReportServer.config file?These settings are used by the windows service. They are percentages of
physical memory so they can be larger then 100%. When the process is using
the MemoryLimit amount of physical memory it will no longer take on any work
until it can reclaim memory. If the current work continues to grow memory
past MaximumMemoryLimit, then RS will stop executing all work and reclaim
its memory.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Siebert" <DavidSiebert@.discussions.microsoft.com> wrote in message
news:1C37D3BF-15D2-4FA9-9342-F3E7683EF0C1@.microsoft.com...
> What is the difference between the MemoryLimit and MaximumMemoryLimit
> settings in the RSReportServer.config file?

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
Thierry
First off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>
|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Even
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>
|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.
|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]
|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
--
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Andrew J. Kelly wrote:
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
>> Thanks,
>> But I use SpotLigth to monitor my server and if I limit the sql
>> server memory to 500 MB, It use 500 MB, afterv that I set no limit
>> and the memory grow up to 1.24 GB never upper.
>> Why ?
>> Thanks
>> "Andrew J. Kelly" wrote:
>> First off what are you using to determine the memory? DOn't use
>> task manager use Perfmon and the proper SQL Server memory counters.
>> SQL Server will only use memory if it needs to. It simply may not
>> need all 2GB. Even so with 2GB you likely to only see about
>> 1.75GB's used for SQL Server anyway.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
>> message news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>> HI,
>> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2
>> cpu, 2 GB of ram
>> The memory is configuring as dynamic, no max, no min
>> So, my question is :
>> Why the sql server use only 1.24 GB of ram et no 2 GB ?
>> Thanks a lot
>> Regards
>> Thierry
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
> > First off what are you using to determine the memory? DOn't use task
> > manager use Perfmon and the proper SQL Server memory counters. SQL
Server
> > will only use memory if it needs to. It simply may not need all 2GB.
Even
> > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > anyway.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
message
> > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > HI,
> > >
> > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
2 GB
> > > of ram
> > > The memory is configuring as dynamic, no max, no min
> > >
> > > So, my question is :
> > >
> > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > >
> > > Thanks a lot
> > >
> > > Regards
> > >
> > > Thierry
> > >
> >
> >
> >|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:
> use Perfmon / Process / Private Bytes to see if there are any other non-sql
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> > Thanks,
> >
> > But I use SpotLigth to monitor my server and if I limit the sql server
> > memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> > grow up to 1.24 GB never upper.
> >
> > Why ?
> >
> > Thanks
> >
> > "Andrew J. Kelly" wrote:
> >
> > > First off what are you using to determine the memory? DOn't use task
> > > manager use Perfmon and the proper SQL Server memory counters. SQL
> Server
> > > will only use memory if it needs to. It simply may not need all 2GB.
> Even
> > > so with 2GB you likely to only see about 1.75GB's used for SQL Server
> > > anyway.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message
> > > news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> > > > HI,
> > > >
> > > > My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu,
> 2 GB
> > > > of ram
> > > > The memory is configuring as dynamic, no max, no min
> > > >
> > > > So, my question is :
> > > >
> > > > Why the sql server use only 1.24 GB of ram et no 2 GB ?
> > > >
> > > > Thanks a lot
> > > >
> > > > Regards
> > > >
> > > > Thierry
> > > >
> > >
> > >
> > >
>
>

Memory limited to 1.24 GB

HI,
My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
of ram
The memory is configuring as dynamic, no max, no min
So, my question is :
Why the sql server use only 1.24 GB of ram et no 2 GB ?
Thanks a lot
Regards
ThierryFirst off what are you using to determine the memory? DOn't use task
manager use Perfmon and the proper SQL Server memory counters. SQL Server
will only use memory if it needs to. It simply may not need all 2GB. Even
so with 2GB you likely to only see about 1.75GB's used for SQL Server
anyway.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
> HI,
> My config is Windows 2000 entreprise, sqlserver 2000 standard, 2 cpu, 2 GB
> of ram
> The memory is configuring as dynamic, no max, no min
> So, my question is :
> Why the sql server use only 1.24 GB of ram et no 2 GB ?
> Thanks a lot
> Regards
> Thierry
>|||Thanks,
But I use SpotLigth to monitor my server and if I limit the sql server
memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
grow up to 1.24 GB never upper.
Why ?
Thanks
"Andrew J. Kelly" wrote:

> First off what are you using to determine the memory? DOn't use task
> manager use Perfmon and the proper SQL Server memory counters. SQL Server
> will only use memory if it needs to. It simply may not need all 2GB. Eve
n
> so with 2GB you likely to only see about 1.75GB's used for SQL Server
> anyway.
> --
> Andrew J. Kelly SQL MVP
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3EDFDC58-470E-40DE-A970-21BC3ADEC1A4@.microsoft.com...
>
>|||Are you absolutely sure you have as much memory as you think? I don't know
for sure how Spotlight is measuring memory so I can't comment on that part.
My guess you have other apps runing on that server that take up a certain
amount of ram as well or SQL just does not need more than that.
Andrew J. Kelly SQL MVP
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Andrew J. Kelly wrote:[vbcol=seagreen]
> Are you absolutely sure you have as much memory as you think? I
> don't know for sure how Spotlight is measuring memory so I can't
> comment on that part. My guess you have other apps runing on that
> server that take up a certain amount of ram as well or SQL just does
> not need more than that.
>
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in
> message news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
I agree with Andrew. Whatever algorithm SQL Server uses to determine how
much system memory it can grab is probably coming up the 1.24GB number
on that particular server.
For a server, having 700MB or so dedicated to server functions doesn't
sound unreasonable.
David G.|||use Perfmon / Process / Private Bytes to see if there are any other non-sql
processes using a large amount of memory.
cheers,
Andy.
"Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in message
news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...[vbcol=seagreen]
> Thanks,
> But I use SpotLigth to monitor my server and if I limit the sql server
> memory to 500 MB, It use 500 MB, afterv that I set no limit and the memory
> grow up to 1.24 GB never upper.
> Why ?
> Thanks
> "Andrew J. Kelly" wrote:
>
Server[vbcol=seagreen]
Even[vbcol=seagreen]
message[vbcol=seagreen]
2 GB[vbcol=seagreen]|||Hi,
Thanks a lot all for your response.
In fact, the server have 500 MB RAM free in all times, sqlserver reserved
1.24 GB and the other process reserved 250 MB.
Why ?
"Andy Ball" wrote:

> use Perfmon / Process / Private Bytes to see if there are any other non-sq
l
> processes using a large amount of memory.
> cheers,
> Andy.
> "Thierry Bertin" <ThierryBertin@.discussions.microsoft.com> wrote in messag
e
> news:3C20EE66-E1F2-4DC7-85A9-DC101FE57E77@.microsoft.com...
> Server
> Even
> message
> 2 GB
>
>