Resource Accumulation on SQLServer

[Gary Whitten]

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
wh******@con2inc.com

[Sampsa Sohlman]

Gary Whitten wrote:

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?

Check that your application is closing Connections, Statements and
ResultSet after they have been used (also when exceptions has occurred)
In SQL server every connection get process id and to me 511
processes/connections sounds a lot.

I saw similar kind of problems at one application and reason was that
developer didn't close resultset's everytime, when he got Exception he
didn't close ResultSets.

- Sampsa

--
-------------------------------------------
// Sampsa Sohlman //
// My email can be found on my homepage //
// http://sampsa.sohlman.com //
-------------------------------------------

[Erland Sommarskog]

[posted and mailed, please reply in news]

Gary Whitten (wh******@con2inc.com) writes:

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.
Hm, what do you mean with "crashes"? If you exhaust some resource, I
would rather expect SQL Server kill a connection, or refuse to let
someone in, but I would not expect it to crash.

One thing I should point out from the beginning: the fact that SQL Server
grabs about all available memory on the machine is perfectly normal. It
thinks that the more data it can have in cache, the better.

That said, if the number of locks accumulate, then you might have a problem.
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

I don't know what a "process lock" is, but I get the feeling that you've
been running the Performance Monitor; I never run that tool myself. I
would guess it is a lock on the current database, which about every
process has. Thus, nothing to lose sleep over.

18 object locks is not very much at all. It seems like an idle database.

So is 526 connections bad or not? I can't tell, because I don't know
your application. But assuming that you expect a far lower number, you
may need to review how you close connections. (But since I don't know
JDBC or Java, I can tell about the client-side parts here.) The connections
may just be oprhans. Sampsa suggested that you may not close result sets,
but if you had failed to get all data, I would expect a lot more object
locks.

A shot from the hip, is that you should look into to issue SET NOCOUNT
ON when you open your connections. If you are using stored procedures,
but that in your SPs. Without SET NOCOUNT ON, you get empty resultsets
with the row count for INSERT/DELETE/UPDATE statements; these could be
the culprits.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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