x

Is Connections Overhead significant

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.
more ▼

asked Mar 17, 2011 at 08:45 AM in Default

rixandy gravatar image

rixandy
71 1 1 1

I understand the arguments regarding use of 'pooled' or shared connections to avoid overhead, which are well documented.

My question was that if (for example) I have only one active client with a queue of transactions to process, is there any significant overhead if that client releases the connection after every transaction. The key word is "significant" - another way is to ask whether my transaction processing is significantly slower because I am unnecessarily releasing and requesting a connection.
Mar 17, 2011 at 10:50 AM rixandy
It depends on what a "queue of transactions to process" means. If these are a bunch of transactions which need to be performed one after another on all or none basis then no, you should not close the connection, and in fact, you cannot. If these are independent and it is OK for one to succeed and another to rollback then the connection should be closed and disposed every time unless all the work needs to be performed one task immediately after another. In other words, if you have a UI screen which needs to populate a bunch of dropdowns from the catalog and then populate some grid with data based on initial default values in the dropdowns then it could be OK to use just one connection for this unit of work (though I would personally still segregate the tasks). It would be definitely not OK to "keep the connection warm" for the next request from UI, such as when the user picks other values in the dropdowns and now wants to refresh the data in the grid. This action needs to be done on the brand new connection (which will be served by SQL Server from its pool considerably faster than the first one). Once the data is served, the connection should be then immediately closed and disposed.
Mar 17, 2011 at 11:10 AM Oleg
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Mar 17, 2011 at 09:02 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

You are correct, I should have said "pooled".

Refining my question - how can I (relatively easily) determine the 'scope of useful work' where it is reasonable to keep the connection open. If overhead is practically zero, the question is moot. If overhead is relatively significant, the connection release could be delayed until there is no work (or we take a scheduled tea break).
Mar 17, 2011 at 10:58 AM rixandy
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Mar 17, 2011 at 09:06 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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][1].

[1]: http://msdn.microsoft.com/en-us/library/aa175395(SQL.80).aspx
more ▼

answered Mar 17, 2011 at 09:43 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

@Grant Fritchey I still cannot believe that Ken is gone. It was such a tremendous loss to the community when he passed away.
Mar 17, 2011 at 11:19 AM Oleg
Tell me about it. And I never ever got to meet him or hear him talk. And now his EXCELLENT books are starting to age. Sucks.
Mar 17, 2011 at 11:40 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x46

asked: Mar 17, 2011 at 08:45 AM

Seen: 1673 times

Last Updated: Mar 17, 2011 at 08:45 AM