May 22, 2006 at 5:11 am
Hi all.
I'm a SQL Server Admin newbie, so this may be a silly question, but if I don't ask, I'll never know....
We recently turned off connection pooling in our client application because we thought that SQL Server handled this automatically. However, since doing this, 2 of our clients (both running SQL Server 2000 on Win2k3 Server) are having huge performance issues, which seem to stem from connection pooling. i.e. the performance hit comes because every call to the app server has to wait to get a new connection, rather than receiving an existing connection from the pool. No-one else is affected, and we haven't been able to replicate this problem in testing.
I'm guessing there's some configuration that is common to both of these systems that is causing this automatic pooling to fail, but I wouldn't have a clue as to what it might be.
Does this problem ring a bell with anyone?
Thanks in advance,
Lin.
May 25, 2006 at 8:00 am
This was removed by the editor as SPAM
May 25, 2006 at 8:54 am
nothing strange here, turn your connection pooling back on.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 25, 2006 at 11:56 pm
SQL Server has no hand in the management of connection pooling.
Connection pooling is similar to a bank of phones used by a research department. One guy calls the library (dials the phone number, works his way through the menus, enters his departments support contract ID, waits for someone to answer, asks to be transferred to somebody in a specific department...), asks a question, and gets an answer. When he's done, he can:
A) Hang up the phone so the person at the library who took the call can go back to work, and the phone line can be used for other purposes
- or -
B) Hand the phone to the next researcher in line who needs to speak with the same department at the library. That next researcher can immediately ask their question without having to go through the mess of actually getting the right person on the phone. Or there can be someone who stands by the phones who takes the receivers when the researchers are done with them; that person waits a period of time for anyone else to show up and hands that researcher a phone already connected to the right person at the library.
Option A saves library (server) resources by not tying up thier lines or staff. Option B saves lots of time for the researchers (clients), who won't have to go through all the calling/connection/waiting mess. The person who stands at the bank of phones holding the lines open and handing open lines to approaching researchers is Mr. Connection Pooling.
In SQL Server parlance, those server resources of holding open a connection - compared to establishing one - are really cheap. The people who answer the phones can handle lots of calls at the same time, and there are plenty of inbound lines. There are obvious limitations (1,000,000 unused open connections will hurt pretty bad), but the 10 default connections for connection pooling are barely a blip on the map for most production systems.
However, it is not for the server to hold those connections open, beyond not rudely hanging up the phone as soon as a question is answered. It's up to something that stands between the server and client (connection pooling) to hold open that line and have a way to identify who else can take advantage of it, and handing the phone over to them.
Turn the connection pooling back on and see if things speed up a bit.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply