SqlClient: Query execution speed is 2-3 times slower whenever dotnet-counters is connected to an app

Huh, that’s interesting - just updated dotnet-counters to the latest version and now it works correctly. So, that actually was a dotnet-counters bug, and not SqlClient’s one. @Zoxive thanks!

I’m closing this issue since it doesn’t look like there is anything to fix.

@cheenamalhotra here.

After the app is started, just call dotnet-counters monitor -n SqlClientCounters --counters "Microsoft.Data.SqlClient.EventSource" at some point and you’re gonna see the cliff immediately.

@DavoudEshtehari it’s very unlikely the perf issue is related to the counters (calculating them is very trivial), but rather to unrelated events in the same class.

@roji the obvious way to fix this issue is to add lots of events and counters to npgsql so that the speed decrease is equivalent 😀

I’ll take a look at the repro and see if there’s anything that can clearly be improved. As I mentioned SqlClient outputs a huge amount of events because they’re an excellent live debugging aid so it’s unlikely that anything can be entirely removed. Splitting counters and events may be possible.

In my mind, there’s trace logging/diagnostics which you’d only ever turn off in local debugging or similar, and then there’s normal diagnostics which is meant to be suitable for a production system.

If there needs to be a split of production debugging data and production monitoring data that can be done if we can identify what people want to see in which scenario. Perhaps it just needs some guidance on which events to use in production? For example the SNI events are worthless unless you’re debugging but things like ExecuteReader start and finish are quite valuable for continuous monitoring.

The current and long term historic configuration for events in this library is for debug tracing and if you turn on everything you’ll see a lot of data and take the perf hit that it generates. It’s another aspect of being a very old codebase, it predates the current common use of events for casual production monitoring.

I’d say first step split out the counters. Then investigate what users are trying to get out of events, if anything, and make that as low profile as possible without degrading the ability to use rich events for support. Does that seem reasonable? If so how would we go about identifying users needs from events?

I don’t see any reason from the code that the events and counters can’t be split into separate classes, I’d suggest Microsoft.Data.SqlClient.EventSource for the tracing events and Microsoft.Data.SqlClient.EventCounters for the counters to allow independent use. That’s a policy decision @David-Engel @cheenamalhotra .