Memory Leak problem... in SQL Server 2K

[Ryan]

Hello,

I am having trouble with a production db server that likes to gobble
up memory. It seems to be a slow burn (maxing out over about an 18
hour time frame, before pegging both procs on the server and bringing
everything to a standstill). After viewing the trace logs, it appears
that all the SPIDs are being recycled - does this assert that
connections are being properly closed when the need for them has
ended? The code base is huge and quite messy, so it's difficult to
discern where the problem is just by looking at code, and we can't
seem to nail it down by looking at it, and I'm not sure what to look
for in the trace logs or perfmon.

Does anyone have any suggestions about what else might cause such a
problem?

Ryan

[Erland Sommarskog]

Ryan (ry******@excite.com) writes:

I am having trouble with a production db server that likes to gobble
up memory. It seems to be a slow burn (maxing out over about an 18
hour time frame, before pegging both procs on the server and bringing
everything to a standstill). After viewing the trace logs, it appears
that all the SPIDs are being recycled - does this assert that
connections are being properly closed when the need for them has
ended? The code base is huge and quite messy, so it's difficult to
discern where the problem is just by looking at code, and we can't
seem to nail it down by looking at it, and I'm not sure what to look
for in the trace logs or perfmon.

SQL Server likes to gobble up memory. In fact this is by design. The
more data SQL Server can hold in cache, the more queries it can
respond to without disk access. So normally SQL Server expands to
get all avilable memory. But if there are other processes in need of
memory, SQL Server will yield. It may not yield fast enough, though,
and you can configure SQL Server to use only part of the memory.

So the perceived memory leak is not a problem, but since you talk about
standstill, it seems that you have a problem. And since you talk about
pegging the processors on the server, it seems that you have a query in
need of rewrite somewhere. Or a in need of a better index. So while that
code base may be big and ugly, and you prefer not to look at it, it is
most likely there you find the solution.

The Profiler is a good tool. Filter for Duration greeater than, say,
1000 ms. Then again, if you start tracing when that bad query starts
running, you will not see the query until it is completed. One
alternative is aba_lockinfo, which is on my home page,
http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure
is really intended for lock monitoring, but you get all active processes
and what they are doing. And since "standstill" often includes blocking
as well, it may be interesting. aba_lockinfo gives you a snapshot, but
can still reveal something about what is going on. One word of caution
though: aba_lockinfo can take some time to return on a busy system. I
have identiefied a few weaknesses in terms of performance, but I have
not came around to fix them yet.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Pippen]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Ryan (ry******@excite.com) writes:
<snip>
SQL Server likes to gobble up memory. In fact this is by design. The
more data SQL Server can hold in cache, the more queries it can
respond to without disk access. So normally SQL Server expands to
get all avilable memory. But if there are other processes in need of
memory, SQL Server will yield. It may not yield fast enough, though,
and you can configure SQL Server to use only part of the memory.

So the perceived memory leak is not a problem, but since you talk about
standstill, it seems that you have a problem. And since you talk about
pegging the processors on the server, it seems that you have a query in
need of rewrite somewhere. Or a in need of a better index. So while that
code base may be big and ugly, and you prefer not to look at it, it is
most likely there you find the solution.

The Profiler is a good tool. Filter for Duration greeater than, say,
1000 ms. Then again, if you start tracing when that bad query starts
running, you will not see the query until it is completed. One
alternative is aba_lockinfo, which is on my home page,
http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure
is really intended for lock monitoring, but you get all active processes
and what they are doing. And since "standstill" often includes blocking
as well, it may be interesting. aba_lockinfo gives you a snapshot, but
can still reveal something about what is going on. One word of caution
though: aba_lockinfo can take some time to return on a busy system. I
have identiefied a few weaknesses in terms of performance, but I have
not came around to fix them yet.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I agree with every thing above... in the intrim you may want to limit the
amout no memory that MS SQL can have. This helps especially if you are
running other programs on the machine that compete for memory. The downside
is that MSSQL has less to work with and will possibly take longer. Upside is
that MSSQL will not gobble up all the memory bringing everything to a halt.

-p

[Chuck Conover]

