Event counters in SqlClient

Article
11/18/2022

Applies to: .NET Framework .NET Core .NET Standard

Important

Event counters are available when targeting .NET Core 3.1 and higher or .NET Standard 2.1 and higher. This feature is available starting with Microsoft.Data.SqlClient version 3.0.0.

You can use Microsoft.Data.SqlClient event counters to monitor the status of your application and the connection resources that it uses. Event counters can be monitored by .NET CLI global tools and perfView or can be accessed programmatically using the EventListener class in the System.Diagnostics.Tracing namespace.

Available event counters

Currently there are 16 different event counters available for Microsoft.Data.SqlClient as described in the following table:

Name	Display name	Description
active-hard-connections	Actual active connections currently made to servers	The number of connections that are currently open to database servers.
hard-connects	Actual connection rate to servers	The number of connections per second that are being opened to database servers.
hard-disconnects	Actual disconnection rate from servers	The number of disconnects per second that are being made to database servers.
active-soft-connects	Active connections retrieved from the connection pool	The number of already-open connections being consumed from the connection pool.
soft-connects	Rate of connections retrieved from the connection pool	The number of connections per second that are being consumed from the connection pool.
soft-disconnects	Rate of connections returned to the connection pool	The number of connections per second that are being returned to the connection pool.
number-of-non-pooled-connections	Number of connections not using connection pooling	The number of active connections that aren't pooled.
number-of-pooled-connections	Number of connections managed by the connection pool	The number of active connections that are being managed by the connection pooling infrastructure.
number-of-active-connection-pool-groups	Number of active unique connection strings	The number of unique connection pool groups that are active. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
number-of-inactive-connection-pool-groups	Number of unique connection strings waiting for pruning	The number of unique connection pool groups that are marked for pruning. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
number-of-active-connection-pools	Number of active connection pools	The total number of connection pools.
number-of-inactive-connection-pools	Number of inactive connection pools	The number of inactive connection pools that haven't had any recent activity and are waiting to be disposed.
number-of-active-connections	Number of active connections	The number of active connections that are currently in use.
number-of-free-connections	Number of ready connections in the connection pool	The number of open connections available for use in the connection pools.
number-of-stasis-connections	Number of connections currently waiting to be ready	The number of connections currently awaiting completion of an action and which are unavailable for use by the application.
number-of-reclaimed-connections	Number of reclaimed connections from GC	The number of connections that have been reclaimed through garbage collection where `Close` or `Dispose` wasn't called by the application. Note Not explicitly closing or disposing connections hurts performance.

Retrieve event counter values

There are two primary ways of consuming EventCounters, either in-proc, or out-of-proc. For more information, see Consume EventCounters.

Consume out-of-proc

In Windows, you can use PerfView and Xperf to collect event counters data. For more information, see Enable event tracing in SqlClient. You can use dotnet-counters and dotnet-trace, which are cross platform .NET tools to monitor and collect event counters data.

Out-of-proc example

The following command runs and collects SqlClient event counters values once every second. Replacing EventCounterIntervalSec=1 with a higher value allows collection of a smaller trace with less granularity in the counter data.

PerfView /onlyProviders=*Microsoft.Data.SqlClient.EventSource:EventCounterIntervalSec=1 run "<application-Path>"

The following command collects SqlClient event counters values once every second.

dotnet-trace collect --process-id <pid> --providers Microsoft.Data.SqlClient.EventSource:0:1:EventCounterIntervalSec=1

The following command monitors SqlClient event counters values once every three seconds.

dotnet-counters monitor Microsoft.Data.SqlClient.EventSource -p <process-id> --refresh-interval 3

The following command monitors selected SqlClient event counters values once every second.

dotnet-counters monitor Microsoft.Data.SqlClient.EventSource[hard-connects,hard-disconnects] -p <process-id>

Consume in-proc

You can consume the counter values via the EventListener API. An EventListener is an in-proc way of consuming any event written by instances of an EventSource in your application. For more information, see EventListener.

In-proc example

The following sample code captures Microsoft.Data.SqlClient.EventSource events using EventCounterIntervalSec=1. It writes the counter name and its Mean value on each event counter update.

Note

It's required to specify the EventCounterIntervalSec property value when enabling this event.

using System;
using System.Collections.Generic;
using System.Diagnostics.Tracing;
using System.Linq;

// This listener class will listen for events from the SqlClientEventSource class.
// SqlClientEventSource is an implementation of the EventSource class which gives 
// it the ability to create events.
public class EventCounterListener : EventListener
{
    protected override void OnEventSourceCreated(EventSource eventSource)
    {
        // Only enable events from SqlClientEventSource.
        if (eventSource.Name.Equals("Microsoft.Data.SqlClient.EventSource"))
        {
            var options = new Dictionary<string, string>();
            // define time interval 1 second
            // without defining this parameter event counters will not enabled
            options.Add("EventCounterIntervalSec", "1");
            // enable for the None keyword
            EnableEvents(eventSource, EventLevel.Informational, EventKeywords.None, options);
        }
    }

    // This callback runs whenever an event is written by SqlClientEventSource.
    // Event data is accessed through the EventWrittenEventArgs parameter.
    protected override void OnEventWritten(EventWrittenEventArgs eventData)
    {
        if (eventData.Payload.FirstOrDefault(p => p is IDictionary<string, object> x && x.ContainsKey("Name")) is IDictionary<string, object> counters)
        {
            if (counters.TryGetValue("DisplayName", out object name) && name is string cntName
                && counters.TryGetValue("Mean", out object value) && value is double cntValue)
            {
                // print event counter's name and mean value
                Console.WriteLine($"{cntName}\t\t{cntValue}");
            }
        }
    }
}

class Program
{
    static void Main(string[] args)
    {
        // Create a new event listener
        using (var listener = new EventCounterListener())
        {
            string connectionString = "Data Source=localhost; Integrated Security=true";

            for (int i = 0; i < 50; i++)
            {
                // Open a connection
                SqlConnection cnn = new SqlConnection(connectionString);
                cnn.Open();
                // wait for sampling interval happens
                System.Threading.Thread.Sleep(500);
            }
        }
    }
}

Actual active connections currently made to servers           0
Active connections retrieved from the connection pool         26
Number of connections not using connection pooling            0
Number of connections managed by the connection pool          26
Number of active unique connection strings              1
Number of unique connection strings waiting for pruning       0
Number of active connection pools               1
Number of inactive connection pools             0
Number of active connections            26
Number of ready connections in the connection pool            0
Number of connections currently waiting to be ready           0
...