How should a well written ASP.Net app’s connection use profile look like?

Recently I had to look into an ASP.Net application, which was consuming way too many SQL Connections and was causing problems to our web-server.

The way we were able to tell that the issue was being caused by too many SQL Connections being consumed was by profiling the “.Net Data Provider for Sql Server”.

Here is how you need to setup the performance profiler.

Open up the Performance console. Click on the “+” icon to add a new counter.
Select “.Net Data Provider for SqlServer” under Performance Object.

The 2 counters that I found most useful are:
1. NumberOfPooledConnections and
2. NumberOfReclaimedConnections.

Select the 2 counters and make sure that you select the correct instance from the Instance list. Click on Add.

Now use the Microsoft Web Application Stress Test Tool to exercise your web-app. (Remember to set Stress Level and Stress multiplier values under settings as well as the test run time).

Note: number of concurrent tests = stress level x stress multiplier

Now lets take of an app that was badly written and see how its graph looked like when it was exercised using the stress test tool:
 

As can be seen from the graph, both the “numberOfPooledCOnnections” and “numberOfReclaimedConnections” grow rapidly. This is probably because the sql connections were not be closed explicitly. At this point some definitions are in order:

numberOfPooledConnections: the number of open sql-connections
numberOfReclaimedConnections: the number of sql-connections that were closed because of the garbage collector running and reclaiming those Sql-Connections.

One should aim for numberOfReclaimedConnections to remain level at zero. Which would mean that you as the developer are proactively closing out each and every SqlConnection that you are opening. Keeping this number low, also saves CPU cycles.

As for numberOfPooledConnections, one should aim for slow growth which reaches a plateau. This should never grow quickly, as that could mean that you might hit the hard limit of the number of concurrent SqlConnections that can be open from your server.

So how should a well written app look? Lets take a look at the graph:

As you can see here, the numberOfReclaimedConnections remained level at zero. The numberOfPooledConnections grew only to 9, even though I was using 100 simultaneous connections to test the web-app (the same number that was used to test the app when the code wasnt pro-actively closing out the connections).

Final note:

I prefer to create a new SqlConnection object within a using block. This allows me to forget about the management of the Sql Connection (which is automatically closed once program execution leaves the using block – either normally or via an exception). Also, it is almost always a good idea to write your methods that work with your database, in such a way that they take a SqlConnection object. This way, the caller of the method knows that they will need to manage the connection object. It also allows you to leverage the same connection object across multiple operations on a database that occur at the same time.

One of the ways in which too many open SQL Connections manifests itself is that you will get connection time-outs.

Sample code:

using (SqlConnection connection = SqlUtil.GetConnection())
{
    connection.Open();
    SqlDataReader sdr = null;
    using(SqlCommand command = new SqlCommand("select * from test", connection))
    {
        sdr = command.ExecuteReader();
        bool dataLoaded = sdr.HasRows;
        sdr.Close();
    }
}