Debate - .Net Dev Vs. DBA Who will win?
-
Our scrappy new .Net Developer has convinced the long term VB developers that the excessive number of connections showing up in sp_who2 is normal. We have about 30-50 users on this new system he helped develop, and I have well over 1000+ connections every minute of the day. Me, being the DBA, am not too fond of having so many open connections laying around SLEEPING, so I told them to fix it. Especially since some have a last_batch date of 1/1/1900? His reply was, .NET does connection pooling, therefore this is normal. In order for me to figure this out, I'd have to do an awful lot of research, so I'm opening the debate up to you all.
Physical Environment: Windows 2000 sp4, SQL 2000 sp3a, no external application components are installed. Front end application is Windows Forms installed on each user's PC. All Connections are ADO.net SQL Provider. All list boxes, combo boxes, etc. are in type tables within the database, and all code is housed in stored procedures in the one database.
Debate: Should the DBA just except that .NET's connection pooling is causing the excessive connections, and this is normal and okay, or inform the developer that this is not normal, there shouldn't be excessive connections, because ____________________. (PLEASE FILL IN THE BLANK - and if you have a white paper/article/etc that addresses this, I'd consider it a gift from the gods).
Thanks to all who participate!
-
I know I might be a little off topic here, but I use Access ADPS to connect to the server (1 connection by default for the whole application). For auditing purpuses I had to add another global connection for the application. Now if you take in consideration that I shouldn't have a second connection held opened at all times and the fact that we have about 15 employees using on average 2-3 apps at the same time, you should get that I must have no more than 90 connections held opened at all times. A quick run of sp_who confirms this and shows only 59 connections on the server ATM (14 of them by sa ?????).
However I never worked with connection pooling and the only thing I know about it is how to spell it. However I find that 1000 connections is pretty f?&*ing much.
Have you reviewed the code to see if he creates a new connection everytime he queries the DB, does he destroys them all after use? Is there a global connection for the app, per form?
-
Thanks Remi, at your suggestion, I requested a copy of their syntax template. It's used throughout the application:
Public Function GetAllGroups() As DataSetDim sSQL As String = ""
Dim moSelCmd As SqlClient.SqlCommand
moDS = New DataSet
Try
sSQL = "dbo.sel_AllGroups"
moSelCmd = New SqlClient.SqlCommand(sSQL)
moSelCmd.CommandType = CommandType.StoredProcedure
moSelCmd.CommandTimeout = 180
moDA = New SqlClient.SqlDataAdaptermoDA.SelectCommand = moSelCmd
moDataConn.InitializeConnection()
moDA.SelectCommand.Connection = moDataConn.SQLConnection
moDA.Fill(moDS, mstrClassName)Catch e As Exception
MsgBox(e.Message)
End Try
Return moDS
End Function
-
It's a little hard for me to tell what's exactly happening from this.
Can you create new user in the db, then use the application with this new user?
If so you could see how many connections are opened after the application opens under this user, then you could load a few forms and see if more connections are added.
-
Oh, no, we don't have to do that. It's obvious the application is opening multiple connections per person. That's not really an issue. The problem is these connections are remaining connected all day. So if there were 50 people in this application all at one time, then that would mean an average of 10 connections being held throughout the day per person. That's the debate. Should this be considered normal for .NET or not? and why?
-
My own personal opinion :
One connection held opened 24/7 is already one too many. That is real for all programming languages/versions (with exceptions obviously). It would be like taking a new straw everytime I wanted to take a sip of my drink. After a few sips I couldn't even add any more straws... Now I agree that sqlserver can handle a huge load of connections, but why would you force it too? I've always tried to keep as little opened connections opened at all times... How hard is it to have 1 global connection for the app with a pair of open/close subs that you call everytime you query the db??
-
What's the architecture ? Are these smart client apps running on 50 individual PC's all making connections from each PC to the server, or is it an ASP.Net web app with the users using a browser based interface ?
-
Thanks PW! It's the smart client app running on 50 individual PC's all making connectino from each PC to the server. No Web or shared components involved.
-
Without looking at the code I would have to say 1000 connections supporting 50 users is horrible. I would see normally about 50 connections maybe a few more.
A sinle connection is normally used unless you are trying to handle concurrent processing and instantiate additional connections open at the same time.
Think of how Query Analyzer works. A single connection is made with QA for each child window you create.
What I suspect is that your developer may be creating connections in a global reference and not closing them thru the coruse of the applications runtime.
Then later is creating a new connection object and not actually reusing the existing object. If he closed an existing object then opened a new then connection pooling will actually reuse the previous connection.
But I believe he has done something wrong in the programming that is holding the connections open. Without the details of the code I can only speculate but .NET is not prone to opening up multiple connections on a single call, he has done something wrong.
Anyway
When using ADO.NET to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance. [7.0, 2000] Updated 2-11-2005
from
http://www.sql-server-performance.com/ado_net_performance.asp
-
I suggest you read this:
If possible, grab a test PC, start tracing the perf counters mentioned, and run the .Net client app.
It sounds like the SQL connection mantra of "open as late as possible, close as early as possible" isn't being followed in the client code. Or worse, a connection is being left open somewhere in badly written app code, and the "Max Pool Size" parameter in the client app's connection string is set way too high.
-
In the code I see
moDataConn.InitializeConnection()
This is probably where the connection will be made. But the connection is never been closed ?!
Before the return moDS, there should be a moDataConn.close() or something like that ( suppose moDataConn is an own written class ). Maybe moDataConn.SQLConnection = null will allready help to make sure the connection can be reused ( connection pooling ) or closed ...
Bert
-
Connection reuse SHOULD/COULD only come into play, when a connection is closed (if's it in use, it can't be borrowed by someone else)
If all that is being done is conections being opened, then SQL connection pooling is never coming into play. Maybe your dot.net buddy is referring to connection pooling outside of SQL.....but it sounds like he/she is talking through their a**.
Re any debate...an informed DBA should always win. The burden of proof should be on the dot.net idiot to prove, via a small-dummy-app, the application of their theory.
-
I feel 1000 connections is too much. I have a HRS system running in my enviornment (24*7) with around 100+ users accessing the database but using different applications. I have around 100-125 connections at the peak time when the database is at a maximum use.
One thing is clear that connections opened are not getting closed after they have finished what they were supposed to do. Thats resulting in opening of 1000+ connections. Every connection dealing with SQL Server will run a set of transactions in a batch. Once the transaction is committed or rollback, connection should be closed immediately which unnecessarily uses server & memory resources.
--Kishore
-
I agree with this, it looks to me like the open connections are not being closed. ADO.NET does not automatically close connections, even when the connection goes out of scope. Sounds to me like a very excessive number of connections, even with connection pooling, and looking at the code snippet there is no explicit closing of the connection, so best bet is that this is the problem.
Tell your .NET developer to check his code to ensure that he's closing his database connections, looks to me like a bug in the code. He should either be using a using() statement around his connection, or put the connection Open() in a try/catch/finally and be sure to explicitly close the connection when the code is done with it. Connection pooling will keep some connections hanging around for a while, but most definitely shouldn't be thousands of them.
-
After using each connection, you need to close the connetcion and set the object to Nothing.
If moDataConn.SQLConnection.State = ConnectionState.Open Then moDataConn.SQLConnection.Close()
moDataConn.SQLConnection = Nothing
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply