SQL Server Performance Forum – Threads Archive
Connection Overhead?
Let’s say you have 1,000 front end users and enough resources to buy enough licenses so that all 1000 users can be connected at once to the database. Does having this many connections slow down the performance of the database? Is it better to write the front end so it only connects when updating, deleting, reading, etc.? or Is it better to beef up the server? ThanksTom <><< Tom Laurie
CIS Professor
NH Technical Institute
Unfortunately, there is no easy answer to your question. Here are some issues to consider when you try to answer this question. 1) Generally speaking, fewer connections are better than more connections as each connection takes up additional overhead (uses more memory). 2) If you assume a situation where there are 1,000 simultaneous connections, what is really important to look at is how many of these are actually active. In many cases, a large number of the connections will not be doing anything, so while the total number of connections is big (and taking up some overhead), the actual activity of these 1,000 connections may be only 100 or 300, or whatever, and it is those that you want to spend most of your time tuning. Ideally, you can guesstimate this figure. 3) Another issue is connection pooling. Generally speaking, in your client or middle-ware code you want to open a connection, use it, and then close it as fast as you can. While this reduces the total number of connections, it also introduces a problem of connection opening/closing overhead. Connection pooling is used to overcome much of this problem. For example, it is possible to support 100 active users with only 10 actual connections, as the connections are used over and over. Be sure you write your code to take advantage of connection pooling. How you do this varies on your development environment. 4) Another issue is that a connection can be used by more than one user at the same time. This is often done to reduce the total number of connections needed. 5) By default, SQL Server will only create a maximum of 255 simultaneous connections. If your application asks for more than 255, SQL Server will automatically begin to share connections. This has its limitations, and at some point, only so many connections can be shared, and you have to manually tell SQL Server to use more connections. Where to set this is much as trial and error as other things. For example, if you have a 1,000 users, and only 100-200 are active, then the 255 default setting is fine. But if you find that you have 500 active users, then you may need to bump the 255 to a higher number, using trail and error to find the ideal setting. If you bump up the maximum number of connections, you will need more available memory, or other SQL Server performance may suffer some. To summarize, it is best to reduce the number of connections as much as possible, use connection pooling, and to use connection sharing (as appropriate).
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
You got first bite from Brad which has covered most of the stuff, moreover I suggest refer to the articles listed in this site about database and server configuration which will give you idea. HTH Satya SKJ
I am running 2000 and an ASP front end for an intranet application. It seems to take about 30 – 45 seconds to drop a connection. I am using cn.close() set cn=nothing consistantly each time I use ADO. Is there a way to get SQL Server to dump the connections faster? Thanks,
Ron Corvette – It’s never too late for a happy childhood.
to rwatts:
When you issue cn.close the connection will be returned to the connection pool that is kept alive by the web server. This is normal behaviour. The connection will not be removed from sql server unless the connection pool drops it, and that only occurs when activity drops and you need less connections open in the pool. to tlaurie:
I would let the web server(s) handle the connection pool by itslef. It’s often better than to have say 1000 connections open all the time.
]]>