Sqlclient: Implement DBConnectionPoolCounters for .NET Core

[cheenamalhotra]

Currently SqlClient supports Performance Counters in .NET Framework implementation, but since it was not supported in .NET Core 2.1 when the driver was ported over, the support was removed in CoreFx.

.NET Core 3.0 added support for PerformanceCounters and that brings an opportunity to introduce DbConnectionPoolCounters for .NET Core applications as well.

This would require to build a separate DLL for .NET Core 3.1+ with similar API support.

[roji]

Following our conversation on diagnostics today, it would be good to have a good database perf counters story, that's consistent across database providers and well-integrated in VS.

Some resources:

• Blog post announcing the .NET Core 3.0 diagnostics improvements (including dotnet counters): https://devblogs.microsoft.com/dotnet/introducing-diagnostics-improvements-in-net-core-3-0/
• Quick demo of Npgsql's current perf counters being displayed by dotnet counters: https://asciinema.org/a/256182. Note that the list of counters and their names isn't final and can change based on our discussions.
• Npgsql's EventSource producing the counters: https://github.com/npgsql/npgsql/blob/dev/src/Npgsql/NpgsqlEventSource.cs.

Note that the new EventSource-based API is completely different from the old .NET Framework Performance Counters API. Since consumption from outside the process is also completely different, we really can decide which counters to expose, rather than just carry over what existed in .NET Framework.

@karpinsn @davidmikh what logic is planned to be built into VS around perf counters? Is the idea for you guys to know about certain specific event source names (e.g. System.Runtime), and even specific counters (e.g. cpu-usage within System.Runtime), to display them in a special way? Or would this be a totally generic feature, which ideally allows the user to choose any event source name exposed by the process, and any counter within those?

/cc @ajcvickers

[daniel-munch-cko]

Very interested in this as well. Note that there's been a similar discussion for the MySqlConnector around here: https://github.com/mysql-net/MySqlConnector/issues/471

[roji]

@daniel-munch-cko note that this is about performance counters, which aren't the same as DiagnosticSource events (though both are indeed related to diagnostics).

[karpinsn]

@roji sorry about the delayed response. Initially the tool will just have known counters, System.Runtime, ASP.NET counters, and hopefully some relevant Database ones. We are working with the runtime to figure out a way to uncover / probe for generic counters. I could also see us adding some UI to specify generic counters before collection to support user created ones, but that would come in updates to the tool.

[cheenamalhotra]

Note that the new EventSource-based API is completely different from the old .NET Framework Performance Counters API. Since consumption from outside the process is also completely different, we really can decide which counters to expose, rather than just carry over what existed in .NET Framework.

Hi @roji

I did consider this before starting work on PR #509 and as you know the new "dotnet counters" aren't supported for .NET Framework 4.6 that we support. The EventCounter class is only supported for v4.7.1 onwards.

While PerformanceCounter was also added to .NET Core 3.0, my initial thoughts were there's probably a future for this, otherwise why not let everyone use Event Counters. Maybe @danmosemsft can help us here to know which one should libraries opt-in.

However, supporting Event Counters can be considered if there is high demand and is planned for uniformity in Data providers (as we also support Diagnostic Source Tracing in .NET Core) but we generally want to have uniform support for .NET Framework and .NET Core if possible.

cc @David-Engel @saurabh500 @ajcvickers

[roji]

FYI in Npgsql I used PollingCounter and IncrementingPollingCounter, following the pattern in the .NET runtime.

Unless I'm mistaken, this is the option with the least overhead, since you're not emitting emitting events as they occur (as with EventCounter), but rather aggregating values in some internal regular variable, and these get polled on an interval determined by the attached user. Unless I'm mistaken, if dotnet counters (or similar) doesn't attach, the polling counters are never activated and there is zero overhead (except for updating the variable). I think this is the best way to go in modern .NET Core - I don't think that performance counters in .NET Core < 3.x should be a goal at this point.

I wouldn't worry too much about uniformity across .NET Framework and .NET Core. The new performance counter support (e.g. dotnet counter, PollingCounter) only exist for .NET Core, and .NET Framework in any case has the old-style, Windows-only PerformanceCounter facility which doesn't exist in Core.

[danmosemsft]

Maybe @danmosemsft can help us here to know which one should libraries opt-in.

Although my team owns the PerformanceCounter library, we aren't particularly knowledgeable about when to use which kinds of counters. @brianrob who is a good person to give guidance?

[brianrob]

@josalem are you the right person to talk to about EventCounters?

I think the right answer here is to use EventCounters for .NET Core regardless of the fact that on full framework you used Windows Performance Counters. EventCounters is the cross-platform way to emit counters and comes with the side benefit that it's easy to get detailed logs because this essentially just converts EventSource events into aggregated counters.

[josalem]

@sywhang implemented the EventCounters API and can speak to it better than I can. @brianrob is correct, though, you should be using the EventCounters API in .NET Core.

[cheenamalhotra]

@brianrob @josalem Thank you for your suggestions. Since we already added support for EventSource in .NET Core, it would be a good idea to support Event Counters as well. We will definitely consider introducing support for the same!

Could you also confirm if Performance Counters have any future in .NET Core since they were reintroduced in .NET Core v3.0? Supporting the same is not challenging for us since implementation already exists but we'd like to know more about the motivation behind reintroduction and if it's something that maybe of interest/benefit for existing SqlClient customers coming from .NET Framework? Also if you're aware of any other library supporting them in .NET Core?

[brianrob]

The story is that Windows Performance Counters were added as part of the Windows Compatibility Pack to ensure that customers that consumed or produced counters could successfully port their apps to .NET Core. Beyond that, we're not recommending nor building support for counters into the product. As an example, .NET full framework produces Windows Performance Counter data, but .NET Core does not and there is no plan to add this to .NET Core. Instead, the .NET Core runtime produces EventCounter data and the performance counter tooling story for .NET Core is based around EventCounters.

[cheenamalhotra]

Thanks @brianrob

That clarifies things for us. I will then close this issue and open new one for supporting Event Counters instead in SqlClient.