Memory leak...How to get memory usage for each connections?

Hi Guys,
I find there is memory leak in our SQL 2000+SP3 databass. We have a
client-server system, and the there are Jrun4.0 running JSP and someother
Java application talks to the database.
However, for only less than 40 users, the SQL 2000 memory usage will
increase consistantly until it use all the memory it can get ,about 1.7G
memory. Then eventually the SQL server process will hang in couple days.
I don't know whether JRun JSP stuff or Java application is the culprit, and
I am wondering if there is a way to monitor the memory usage of every
database connection.
I would love to know what you guys think!
Thanks a lot!The number of users has little or nothing to do with how much memory sql
server uses. It's more the amount of data your accessing and how you do it.
A single user can easily take up all the available ram. The process you
describe (except maybe for the hanging portion) is normal and by design.
See here: http://www.support.microsoft.com/?id=321363 SQL Server 7 &
2000 memory usage
This is not to say there isn't a memory leak somewhere but it is unlikely
from SQL Server. I would suggest you make sure your queries are properly
tuned and you might want to set an upper limit on the SQL Server memory and
see if that helps.
--
Andrew J. Kelly
SQL Server MVP
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> Hi Guys,
> I find there is memory leak in our SQL 2000+SP3 databass. We have a
> client-server system, and the there are Jrun4.0 running JSP and someother
> Java application talks to the database.
> However, for only less than 40 users, the SQL 2000 memory usage will
> increase consistantly until it use all the memory it can get ,about 1.7G
> memory. Then eventually the SQL server process will hang in couple days.
> I don't know whether JRun JSP stuff or Java application is the culprit,
and
> I am wondering if there is a way to monitor the memory usage of every
> database connection.
> I would love to know what you guys think!
> Thanks a lot!
>|||Well.One thing is for sure, the SQL server hangs.
The application is quite complicated, so it is very hard for me to push
developers to tune every thing they wrote. I only want to find out if there
is way to find who takes the most memory or makes SQL server grab more
momory.
I did try to limit the max memory to 1.5 G. It hangs too.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> The number of users has little or nothing to do with how much memory sql
> server uses. It's more the amount of data your accessing and how you do
it.
> A single user can easily take up all the available ram. The process you
> describe (except maybe for the hanging portion) is normal and by design.
> See here: http://www.support.microsoft.com/?id=321363 SQL Server 7 &
> 2000 memory usage
> This is not to say there isn't a memory leak somewhere but it is unlikely
> from SQL Server. I would suggest you make sure your queries are properly
> tuned and you might want to set an upper limit on the SQL Server memory
and
> see if that helps.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > Hi Guys,
> >
> > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > client-server system, and the there are Jrun4.0 running JSP and
someother
> > Java application talks to the database.
> >
> > However, for only less than 40 users, the SQL 2000 memory usage will
> > increase consistantly until it use all the memory it can get ,about 1.7G
> > memory. Then eventually the SQL server process will hang in couple days.
> >
> > I don't know whether JRun JSP stuff or Java application is the culprit,
> and
> > I am wondering if there is a way to monitor the memory usage of every
> > database connection.
> >
> > I would love to know what you guys think!
> >
> > Thanks a lot!
> >
> >
>|||Do you have other apps running on the same server as sql server? How about
extended sp's? The only real way to tell what is making it use more memory
is to run profiler and perfmon to see what is running when the buffers start
climbing. But if you run profiler traces you can easily tell what sp's are
taking large amounts of I/O, cpu etc. This will certainly give you a clue
as to what may be gobbling up ram in the buffer.
Maybe this will help:
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
Andrew J. Kelly
SQL Server MVP
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> Well.One thing is for sure, the SQL server hangs.
> The application is quite complicated, so it is very hard for me to push
> developers to tune every thing they wrote. I only want to find out if
there
> is way to find who takes the most memory or makes SQL server grab more
> momory.
> I did try to limit the max memory to 1.5 G. It hangs too.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > The number of users has little or nothing to do with how much memory sql
> > server uses. It's more the amount of data your accessing and how you do
> it.
> > A single user can easily take up all the available ram. The process you
> > describe (except maybe for the hanging portion) is normal and by design.
> > See here: http://www.support.microsoft.com/?id=321363 SQL Server 7
&
> > 2000 memory usage
> > This is not to say there isn't a memory leak somewhere but it is
unlikely
> > from SQL Server. I would suggest you make sure your queries are
properly
> > tuned and you might want to set an upper limit on the SQL Server memory
> and
> > see if that helps.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > Hi Guys,
> > >
> > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > client-server system, and the there are Jrun4.0 running JSP and
> someother
> > > Java application talks to the database.
> > >
> > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > increase consistantly until it use all the memory it can get ,about
1.7G
> > > memory. Then eventually the SQL server process will hang in couple
days.
> > >
> > > I don't know whether JRun JSP stuff or Java application is the
culprit,
> > and
> > > I am wondering if there is a way to monitor the memory usage of every
> > > database connection.
> > >
> > > I would love to know what you guys think!
> > >
> > > Thanks a lot!
> > >
> > >
> >
> >
>|||SQL using 1.7GB of memory is perfectly normal. the hanging is probably NOT
caused by memory usage. Have you checked for blocking?
"Erik Miller" <bopew2000@.yahoo.com> wrote in message
news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> Well.One thing is for sure, the SQL server hangs.
> The application is quite complicated, so it is very hard for me to push
> developers to tune every thing they wrote. I only want to find out if
there
> is way to find who takes the most memory or makes SQL server grab more
> momory.
> I did try to limit the max memory to 1.5 G. It hangs too.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > The number of users has little or nothing to do with how much memory sql
> > server uses. It's more the amount of data your accessing and how you do
> it.
> > A single user can easily take up all the available ram. The process you
> > describe (except maybe for the hanging portion) is normal and by design.
> > See here: http://www.support.microsoft.com/?id=321363 SQL Server 7
&
> > 2000 memory usage
> > This is not to say there isn't a memory leak somewhere but it is
unlikely
> > from SQL Server. I would suggest you make sure your queries are
properly
> > tuned and you might want to set an upper limit on the SQL Server memory
> and
> > see if that helps.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > Hi Guys,
> > >
> > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > client-server system, and the there are Jrun4.0 running JSP and
> someother
> > > Java application talks to the database.
> > >
> > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > increase consistantly until it use all the memory it can get ,about
1.7G
> > > memory. Then eventually the SQL server process will hang in couple
days.
> > >
> > > I don't know whether JRun JSP stuff or Java application is the
culprit,
> > and
> > > I am wondering if there is a way to monitor the memory usage of every
> > > database connection.
> > >
> > > I would love to know what you guys think!
> > >
> > > Thanks a lot!
> > >
> > >
> >
> >
>|||Thanks for all the hints here, appreciated !
I'll try to check them accordingly.
"Hal Berenson" <haroldb@.truemountainconsulting.com> wrote in message
news:Ou4J3sXQDHA.2432@.TK2MSFTNGP10.phx.gbl...
> The hang situation makes this quite different from the usual concerns
about
> SQL Server allocating very large amounts of memory. Likely culprits:
> - Extended Stored Procedures (XP) - If you have any user written XPs then
> they are a very likely cause of problems. For example, they don't use SQL
> Server's internal memory management, and there is nothing to prevent them
> from behaving badly.
> - OLE Automation - If you use this feature then you have some similar
> problems as XPs in that the environment the objects run in is "outside"
the
> SQL Server environment itself. The likeliness of a problem is much lower
> than an XP, but its still present.
> - Distributed Queries - The only OLE DB provider that really has been
tuned
> to run in the SQL Server address space, and stress tested in high
> transaction rate environments by the SQL Server group, is SQLOLEDB. Other
> OLE DB providers aren't concious of running in the SQL Server address
space
> and are more likely to misbehave in that environment. I'd particularly be
> wary of providers that are not supplied by Microsoft. Fortunately, you
can
> just make sure that AllowInProcess=0 for these OLE DB providers. (A more
> general recommendation would be to test with AllowInProcess=0 and then set
> AllowInProcess=1 when you are very confident that the provider is working
> well.)
> - Bug in SQL Server. If you rule out all the external items that could
> behave badly, then you tend to look at the possibility that you are
running
> into a bug in SQL Server. In that case, I'd place a support call with
> Microsoft. Debugging something so general can be very complex, and it
could
> take a very long time to do simply based on suggestions from folks in a
> newsgroup of what you should try.
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
>
> "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> news:mpXMa.52170$Mc4.6537800@.news0.telusplanet.net...
> > Well.One thing is for sure, the SQL server hangs.
> >
> > The application is quite complicated, so it is very hard for me to push
> > developers to tune every thing they wrote. I only want to find out if
> there
> > is way to find who takes the most memory or makes SQL server grab more
> > momory.
> >
> > I did try to limit the max memory to 1.5 G. It hangs too.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23K%23kUzRQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > > The number of users has little or nothing to do with how much memory
sql
> > > server uses. It's more the amount of data your accessing and how you
do
> > it.
> > > A single user can easily take up all the available ram. The process
you
> > > describe (except maybe for the hanging portion) is normal and by
design.
> > > See here: http://www.support.microsoft.com/?id=321363 SQL Server
7
> &
> > > 2000 memory usage
> > > This is not to say there isn't a memory leak somewhere but it is
> unlikely
> > > from SQL Server. I would suggest you make sure your queries are
> properly
> > > tuned and you might want to set an upper limit on the SQL Server
memory
> > and
> > > see if that helps.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Erik Miller" <bopew2000@.yahoo.com> wrote in message
> > > news:7RNMa.46791$T85.5399682@.news1.telusplanet.net...
> > > > Hi Guys,
> > > >
> > > > I find there is memory leak in our SQL 2000+SP3 databass. We have a
> > > > client-server system, and the there are Jrun4.0 running JSP and
> > someother
> > > > Java application talks to the database.
> > > >
> > > > However, for only less than 40 users, the SQL 2000 memory usage will
> > > > increase consistantly until it use all the memory it can get ,about
> 1.7G
> > > > memory. Then eventually the SQL server process will hang in couple
> days.
> > > >
> > > > I don't know whether JRun JSP stuff or Java application is the
> culprit,
> > > and
> > > > I am wondering if there is a way to monitor the memory usage of
every
> > > > database connection.
> > > >
> > > > I would love to know what you guys think!
> > > >
> > > > Thanks a lot!
> > > >
> > > >
> > >
> > >
> >
> >
>