Monitoring the opening time of an ADO.NET connection

Question

We have a three-tier application with a C # client, a C # WCF web service level and a SQL Server database. The web service connects to the database using ADO.NET. All of our C # code uses the .NET Framework 2.0.

Recently, a client performed a stress test in our application. During the test, the web server generated many errors, such as:

Failed to connect to database for connection string "...". Timed out. The wait period expires before the operation is completed or the server is not responding.

I know that there are ways to catch connection errors when the connection pool is full, and try to acquire a connection outside the connection pool. We also found several queries that needed to be configured, but they did not take into account the timeouts of the web server connection.

We are trying to find out why the timing web server connected to the database. I configured the web server to enable all ADO.NET performance counters . However, I do not see anything related to the time required for the connection or connection timeouts or the like. Ideally, we could graphically display the connection time in perfmon next to other ADO.NET counters.

Is there a way to track the performance of ADO.NET when acquiring connections?

I suppose we could create our “average connection open time” performance counter by making temporary attempts to open connections, but I would rather use something that already exists.

Answer 1

You can use perfmon to get this information. You will need to connect to the User Connections monitor in the SQL Server: General Statistics section. Here is the blog post I grabbed it. This will let you know at what points in time the connections remain open.

Then you will need to correlate this with the tasks of the application (i.e. the things you do in the application when you see that they are constantly being raised).

As soon as you do this, you will want to get these connections inside the using statement, if you have not already done so:

 using (SqlConnection cn = new SqlConnection("some connection string")) { cn.Open(); ... } 

by doing this, you won’t need to release Close , and you don’t have to worry that they will be disposed of properly.

In short, the performance counter should help you track the code in the application causing the problem, but it won’t reach the line, say, it will take a little more effort.