| Author |
Topic |
|
snaayk
Starting Member
17 Posts |
 Posted - 2007-07-24 : 12:01:16
|
| I have a web app (.net, and only one) with about 300 users, which most access between 7am and 9am. I have been having a problem with timeouts recieiving connections. Sure enough I have been hitting my 100 max connection default. For the last 2 weeks I have been tracking the code down and have isolated it (using different logins for differents parts of the app) to one business object.This class has 1 insert, 2 deletes and 5 selects. And it is causing my problem! I commonly have 100 connections sleeping for about 30 minutes or so, I have had a max of 189 sleeping connections.Connection Pooling is working (maybe), since I see in Profiler the reuse of connections, but not all. My code closes all connections. What could be the problem?The one thing I am not sure about is that this class does a loy of inserts - sometimes in quick succession. Could that cause a lock of some sort?I have no open transactions, no blocking spids, I don't get it!!Help |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-24 : 12:29:20
|
| are you closing the connections properly?inserts and deletes can cause locks, yes._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-24 : 12:42:02
|
Every connection is closed - I only have to look at 8 connections - so I'm sure I didn't miss one  How can I check if the inserts and deletes are causing the problem? This has been suspect, this table has a lot of inserts and can have many deletes.Each user inserts a record 20 columns wide about 15 times, for a total of ~700 times a day with probably 75% of them by 9am. Which will be increasing to about 1300 soon. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-24 : 12:55:21
|
| run profiler and see what's going on.also look in the table syslockinfo.you see when an insert or an update happens an exclusive lock on the data is held. so if a select comes by and wants to select that data it has to wait and it can timeout._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-24 : 13:18:16
|
| OK, SysInfo says that I have 24 items, one in msdb, 4 in another db that I have and the remaining in my main db (with the issues).They are all exactly the same:Type: 2, dbMode: 3, IS (intent Shared)Status: 1, grantedRef Count: 1Owner type: 3, cursoreverything else is 0. I don't use cursors!? or tansactions in this class?What do I look for in profiler? Ive looked through it a hundered times I dont see anything out of the ordinary |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-24 : 13:55:26
|
| look for stmt:starting and stmt:completed event and sp:starting and sp:completed events that way you can see exactly what statements are timing out_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-24 : 14:11:12
|
| My statements are [not] timing out, I get no complaints. It's the connections that stay open, sleeping, and then there no connections available. What is the significance of teh syslockingo table?edited [not] |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-24 : 14:48:08
|
| Do you use connection pooling? |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-25 : 11:43:53
|
quote:
Originally posted by rmiao
Do you use connection pooling?
Yes, and it works except for the connections out of this class. |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-26 : 12:01:33
|
| I think I may have found half the problem.This table was moved from a different db last week. The index weren't moved [:/], i just created the index and the queries are (obviously) completing quicker. What may have been happening, is that with such a large number of users hitting it at the same time, a new connections were created because existing ones were not complete. Maybe that why there are a large number created.But i still dont understand, why if I have 10, 20, 50 connections sleeping why would a new connection be created? Why isn't it resuing a sleeping connection??Also, I have connections that audit logout, after a duration of 5-10 minutes, yet they login again with the same spid. Or I see audit logins with no activity after it??Am I missing something? |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-27 : 10:08:13
|
| Ok, the same issue. I can see why they are created - I have 10 connections created (last batch) at the same time, separated my milliseconds. But why are they not being reused when a future connection is requested?There has to be someone here that knows [:) |
|
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2007-07-30 : 00:24:07
|
| Inserts wouldn't normally pose much problem on lockings, but updates are, depending on the type of locking you use. You might need to optimize index for efficiency. On the other hand, you got connection pooling on, and once one connection is done executing, there is some kind of delay before they get automatically disconnected, they remain for a couple of secs that's why you they still show when you look at them on sysprocesses.Donn Policarpio |
|
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-07-30 : 13:57:16
|
| I think I have it...After some more investigating and readings on lockings, sleeping, etc. I thisnk this is what's happening:User requests data: queries take 2 seconds in QA, user gets impatient and re-requests the data or leaves, the data from the server never returns and just sits waiting for something. Which'll wait around all day until it's cleaned up.Is there any way not to lock the db when doing selects? is WITH NOLOCK safe? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snaayk
Starting Member
17 Posts |
Posted - 2007-08-01 : 12:20:08
|
Change the app? I'm trying to figure out what the problem so that I can change it. It's not the app, it closes every connection. The app uses the same patern across every business object (30+) and this is only happening here, this one business object.I think it's osmething in the way that multiple access to teh db is made or ajax....I just made three changes: - Changed to sprocs, no difference
- added unique constaint to the db and removed extra db call to check for existing data. Not sure what was better, an SQL exception because of a constraint violation or an additional db call
- added a window overlay when a db call is made on the page preventing user input (save browser buttons) to ensure that db calls are returned before the user attempts another request
I'm waiting on the last two changes and see if they have any affect, the first yielded no change. |
|
|
|
|
Sleeping Connections