Re: Resource Accumulation on SQLServer
From: Erland Sommarskog (sommar_at_algonet.se)
Date: 02/15/04
- Next message: Silvio Bierman: "Re: mySQL JDBC & Applet problem"
- Previous message: Dimitri Maziuk: "Re: which GUI library to use for database app to simply making form?"
- In reply to: Gary Whitten: "Resource Accumulation on SQLServer"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Sun, 15 Feb 2004 22:36:16 +0000 (UTC)
[posted and mailed, please reply in news]
Gary Whitten (whitteng@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, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Silvio Bierman: "Re: mySQL JDBC & Applet problem"
- Previous message: Dimitri Maziuk: "Re: which GUI library to use for database app to simply making form?"
- In reply to: Gary Whitten: "Resource Accumulation on SQLServer"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
