Monitoring Database Connections using Performance Counters

Introduction

Database connections are vital in large enterprise applications. Improper usage of connections leads to performance issues. Monitoring the database at real-time is trivial but we are holding one session of the Database and invites lot of security issues. ADO.NET performance counters can be used to monitor the status the connection resources that the application uses. This article concentrates mainly on monitoring database connections.

Database Connection Counters

Let's briefly look at the various counters in ADO .Net that are required to monitor.

NumberOfActiveConnectionPools	When connection pooling is configured in the applications, connection pools will be created and connections (min Size mentioned) will be established. This counter tells how many such connection pools are created.
NumberOfActiveConnections	This value denotes how many active connections that are currently being used.
NumberOfFreeConnections	This shows how many number of connections available in the usable state in the pool.
HardConnectsPerSecond	This value denotes the number of connection made to the database per second.
HardDisconnectsPerSecond	This value denotes the number of connections disconnected from the database per second.
SoftDisconnectsPerSecond	This value shows the number of connections that are pulled out of the pool per second.
SoftConnectsPerSecond	This value shows the number of connections that are returned to the pool per second.
NumberOfReclaimedConnections	This shows the number of ACTIVE connections that have reclaimed by the garbage collector without explicit close or dispose connection calls from the creator.
NumberOfPooledConnections	NumberOfPooledConnections

These counters can be monitored using

1. Performance Monitor (perfmon)
2. ADO .Net --> System.Diagnostics.PerformanceCounter

1. Performance Monitor (perfmon)

Perfmon command can be used to monitor all the various counters that ADO .Net supports. Goto Start -> Run -> <Type> perfmon <enter>.

We should see a screen as follows.



The Initial screen may not show the ADO .Net Providers. We should select those performance objects depending on the provider we use. ADO.NET 2.0 introduces support for performance counters for both System.Data.SqlClient and System.Data.OracleClient.

We are now adding those counters to our monitor. Click on  Icon from the tools and select the desired performance object.





Select the Application instance we would like to monitor from the available instance list.



I would prefer selecting all the counters from the above screen. After adding those counters they will be added to our monitor.



2. Using ADO .Net PerformanceCounter library

Refer to MSDN Documentation here for the details on the framework class library.

Let's create a simple class that initializes the various counters for a given application instance and prints the value for different counters.

public class DotNetPerformanceCounters
{
    List<PerformanceCounter> lstPerfCounters = new List<PerformanceCounter>();
    public virtual void InitializeCounters(string AppInstanceName, Type PerfCountersList)
    {
        foreach (string counterName in Enum.GetNames(PerfCountersList))
        {
            PerformanceCounter PerfCounter = new PerformanceCounter();
            PerfCounter.CategoryName = ".NET Data Provider for SqlServer";
            PerfCounter.CounterName = counterName;
            PerfCounter.InstanceName = AppInstanceName;
            lstPerfCounters.Add(PerfCounter);
        }
    }
    public virtual void PrintCounters()
    {
        foreach (PerformanceCounter p in lstPerfCounters)
        {
            Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue());
        }
        Console.WriteLine("****************************************************************");
    }
}

While initializing the counter names that are to be monitored, we should send the instance name of the application also. Instance name of a console application can be obtained as given below.

Assembly.GetEntryAssembly().GetName().Name + "[" + Process.GetCurrentProcess().Id + "]";

Counters can be initialized before making any database connections. But the values will be 0 or NULL since no database connections are made.

I have defined all the counter names as constant in a different class as Enumeration type.

public enum DBPerformanceCounters
        {
            NumberOfActiveConnectionPools,
            NumberOfActiveConnections,           
            NumberOfFreeConnections,
            NumberOfNonPooledConnections,
            NumberOfPooledConnections,
            SoftDisconnectsPerSecond,
            SoftConnectsPerSecond,
            NumberOfReclaimedConnections,
            HardConnectsPerSecond,
            HardDisconnectsPerSecond,
            NumberOfActiveConnectionPoolGroups,
            NumberOfInactiveConnectionPoolGroups,
            NumberOfInactiveConnectionPools,
            NumberOfStasisConnections
        }

Client Code will be as follows.

DotNetPerformanceCounters perfCounter = new DotNetPerformanceCounters();
string InstanceName = Assembly.GetEntryAssembly().GetName().Name + "[" + Process.GetCurrentProcess().Id + "]";
perfCounter.InitializeCounters(InstanceName, typeof(CountersList.DBPerformanceCounters));
// Making Connection to the DB
SqlConnection connection = new SqlConnection(connectString);
connection.Open();
SqlCommand command = new SqlCommand("SELECT GETDATE()", connection);
command.ExecuteNonQuery();
perfCounter.PrintCounters();

I have defined my connection string below.

Testconn uses Pooling with minimum pool size of 2 and maximum connections of 5, whereas TestConnNonPooled configuration disables the connection pool.

<connectionStrings>
    <add name="TestConn" providerName="System.Data.SqlClient" connectionString="Data Source=servername;User Id=USERID;Password=PWD;Initial Catalog=DBNAME;Pooling=true;Min Pool Size = 2; Max Pool Size=5;"/>
    <add name="TestConnNonPooled" providerName="System.Data.SqlClient" connectionString="Data Source= servername;User Id=USERID;Password=PWD;Initial Catalog=DBNAME;Pooling=false;"/>
