Event counters in SqlClient

APPLIES TO: .NET Framework .NET Core .NET Standard

Download ADO.NET

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
...

See also