Posted by Erland Sommarskog on 01/19/06 00:49

Oscar Santiesteban Jr. (o_santiesteban@bellsouth.net) writes:
> Last weekend, the servers in our datacenter where moved around. After
> this move, and maybe coincidental, 1 server is performing very poor.
> After running a trace with SQL Profiler, I saw the problem which was
> later confirmed with another tool for SQL server performance monitoring.
> It seems that all connections to the SQL server (between 200 - 400) are
> doing a login / logout for each command that they process. For example,
> the user's connection will login, perform a SELECT, and then logout.
> This is not a .NET application. The client software was not changed, it
> is still the same. The vendor has said that it is not supposed to do
> that, it is supposed to use 1 connection that log's on in the morning
> and logs off at the end of the day or whenever the user exits. 1 user
> may have several connections to the database.

It sounds as if connection pooling was turned off. Most applications
these days - and it does not have to be .Net - works with the paradigm
that they connect, run a query and then disconnect. Under the covers,
the client API maintains a connection pool, which means that a logical
disconnect is not directly a physical, but if there is a reconnection
within 60 seconds, the connection will be reused.

But this is stricly a client-side feature, so moving a server should not
cause this, but there has to be some change on the client side for
connection pooling to be ditched.

An other alternative is that SQL Server would itself close the connection,
but this only happens on error; SQL Server does not have any inactivity
monitor.

That leaves the network. The network could be configured to drop the
connection when nothing have happned for n seconds. But in such case,
I would expect the client to report errors, as most clients are not
prepare for this form of brutal disconnection. Still I would investigate
networks and firewalls.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]