</connectionStrings>

Let's look various examples and outputs below to better understand the usage of counters.

1. Pooling = true; MAX Pool Size = 5; Min Pool Size = 2; # Connection < Min Pool Size
   Following is the output after making one connection using the connection properties mentioned above.
    

   NumberOfActiveConnectionPools = 1 NumberOfActiveConnections = 1 NumberOfFreeConnections = 1 NumberOfNonPooledConnections = 0 NumberOfPooledConnections = 2 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 0 NumberOfReclaimedConnections = 0 HardConnectsPerSecond = 0 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   Commentary -
   
   Number of active connection pool = 1. We mentioned Pooling=true in our connection string. So one pool is created automatically when the first connection to the database is made.
   Number of pooled Connections is 2. Since we mentioned the minimum pool size is 2.
   NumberOfActiveConnections = 1 & NumberOfFreeConnections = 1, 1 Connection was used and still active. The connection was not closed by the caller explicitly. Free connections available in the pool is 1.  
   
2. Pooling = true; MAX Pool Size = 5; Min Pool Size = 2; # Connection > Min Pool Size but < Max Pool size
   
   Now let us make 3 connections in a row using the same connection string. Since Min Pool Size is 2, while making attempts for the 3rd connection, it should either add a new connection to the pool and use them or use the any connections that are released to the pool.
   
   In the below output, take a note on following counters.
   NumberOfActiveConnections is 2 since both the connections were used and still active.
   NumberOfPooledConnections is 2 only. No new connections made to the pool.
   NumberOfFreeConnections is 0, since all 2 are active.
   
   

   NumberOfActiveConnectionPools = 1 NumberOfActiveConnections = 2 NumberOfFreeConnections = 0 NumberOfNonPooledConnections = 0 NumberOfPooledConnections = 2 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 0 NumberOfReclaimedConnections = 0 HardConnectsPerSecond = 0 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   Now when the 3rd connection is made and the query is executed you can see one of the connection is disposed. This is denoted by the counter name SoftConnectsPerSecond. Also NumberOfReclaimedConnections is 2, which means all 2 connections were active and they were returned to the pool forcefully.
   
   Look at the below output for changes.
   
   

   NumberOfActiveConnectionPools = 1 NumberOfActiveConnections = 1 NumberOfFreeConnections = 1 NumberOfNonPooledConnections = 0 NumberOfPooledConnections = 2 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 3.297869 NumberOfReclaimedConnections = 2 HardConnectsPerSecond = 0 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   
3. Pooling = true; MAX Pool Size = 2; Min Pool Size = 2; # Connection > Min Pool Size and > Max Pool size
   
   Before making the 3rd connection attempt, I got the same output as #2. No difference at all.
   
   

   NumberOfActiveConnectionPools = 1 NumberOfActiveConnections = 2 NumberOfFreeConnections = 0 NumberOfNonPooledConnections = 0 NumberOfPooledConnections = 2 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 0 NumberOfReclaimedConnections = 0 HardConnectsPerSecond = 0 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   

   NumberOfActiveConnectionPools = 1 NumberOfActiveConnections = 1 NumberOfFreeConnections = 1 NumberOfNonPooledConnections = 0 NumberOfPooledConnections = 2 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 3.234508 NumberOfReclaimedConnections = 2 HardConnectsPerSecond = 0 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   We did not get any exception because 2 Active connections were not closed explicitly by the application, but they were in the usable state. So the connection were reclaimed and returned to the pool for the subsequent usage. This is not a good scenario as checking for usability state may cause performance degradation. Always programmers recommended closing the connections in order to return them to the pool.  
   
4. Pooling = false;
   I made 3 connections to the database using the same connection string with the pooling turned off.
    

   NumberOfActiveConnectionPools = 0 NumberOfActiveConnections = 0 NumberOfFreeConnections = 0 NumberOfNonPooledConnections = 3 NumberOfPooledConnections = 0 SoftDisconnectsPerSecond = 0 SoftConnectsPerSecond = 0 NumberOfReclaimedConnections = 0 HardConnectsPerSecond = 0.8073593 HardDisconnectsPerSecond = 0 NumberOfActiveConnectionPoolGroups = 1 NumberOfInactiveConnectionPoolGroups = 0 NumberOfInactiveConnectionPools = 0 NumberOfStasisConnections = 0

   
   Counters to notice here are -
   NumberOfNonPooledConnections is 3 since I made 3 connections and all of them are not using database connections pooling.
   NumberOfActiveConnections and NumberOfFreeConnections are 0s. No connections are added to the pool as the pooling is turned off.
   We are going to learn one new counter here.
   The HardConnectsPerSecond is 0.8073593 from the above output. This denotes how many connection disconnects from the database are made. This is a physical database disconnects as oppose to SoftConnectsPerSecond.

Conclusion

I covered some basic database connection scenarios. I recommend readers to try out connection in a multi-threaded fashion to learn about different scenarios.

I would be writing more about ASP .Net Performance counters later this week.