February 4, 2004 at 2:09 pm
Alright, we have a heavy duty SQL Server. I don't know all the specs but it supports about 10 databases ranging in size from 1 to 15 gigabytes. It runs alright durning the month until month end when it is being hit considerably harder.
When I run a sp_who2 on the machine I find usually about 350 or more connections. Is this possibly causing high contension for everyone. The reason there are so many connections showing up are because of how our programs are written. I have noticed many of our programs open up about 3 or 4 connections and keep them open for the duration of the program, which can be an entire day or longer.
Is this maybe part of the problem, or do you think this has nothing to do with it if these connections are just sitting there and not really doing anything?
Looking for opinions.
Thanks
February 4, 2004 at 2:34 pm
What symptom do you see during the end of month? It could be that your server is unable to handle such workloads.
You may post your server's hardware information here. Meanwhile, start performance monitor to monitor the key performance counters like CPU, Memory and Disk IO to see where the bottleneck are.
Check whether there are long-run queries, blocking and deadlock in SQL Server, They might be the causes of decreasing in performance at month end.
February 4, 2004 at 2:58 pm
Yes, I believe there are bottlenecks with CPU and some locking. There are others looking the performance counters. I was just curious to know if a SQL Server will be slower the more connections it has to it if the connections are not always in use?
Will it perform the same if it has 5 users connected to it that are doing nothing as if there are 350 users connected to it doing nothing?
I have always thought leaving your connections open reduced overall performance. Is this true?
February 4, 2004 at 4:26 pm
"Will it perform the same if it has 5 users connected to it that are doing nothing as if there are 350 users connected to it doing nothing?"
I don't see the difference. Each connection takes approximately 24 kilobytes (KB) of overhead regardless of whether the connection is being used. Of course, all open connections should be properly colsed once transactions complete.
February 4, 2004 at 4:48 pm
So your saying the connections should be shut when a group of transactions is completed rather than just staying open and idle.
And you are also saying that leaving that many(350) connections open all of the time is contributing to our performance problem.
These statements seem to kind of contradict each other.
Is this right?
February 5, 2004 at 9:46 am
From everyone's comments, I gather that a SQL connection is taking up about 24KB per connection. Is that it? If we have 350 open connections, this is roughtly 8.4MB of memory. 8.4MB is not much compared to the several gig we have in there. So if 8.4MB of memory is the only thing that is caused by leaving connections open all of the time, it is no big deal and is probably not causing our database contension problems.
Is this statement right or wrong?
Please comment and let me know.
February 5, 2004 at 9:53 am
You are right on that. I am not sure whether there are any network performance impact to the server when many connections keep open, Anyone has more comments?
February 5, 2004 at 10:00 am
OK, great, we agree that is going to only suck up roughly 8MB.
Now, including myself, everyone says that connections should be closed when not being used. Is everyone saying this to conserve the 8 or so MB's, or is there other performance problems such as network performance as Allen mentions or others related to SQL Server?
The main question is, why is it so important to close your connections when not being used rather than keeping them idle?
February 5, 2004 at 11:27 am
The number of open connections has also impacted to number of worker threads available to SQL Server processes.
When the actual number of user connections is less than the amount set in max worker threads, one thread handles each connection. With hundreds of connections to the server, using a thread-per-connection can consume large amounts of system resources.
if the actual number of connections exceeds the amount set in max worker threads, SQL Server pools the worker threads so that the next available worker thread can handle the request. Thread pooling helps optimize performance when large numbers of clients are connected to the server.
Usually, a separate thread for each client connection consumes fewer system resources. If you can keep number of connection less than max worker threads (default is 255) consistantly, The SQL Server will perform better.
February 5, 2004 at 12:03 pm
Thank you Allen, this is the kind of information I was looking for. This makes sense if we have 350 open connections at any give time and only 255 max thread workers.
Do you have a source for this information or a helpful source on this type of information.
Thanks again
February 5, 2004 at 12:09 pm
One more question. Does the number of records in master..sysprocesses table reflect the number of open connections. I am assuming this is true but have no way to tell for sure.
Or does this number relate to the number of work threads. Since it seems unique by SPID, I would think it would be connection, but thats just me.
Does anyone know?
February 10, 2004 at 8:21 am
No, that is processes, not user connections. Try this:
SELECT cntr_value 'User Connections'
FROM master.dbo.sysperfinfo
WHERE counter_name = 'User Connections'
--Jonathan
February 10, 2004 at 8:22 am
> Do you have a source for this information or a helpful source on this type of information.
Books OnLine.
--Jonathan
February 10, 2004 at 8:40 am
I know this is a bit late but... each connection while performing task may use a portion of tempdb. Any global space used in tempdb is not released until the connection is dropped. Is it possible that tempdb is being overloaded by all the open connections? Also, any ##Tables not explicitly dropped remain in tempdb until the connection is dropped.
February 10, 2004 at 9:18 am
Do you think if I have around 350 consistently open connections based off of the following query, that bumping up the "max worker threads" past 350 will help?
SELECT cntr_value 'User Connections'
FROM master.dbo.sysperfinfo
WHERE counter_name = 'User Connections'
Also, srankin, is there an easy way to tell you if a user is a user has any ##tables sitting around or if they are consuming any of tempdb? Or is there any way to tell if tempdb is getting "overloaded"?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply