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.

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

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.

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.

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.