[sql] Support `db.operation.batch.size` attribute

[alanwest]

Refer to the v1.43.0 conventions for attribute descriptions.

Before implementing we need to agree on when and how db.operation.batch.size will be determined.

Few questions/thoughts:

1. SqlClient has a means to create and execute a batch of commands (see SqlBatch). I'm not certain if SqlBatch can be leveraged nor do I know how a batch is executed. Is each command executed individually, or is the entire batch sent where commands are concatenated together with semi-colons?

2. Would we need to resort to parsing a SQL query for the presence of semi-colons? Are semi-colons always the character that delimits commands?

3. If parsing is our only option, we may want to forgo supporting db.operation.batch.size. I'd like to know if there are other languages that are parsing SQL queries to determine db.operation.batch.size.

[martincostello]

Looks like this might be changed: open-telemetry/semantic-conventions#1984

[stevejgordon]

@martincostello Feel free to assign to me. I'm on leave next week though, so can pick it up when I'm back. Unless we need to wait on the open issue you linked.

[alanwest]

Removing this from the milestone for the stable release of the SqlClient instrumentation. New attributes can be added to spans after stability. This will not be considered a breaking change.

[martincostello]

FYI there were some updates to the spec for this in v1.43.0 - I've updated the link in the description to match.

[PetarJerinic]

Went through the code, and here are my findings on the questions from the description:

1. SqlBatch can't be leveraged from DiagnosticSource subscriber since it never reaches subscriber. SqlBatch.ExecuteNonQuery() delegates to SqlCommand that doesn't have a publicly exposed batch count. There is (_RPCList) in SqlCommand.Batch that keeps count, but is not a public member.
2. AddBatchCommand overwrites CommandText (SqlCommand.Batch.cs#L25), and due to it, there is only the last statement of the batch.
3. I focused on Java, seems (OpenTelemetryStatement.java) instrumentation wraps Statement and increments the counter. Java implementation is a wrapper and can make it work that way.

I would suggest that the maintainers of dotnet/SqlClient are asked to expose the batch on the diagnostic payload. Any thoughts? Alternatively, we could read dotnet/SqlClient internals, but any future changes there would break the functionality.

[martincostello]

Yeah, ideally sqlclient would expose this somehow, then we would consume it. Using internals via reflection can cause issues long-term - there was a similar issue elsewhere recently cause by doing this when StackExchange.Redis v3 was released.

[PetarJerinic]

Filed on dotnet/SqlClient#4545