Event counters in SqlClient
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
...