SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Sleeping Connections
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snaayk
Starting Member

17 Posts

Posted - 07/24/2007 :  12:01:16  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 07/24/2007 :  12:29:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
are you closing the connections properly?

inserts and deletes can cause locks, yes.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/24/2007 :  12:42:02  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/24/2007 :  12:55:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/24/2007 :  13:18:16  Show Profile  Reply with Quote
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, db
Mode: 3, IS (intent Shared)
Status: 1, granted
Ref Count: 1
Owner type: 3, cursor

everything 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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/24/2007 :  13:55:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/24/2007 :  14:11:12  Show Profile  Reply with Quote
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]

Edited by - snaayk on 07/25/2007 11:44:20
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 07/24/2007 :  14:48:08  Show Profile  Reply with Quote
Do you use connection pooling?
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/25/2007 :  11:43:53  Show Profile  Reply with Quote
quote:
Originally posted by rmiao

Do you use connection pooling?


Yes, and it works except for the connections out of this class.
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/26/2007 :  12:01:33  Show Profile  Reply with Quote
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?
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/27/2007 :  10:08:13  Show Profile  Reply with Quote
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 [:)
Go to Top of Page

donpolix
Yak Posting Veteran

97 Posts

Posted - 07/30/2007 :  00:24:07  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message  Reply with Quote
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
Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 07/30/2007 :  13:57:16  Show Profile  Reply with Quote
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/30/2007 :  15:43:52  Show Profile  Reply with Quote
quote:
Originally posted by snaayk
is WITH NOLOCK safe?



No, you can get uncommitted data that might never get committed

Why not change the app?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

snaayk
Starting Member

17 Posts

Posted - 08/01/2007 :  12:20:08  Show Profile  Reply with Quote
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.

Edited by - snaayk on 08/01/2007 12:21:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000