Programatically accessing ADO.NET 2.0 performance counters

I have found that many times using Perfmon with the new ADO.NET counters is not ideal, a much better approach may be to get the performance counter information directly through code. There is only one minor complication, to make it easy to differentiate between instances with the same name we have formatted instance names as “<instancename>(<pid>)”. here is an example that can help get you started:

I really like this example because it shows off one of my favorite new performance counters, the “NumberOfReclaimedConnections”. Whenever you see this counter increasing you know that your code is leaking connections! In this example it looks like we are doing everything right since we are clearly calling connection.Close(). The problem is that this call is not “guaranteed”, if your code throws an exception it will not get called. In this case Close never gets called because I have “doctored“ ExecuteNonQuery to always throw.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Runtime.InteropServices; //for DllImport

using System.Reflection; //for Assembly

using System.Diagnostics; //for PerformanceCounter

public class Repro

{

            public enum APC //Ado.net Performance Counters

            {

                        NumberOfActiveConnectionPools,

                        NumberOfReclaimedConnections,

                        HardConnectsPerSecond,

                        HardDisconnectsPerSecond,

                        NumberOfActiveConnectionPoolGroups,

                        NumberOfInactiveConnectionPoolGroups,

                        NumberOfInactiveConnectionPools,

                        NumberOfNonPooledConnections,

                        NumberOfPooledConnections,

                        NumberOfStasisConnections

                        //SoftConnectsPerSecond (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

                        //SoftDisconnectsPerSecond (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

                        //NumberOfActiveConnections (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

                        //NumberOfFreeConnections (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

            }

            PerformanceCounter[] PerfCounters;

            public void SetUpPerformanceCounters()

            {

                        this.PerfCounters = new PerformanceCounter[10];

                        string instanceName = GetInstanceName();

                        Type apc = typeof(APC);

                        int i = 0;

                        foreach (string s in Enum.GetNames(apc) )

                        {

                                    this.PerfCounters[i] = new PerformanceCounter();

                                    this.PerfCounters[i].CategoryName = ".NET Data Provider for SqlServer";

                                    this.PerfCounters[i].CounterName = s;

                                    this.PerfCounters[i].InstanceName = instanceName;

                                    i++;

                        }

            }

            [DllImport("kernel32.dll", SetLastError = true)]

            static extern int GetCurrentProcessId();

            private string GetInstanceName()

            {

                        //[EDIT: this works for Winforms apps.

                        string instanceName = Assembly.GetEntryAssembly().GetName().Name;

//For ASP.NET applications your instanceName will be your CurrentDomain's

//FriendlyName. Replace the line above with:

                             //instanceName = AppDomain.CurrentDomain.FriendlyName.ToString();

                        string pid = GetCurrentProcessId().ToString();

                        instanceName = instanceName + "(" + pid + ")";

                        Console.WriteLine(instanceName);

                        return instanceName;

            }

            public void WritePerformanceCounters()

            {

                        Console.WriteLine("----------------");

                        foreach (PerformanceCounter p in this.PerfCounters)

                        {

                                    Console.WriteLine(p.CounterName + " = " + p.NextValue());

                        }

            }

            public static int Main(string[] args)

            {

                        Repro repro = new Repro();

                        repro.SetUpPerformanceCounters();

                        for (int i = 0; i <= 500; i++)

                        {

                                    try

                                    {

                                                Console.Write("----------------Connection number = " + i + " ");

                                                repro.LeakConnections();

                                    }

                                    catch (SqlException) { }

                        }

                        return 1;

            }

            public void LeakConnections()

            {

                        SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5");

                        sqlconnection1.Open();

                        this.WritePerformanceCounters();

                        SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                        sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

                        sqlcommand1.ExecuteNonQuery(); //this throws a SqlException

                        sqlconnection1.Close(); //We are calling connection close, and we are still leaking connections.

            }

}

To fix this code you need to guarantee that close gets called by placing the sqlconnection in a “using” statement or by calling Close in the Finally clause of a try finally block.

Rambling out, Standard Disclaimer: this post is provided “AS IS” and confers no rights.