Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
p2bl
Yak Posting Veteran
54 Posts |
 Posted - 2002-11-26 : 01:22:46
|
| hello,I use jsp and SQL Server 2K to develop webapps,using btimechanicconnectionpool,and set maxconnection as 100,but,on SQL Server mmc--"current activity",I find many more threads than 100,avarage 400 and can be up to 1200!most of them are marked as "sleeping",what's going on????========================look! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-26 : 07:45:39
|
| The connections are not being closed, and after a while they go into sleeping mode. You need to review your JSP code and audit the database connection lifecycle; when it connects, the work that it does while it's connected, and when it closes (IF it closes, it doesn't seem to be doing that now) Database connections should be as brief as possible: connect, get the data and/or perform the update, then close. You should NOT have any kind of pause for user input or any other task that takes time to perform while the database connection is open. Those tasks need to be moved out of the DB connection lifecycle. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-26 : 08:13:07
|
| I agree that connections must eventually be closed, however I have an application where keeping the connection open for x amount of time actually speeds up the application tremendously. It's a time keeping application where people charge time to different projects and the data is saved every time they enter it, so when I tried opening, updating, closing, opening,updating, closing, etc... that was very slow. What I do now is keep the connection alive for 1 minute every time they enter data and disconnect after 1 minute of inactivity and it works great. Now I know that for most systems you can collect all the data first then do an update however in my case I had requirements that prevented me from writing to disk or storage reliably and I can not count on the user to remember to press update when they are done entering the data. It all depends on the requirements but definitely close all connections within a reasonable period of time and don't rely on the user to close the program in order to close the connection because some users like to go home and leave their computers on.Edited by - ValterBorges on 11/26/2002 08:15:20 |
|
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2002-11-30 : 04:26:37
|
| thanks for answer.I think I have solve it.the only reason is the little RAM ,about 10000 connections per day and only 256M RAM,after I add it to 1G,the sleeping thread reach a peak at 400 only,and I find another reason why a thread sleeping---lock.if one thread try to update a table,any other table try to update (or even select from)the same table have to "sleep"========================look! |
|
|
|
|
|
|
|
|
sleeping thread???