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******@con2in c.com 2 2684
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 //
-------------------------------------------
[posted and mailed, please reply in news]
Gary Whitten (wh******@con2i nc.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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:
Name=Provider=SQLOLEDB;Password=xxx;Persist Security Info=True;User
ID=xxxx;Data Source=xxxxx;Initial Catalog=xxxxx;Network
Library=dbmssocn
It suddendly stopped working on my customer network, so I made some
|
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 dozen workstations.
I intend to convert the back end to SqlServer and wish to use the
upsizing
wizard. I am acquiring SSW Upsizing Pro! 2000 which seems to be
recommended in other discussions.
|
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 a login page where the users would supply their credentials and the
business logic compares these
credentials against SQLServer database and allows/denies access to the
website.
|
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 insert these into a
database table (Access). I would appreciate any help
Ric
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
|
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 started, sqlserver takes about 100M memory. After
the system runs for a while, the memory used by sqlserver goes up to
512MB. Then we exit our application and expect the memory usage of
SqlServer could be dropped down to the min value....
| |
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 search resource file for this string id.
After that I need to return translated string from other resouce file ?
Is this best idea?
Where to find sample code which implements this ?
|
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. So...how exactly should I make this function? I understand all the other ways to make a hash function except this one.
Laura
|
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
signs of sqlserver.
Is SqlServer Express suppose to get installed when VS2008 Pro is installed?
Or should I download SqlServer and installed it that way?
|
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 Consumer thread. The Producer thread (Enqueue) inserts some heavy user defined object in the Queue. The Consumer thread (Dequeue) deletes one object from the Queue and processes it.
When we monitored the memory usage of this application in Task...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |