473,405 Members | 2,338 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Resource Accumulation on SQLServer

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
Jul 20 '05 #1
2 2676
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 //
-------------------------------------------
Jul 20 '05 #2
[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
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: mdurliSPAMFILTER | last post by:
Hello, I developed a win32 .exe CGI that connects to a clustered SQLServer to report some data. The software is written with Borland C++Builder. This is the oledb string:...
3
by: Devonish | last post by:
I am planning to convert an existing Access database which has a back end (data tables and relationships only) on a server and a copy of the front end (form, queries, reports) on each of about a...
1
by: srivalli chavali via DotNetMonster.com | last post by:
Hi, I have a question regd. Windows authentication and network file access. My asp.net application doesn't use any of the .NET's authentication schemes (Forms, Windows or Passport) yet. I have...
3
by: Ric | last post by:
Hello, I am new to VB.NET. I would like to know if someone can suggest an online source where I could learn ADO. At this moment I have a form containing a number of text fields. I now want to...
2
by: Jenniflower | last post by:
Hi Gurus, Our system is using SqlServer 2005 on XP.( On my machine,only this application access SQLServer.) The sqlserver memory is configured to 128MB (Min)~512 MB(Max) After our system get...
2
by: Andrus | last post by:
I have resource files in different languages created by VCS 2005 Express. I want to use those files to translate reports at runtime. I have text to be translated as string. I think I need to...
1
by: aphrodite | last post by:
I have to write a program to manage a hash table using collision resolution by chaining, with numeric long integer and string keys and where the hash function should use polynomial accumulation....
7
by: Academia | last post by:
I was told that SqlServer express should get installed when VS2008Pro is installed (that is it is on the same DVD). I had a couple of errors during vs2008's installation and I don't see any...
2
by: manojmohadikar2008 | last post by:
Hi All, We are observing a serious issue with the memory usage of Queue and its very critical issue which needs to be fixed. We have an application which runs two threads i.e. a Producer and a...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.