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.

Comnenus

Would SQL Profiler help in this regard? – Eckardt

@MrMoose: The profiler helped a lot in finding some problem queries. But I need something specific to monitor connection times to see when they go up and when they start timing out. – Comnenus

This needs an answer specifically about monitoring connection OPEN time. As in... how long the call to SqlConnection.Open takes -- how long it's waiting on a pool connection to become available specifically -- to diagnose why pool connections take long to become available. Things like connection leaks, etc. Frameworks like EF don't expose events like connection open, and seal the damn classes for connection factories and DbConnections, so it's difficult to instrument. We'd need something at the ADO.NET level, but it has no such perf counters. – Ignominious

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.

Tractable

Thank you, but I'm not worried about application connection diposal. I'm fairly sure our data object is doing that correctly. I'm wondering why occasionally it takes a long time to connect to the database. Specifically, I'd like to monitor the time acquiring a connection via perfmon so I could match spikes in connection acquisition times to some kind of database numbers like pending I/O or CPU usage or the like. I don't see any other way to do this other than to create my own counter. – Comnenus