Absolutely. You should limit the database memory even if you have no other
applications running on the machine. Strangely enough, SQL Server can choke
the operating system by leaving too little memory for the OS to function at
optimum.
Hope this helps,
Chuck Conover
www.TechnicalVideos.net

"Pippen" <12*@hotmail.com> wrote in message news:bw0Rb.152962$na.259030@attbi_s04...
I agree with every thing below... in the intrim you may want to limit the
amout no memory that MS SQL can have. This helps especially if you are
running other programs on the machine that compete for memory. The downside is that MSSQL has less to work with and will possibly take longer. Upside is that MSSQL will not gobble up all the memory bringing everything to a halt.
-p

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Ryan (ry******@excite.com) writes:

<snip>

SQL Server likes to gobble up memory. In fact this is by design. The
more data SQL Server can hold in cache, the more queries it can
respond to without disk access. So normally SQL Server expands to
get all avilable memory. But if there are other processes in need of
memory, SQL Server will yield. It may not yield fast enough, though,
and you can configure SQL Server to use only part of the memory.

So the perceived memory leak is not a problem, but since you talk about
standstill, it seems that you have a problem. And since you talk about
pegging the processors on the server, it seems that you have a query in
need of rewrite somewhere. Or a in need of a better index. So while that
code base may be big and ugly, and you prefer not to look at it, it is
most likely there you find the solution.

The Profiler is a good tool. Filter for Duration greeater than, say,
1000 ms. Then again, if you start tracing when that bad query starts
running, you will not see the query until it is completed. One
alternative is aba_lockinfo, which is on my home page,
http://www.sommarskog.se/sqlutil/aba_lockinfo.html. That procedure
is really intended for lock monitoring, but you get all active processes
and what they are doing. And since "standstill" often includes blocking
as well, it may be interesting. aba_lockinfo gives you a snapshot, but
can still reveal something about what is going on. One word of caution
though: aba_lockinfo can take some time to return on a busy system. I
have identiefied a few weaknesses in terms of performance, but I have
not came around to fix them yet.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Chuck Conover (cc******@commspeed.net) writes:

Absolutely. You should limit the database memory even if you have no
other applications running on the machine. Strangely enough, SQL Server
can choke the operating system by leaving too little memory for the OS
to function at optimum.

No, for a machine that only runs SQL Server, there is no reason to configure
the memory. The most likely result is that when you buy more memory, you
cannot understand why it does not pay off, because you had forgotten that
you had constrained the memory.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Chuck Conover" <cc******@commspeed.net> wrote in message
news:10***************@news.commspeed.net...

Absolutely. You should limit the database memory even if you have no other applications running on the machine. Strangely enough, SQL Server can choke the operating system by leaving too little memory for the OS to function at optimum.

I'd have to disagree. I've never seen this be an issue.

[Chuck Conover]

Greg,
No problem to disagree. It is possible that I came to the wrong
conclusion. However, I did see a situation just recently whereby there were
several views (written very badly) that required the database to bring back
several million rows. The I/O was astronomical. It appeared that even
after the view had come back, the whole machine was incredibly slow, and our
diagnostics showed that the database had eaten almost all 2GB of memory on
the machine. So our assumption was that the OS did not have enough memory
to function optimally. Rebooting the machine was the short-term fix. Being
a production server, we made 3 fixes simultaneously to get the machine
working properly as quickly as possible. Correcting the views, adding
another 2GB of memory, and limiting the DB memory fixed the problem, but we
aren't sure which one of our fixes corrected the problem.

Thanks for the input. It is possible that the views did not ever finish
completely considering the I/O required. That could have been the reason
for the slowdown of the machine.

Best regards,
Chuck Conover
www.TechnicalVideos.net


"Greg D. Moore (Strider)" <mo****************@greenms.com> wrote in message
news:Sg***********@twister.nyroc.rr.com...

"Chuck Conover" <cc******@commspeed.net> wrote in message
news:10***************@news.commspeed.net...

Absolutely. You should limit the database memory even if you have no

other

applications running on the machine. Strangely enough, SQL Server can

choke

the operating system by leaving too little memory for the OS to function

at

optimum.

I'd have to disagree. I've never seen this be an issue.