Resource Accumulation on SQLServer

From: Gary Whitten (whitteng_at_con2inc.com)
Date: 02/12/04 

Date: 11 Feb 2004 20:15:03 -0800

We have a JDBC project that works with SQLServer, along with other
DBs. During development, we noticed that resource useage on SQLServer
seemed to grow steadily - Processes, Process Locks and/or Object Locks,
according to the "Current Activity" section of Enterprise Manager.
Fairly quickly (a few hours), SQLServer would use so many resources it
used up all available system memory and the system would crash.

Without understanding much about why that was happening, mostly out of
trial and error, we started using transactions, which seemed to fix the
problem. Now, one of our customers seems to be seeing the problem again:

511 Process Locks, 18 Object Locks, 526 Processes

after a few hours. The system runs out of memory and crashes. We have
multiple test configurations, including Linux, Win2K, XP, but we do
not see the same behaviour - we typically see no more than 20 or 30
processes, process locks or object locks, and our tests seem to run
indefinitely. Since we can not reliably reproduce the symptoms that our
customer sees, it is very difficult for us to analyze, let alone, fix
the problem.

Does anyone - especially SQLServer pros - have any insight as to what is
going on here and/or how to fix it? Like how does a process, process
lock or object lock relate to JDBC? process <-> connection,
object <-> row, table... or what, locks?

    Thanks,
      Gary Whitten
      whitteng@con2inc.com

Relevant Pages

  • Re: Resource Accumulation on SQLServer
    ... > We have a JDBC project that works with SQLServer, ... we noticed that resource useage on SQLServer ... That said, if the number of locks accumulate, then you might have a problem. ... may need to review how you close connections. ...
    (comp.lang.java.databases)
  • Performance Monitor locking counter
    ... I have a Performance Monitor counter set up with the idea of monitoring ... SQLServer: Locks ...
    (microsoft.public.sqlserver.tools)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... SQLServer view locks as a resource. ... Oracle views locks ...
    (comp.databases.oracle.server)
  • Monitor performance
    ... SQLServer: ... SQLServer: Database application Database -Transaction/Sec ... Latches - Average Latch Wait Time ... LOCKs - Average Wait Time ...
    (microsoft.public.sqlserver.programming)