OLTP SQL Memory problem

My OLTP SQL server usually serves ~2700 connections. Twice in the past we've
had memory problem when there is more traffic. In that situation, the number
of connections goes up to 4000. The connections are legitimate and it's been
proved that there are not leaking connections.
I see flood of errors such as the following in the error log. The server has
4GB of memory and /3GB switch is on. SQL server is configured to use the max
memory.
Where do I being to troubleshoot this problem?
Thanks
---
2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
Maximum=245613 Available=245613
2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410 Free=85
Procedures=843
Inram=0 Dirty=23719 Kept=0
I/O=0, Latched=6, Other=303289
2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
Target=338352 Hashed=327015
InternalReservation=516 ExternalReservation=0 Min Free=172
2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
TotalPages=843 InUsePages=389
2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
Reserved=21680
OS Committed=21667
OS In Use=21665
Query Plan=1080 Optimizer=0
General=2578
Utilities=7 Connection=29191
2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
Locks=59
SQLCache=121 Replication=2
LockBytes=2 ServerGlobal=45
Xact=74Kam,
4GB for a system serving 4000 users seems a little low. I don't know how
large your DB is or how much other parts of the memory are being used but
each connection takes up 12 KB + (3 * Network Packet Size). Then you have
all the other objects and the OS that need memory as well. You don't give
any clues as to what kind of memory issues you are having so it's hard to
say if your just low on ram or there is something else going on. I would
suggest you give MS PSS a call and have them work with you directly and they
can suggest some traces and diags to help pinpoint the issue. Without any
other info I would have to say you are probably just short on ram.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faacbff$1@.news.microsoft.com...
> My OLTP SQL server usually serves ~2700 connections. Twice in the past
we've
> had memory problem when there is more traffic. In that situation, the
number
> of connections goes up to 4000. The connections are legitimate and it's
been
> proved that there are not leaking connections.
>
> I see flood of errors such as the following in the error log. The server
has
> 4GB of memory and /3GB switch is on. SQL server is configured to use the
max
> memory.
>
> Where do I being to troubleshoot this problem?
>
> Thanks
>
>
> ---
> 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
> Maximum=245613 Available=245613
> 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
Free=85
> Procedures=843
> Inram=0 Dirty=23719 Kept=0
> I/O=0, Latched=6, Other=303289
> 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> Target=338352 Hashed=327015
> InternalReservation=516 ExternalReservation=0 Min Free=172
> 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> TotalPages=843 InUsePages=389
> 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
> Reserved=21680
> OS Committed=21667
> OS In Use=21665
> Query Plan=1080 Optimizer=0
> General=2578
> Utilities=7 Connection=29191
> 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> Locks=59
> SQLCache=121 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=74
>|||Andrew,
Some more information based on my observation
- Memory usuage is at 95%+ range
- CPU usage however seems normal ~ 45%
- There is no other application except SQL server
- SQL does accept any more connection when this issue occurs
- When I tried to log into the profiler, I got a message saying Memory is
Low
- There was a rolling blocking senario on the server too
4000 * [12,288 + (3 * 4,096)] = 98,304,000
Andrew, ~93 MB of memory doesn't look that much to me considering SQL
controls 3GB on that server.
Thanks,
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> Kam,
> 4GB for a system serving 4000 users seems a little low. I don't know how
> large your DB is or how much other parts of the memory are being used but
> each connection takes up 12 KB + (3 * Network Packet Size). Then you have
> all the other objects and the OS that need memory as well. You don't give
> any clues as to what kind of memory issues you are having so it's hard to
> say if your just low on ram or there is something else going on. I would
> suggest you give MS PSS a call and have them work with you directly and
they
> can suggest some traces and diags to help pinpoint the issue. Without any
> other info I would have to say you are probably just short on ram.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kam" <krajabi@.hotmail.com> wrote in message
> news:3faacbff$1@.news.microsoft.com...
> > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> we've
> > had memory problem when there is more traffic. In that situation, the
> number
> > of connections goes up to 4000. The connections are legitimate and it's
> been
> > proved that there are not leaking connections.
> >
> >
> >
> > I see flood of errors such as the following in the error log. The server
> has
> > 4GB of memory and /3GB switch is on. SQL server is configured to use the
> max
> > memory.
> >
> >
> >
> > Where do I being to troubleshoot this problem?
> >
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> > ---
> >
> > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
Waiting=0
> > Maximum=245613 Available=245613
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> Free=85
> > Procedures=843
> >
> > Inram=0 Dirty=23719 Kept=0
> >
> > I/O=0, Latched=6, Other=303289
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > Target=338352 Hashed=327015
> >
> > InternalReservation=516 ExternalReservation=0 Min Free=172
> >
> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > TotalPages=843 InUsePages=389
> >
> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
OS
> > Reserved=21680
> >
> > OS Committed=21667
> >
> > OS In Use=21665
> >
> > Query Plan=1080 Optimizer=0
> >
> > General=2578
> >
> > Utilities=7 Connection=29191
> >
> > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > Locks=59
> >
> > SQLCache=121 Replication=2
> >
> > LockBytes=2 ServerGlobal=45
> >
> > Xact=74
> >
> >
>|||It may not look like much by itself but it does have to share that with
everything else that takes up memory. If you had blocking issues then you
could also have a ton of locks being held which also use up memory. It may
be the locks that are starting the trouble so you might want to find out why
the blocking happened. But it still sounds like you are right at the edge
of using all the memory effectively for the load and it is likely to happen
again and again.
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faaeda8$1@.news.microsoft.com...
> Andrew,
> Some more information based on my observation
> - Memory usuage is at 95%+ range
> - CPU usage however seems normal ~ 45%
> - There is no other application except SQL server
> - SQL does accept any more connection when this issue occurs
> - When I tried to log into the profiler, I got a message saying Memory is
> Low
> - There was a rolling blocking senario on the server too
> 4000 * [12,288 + (3 * 4,096)] = 98,304,000
> Andrew, ~93 MB of memory doesn't look that much to me considering SQL
> controls 3GB on that server.
> Thanks,
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> > Kam,
> >
> > 4GB for a system serving 4000 users seems a little low. I don't know
how
> > large your DB is or how much other parts of the memory are being used
but
> > each connection takes up 12 KB + (3 * Network Packet Size). Then you
have
> > all the other objects and the OS that need memory as well. You don't
give
> > any clues as to what kind of memory issues you are having so it's hard
to
> > say if your just low on ram or there is something else going on. I
would
> > suggest you give MS PSS a call and have them work with you directly and
> they
> > can suggest some traces and diags to help pinpoint the issue. Without
any
> > other info I would have to say you are probably just short on ram.
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kam" <krajabi@.hotmail.com> wrote in message
> > news:3faacbff$1@.news.microsoft.com...
> > > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> > we've
> > > had memory problem when there is more traffic. In that situation, the
> > number
> > > of connections goes up to 4000. The connections are legitimate and
it's
> > been
> > > proved that there are not leaking connections.
> > >
> > >
> > >
> > > I see flood of errors such as the following in the error log. The
server
> > has
> > > 4GB of memory and /3GB switch is on. SQL server is configured to use
the
> > max
> > > memory.
> > >
> > >
> > >
> > > Where do I being to troubleshoot this problem?
> > >
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> >
> ---
> > >
> > > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
> Waiting=0
> > > Maximum=245613 Available=245613
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> > Free=85
> > > Procedures=843
> > >
> > > Inram=0 Dirty=23719 Kept=0
> > >
> > > I/O=0, Latched=6, Other=303289
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > > Target=338352 Hashed=327015
> > >
> > > InternalReservation=516 ExternalReservation=0 Min Free=172
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > > TotalPages=843 InUsePages=389
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
> OS
> > > Reserved=21680
> > >
> > > OS Committed=21667
> > >
> > > OS In Use=21665
> > >
> > > Query Plan=1080 Optimizer=0
> > >
> > > General=2578
> > >
> > > Utilities=7 Connection=29191
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > > Locks=59
> > >
> > > SQLCache=121 Replication=2
> > >
> > > LockBytes=2 ServerGlobal=45
> > >
> > > Xact=74
> > >
> > >
> >
> >
>|||under perfmon, process object, sqlservr instance, what is
the Virtual Bytes, Working Set,
also get the Virtual Memory size from task manager for the
SqlServr.exe process.
under perfmon SQLServer Buffer Manager, what is the total
pages?
One common problem when SQL Server use 2 or 3GB of memory
is that too much memory and virtual address space gets
allocated to buffers, leaving inadequate address space for
other data structures.
While SQL Server will release buffers to free up memory
for other applications, it does not appear to free up
buffers to make more address space available for other SQL
Server internal requests
By default, SQL reserves 256M of address space for other
than buffers, that's why you frequently see 1.75GB
physical memory used for standard and 2.75GB for /3GB mode,
almost all of the address space is used for data buffers,
and most of the reserved 256M is unused.
in your case, it could be that you need more than 275M but
SQL has already allocated 2.75GB for buffers, and there no
more address space for other uses
provide the above info, but you can also try the -gxxx
startup parameter with say -g384 leaving 384MB for other
stuff
>--Original Message--
>Andrew,
>Some more information based on my observation
>- Memory usuage is at 95%+ range
>- CPU usage however seems normal ~ 45%
>- There is no other application except SQL server
>- SQL does accept any more connection when this issue
occurs
>- When I tried to log into the profiler, I got a message
saying Memory is
>Low
>- There was a rolling blocking senario on the server too
>4000 * [12,288 + (3 * 4,096)] = 98,304,000
>Andrew, ~93 MB of memory doesn't look that much to me
considering SQL
>controls 3GB on that server.
>Thanks,
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
>> Kam,
>> 4GB for a system serving 4000 users seems a little
low. I don't know how
>> large your DB is or how much other parts of the memory
are being used but
>> each connection takes up 12 KB + (3 * Network Packet
Size). Then you have
>> all the other objects and the OS that need memory as
well. You don't give
>> any clues as to what kind of memory issues you are
having so it's hard to
>> say if your just low on ram or there is something else
going on. I would
>> suggest you give MS PSS a call and have them work with
you directly and
>they
>> can suggest some traces and diags to help pinpoint the
issue. Without any
>> other info I would have to say you are probably just
short on ram.
>> http://support.microsoft.com/default.aspx?scid=fh;EN-
US;sql SQL Support
>> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>> --
>> Andrew J. Kelly
>> SQL Server MVP
>>
>> "Kam" <krajabi@.hotmail.com> wrote in message
>> news:3faacbff$1@.news.microsoft.com...
>> > My OLTP SQL server usually serves ~2700 connections.
Twice in the past
>> we've
>> > had memory problem when there is more traffic. In
that situation, the
>> number
>> > of connections goes up to 4000. The connections are
legitimate and it's
>> been
>> > proved that there are not leaking connections.
>> >
>> >
>> >
>> > I see flood of errors such as the following in the
error log. The server
>> has
>> > 4GB of memory and /3GB switch is on. SQL server is
configured to use the
>> max
>> > memory.
>> >
>> >
>> >
>> > Where do I being to troubleshoot this problem?
>> >
>> >
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> > ---
--
>> >
>> > 2003-11-04 13:59:44.12 spid3100 Query Memory
Manager: Grants=0
>Waiting=0
>> > Maximum=245613 Available=245613
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer
Distribution: Stolen=10410
>> Free=85
>> > Procedures=843
>> >
>> > Inram=0 Dirty=23719 Kept=0
>> >
>> > I/O=0, Latched=6, Other=303289
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts:
Commited=338352
>> > Target=338352 Hashed=327015
>> >
>> > InternalReservation=516
ExternalReservation=0 Min Free=172
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache:
TotalProcs=283
>> > TotalPages=843 InUsePages=389
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory
Manager: Stolen=11252
>OS
>> > Reserved=21680
>> >
>> > OS Committed=21667
>> >
>> > OS In Use=21665
>> >
>> > Query Plan=1080 Optimizer=0
>> >
>> > General=2578
>> >
>> > Utilities=7 Connection=29191
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Global Memory
Objects: Resource=2303
>> > Locks=59
>> >
>> > SQLCache=121 Replication=2
>> >
>> > LockBytes=2 ServerGlobal=45
>> >
>> > Xact=74
>> >
>> >
>>
>
>.
>