Is there any significant overhead with individual SQL2000 connection requests, when connections are shared?
Given that there are a limited number of active clients, no contention for shared connections, memory, etc., and that there are no problems with clients releasing connections when they are not active for a reasonable period.
All general guidelines say to release connections as soon as possible, but common sense says not to do anything that is not required or useful.
asked Mar 17, 2011 at 08:45 AM in Default
You should generally release connections as soon as possible, especially if using connection pooling. By releasing the connection earlier, you release it to the pool, meaning the next connection can then pick up that connection from the pool, rather than creating a new one.
That's not to say you should not keep connections open during the scope of useful work, however.
It does depend what you mean by 'shared connection' though. I have assumed you mean pooling, but maybe I have the wrong end of the stick.
answered Mar 17, 2011 at 09:02 AM
Matt Whitfield ♦♦
Releasing connections as soon as possible is a must, and is very useful. This way, SQL Server will manage the connection pool by itself, and will always have available connections which are served to the client faster. Back in the dark days of last millennium it was common for applications to open a global connection and keep it open for the duration of the application, but these days are long gone. In nowadays, the best way to do it is to release connections as soon as possible. It enables front ends to release the resources as soon as they are no longer in use and allows SQL Server a complete freedom in managing its own connection pool.
answered Mar 17, 2011 at 09:06 AM
There is a cost to memory for each connection, one of the reasons connection pooling was created. It's relatively trivial, but it can add up if you're not managing your code well. To see the details of how it works, read this link.
answered Mar 17, 2011 at 09:43 AM
Grant Fritchey ♦♦