There are a number of useful performance counters exposed for System.Data.SqlClient that can provide some nice insight into what's going on under the hood of your applications. Today I found myself monitoring the
NumberOfReclaimedConnections counter to track down some connections that weren't being properly disposed.
It's certainly not ideal to need to log into the servers to monitor this, so I went off looking for how to expose this through our Datadog dashboards. On top of just being able to query it, this would also mean I could create some monitors and alerts based on the counters as well. Datadog provides support for Windows Management Instrumentation (WMI) out of the box, so it was just a matter of figuring out exactly how to query these ADO.NET counters.
Figuring out exactly what the class name was to get access to these counters required a bit of digging that brought me to some parts of the internet that time seems to have forgotten, so I thought I'd just document it here to hopefully save someone else (or future me) the same digging.
wmi_check.yaml file (see the WMI integration instructions for how to set this), add the following:
# ADO.NET performance counters - class: Win32_PerfFormattedData_NETDataProviderforSqlServer_NETDataProviderforSqlServer metrics: - [NumberOfActiveConnectionPools, adonet.activeconnectionpools.count, gauge] - [NumberOfReclaimedConnections, adonet.reclaimedconnections.count, gauge] - [HardConnectsPerSecond, adonet.hardconnects.rate, rate] - [HardDisconnectsPerSecond, adonet.harddisconnects.rate, rate] - [NumberOfActiveConnectionPoolGroups, adonet.activeconnectionpoolgroups.count, gauge] - [NumberOfInactiveConnectionPoolGroups, adonet.inactiveconnectionpoolgroups.count, gauge] - [NumberOfInactiveConnectionPools, adonet.inactiveconnectionpools.count, gauge] - [NumberOfNonPooledConnections, adonet.nonpooledconnections.count, gauge] - [NumberOfPooledConnections, adonet.pooledconnections.count, gauge] - [NumberOfStasisConnections, adonet.stasisconnections.count, gauge] tag_by: Name
After restarting your Datadog agent it will now start reporting the ADO.NET counters using the names provided here with the
adonet. prefix. For each, the instance name for each counter will be included as a tag, allowing you to associate all of the incoming data with the process it came from.