Monitoring ADO.NET Connection Open Time

Question

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

Recently, a customer performed a stress test on our application. During the test, the web server generated a lot of errors like the following:

Could not connect to database for connection string '...'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I am aware there are ways to catch connection errors when the connection pool is full and try acquiring a connection outside of the connection pool. We also found several queries that needed to be tuned, but they dot not explain web server connection timeouts.

We're trying to figure out why the web server was timing out connecting to the database. I have setup the web server to enable all of the ADO.NET performance counters. However, I don't see anything related to the times required to connect or connection timeouts or the like. Ideally, we would be able to graph the connection times in perfmon next to the other ADO.NET counters.

Is there a way to monitor the ADO.NET performance in acquiring connections?

I imagine we could create our own "average connection open time" performance counter by timing attempts to open connections, but I'd rather use something that already exists.

Answer 1

You can use perfmon to get this information. You'll need to attach to the User Connections monitor under SQL Server: General Statistics. Here is the blog post I grabbed that from. This will let you know at what point in time connections are being left open.

You will then need to correlate that with application tasks (i.e. stuff you're doing in the application when you see them continually climb).

Once you've done that you're going to want to get those connections inside a using statement if you don't already:

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

by doing this you won't need to issue a Close and you won't have to worry about them getting disposed of properly.

In short, the performance counter should help you track down the code in the application that's causing the issue, but it won't be down to the line per say, it will take a bit more effort even from there.