SqlClient Instrumentation for OpenTelemetry

This is an Instrumentation Library, which instruments Microsoft.Data.SqlClient and System.Data.SqlClient and collects traces about database operations.

This component is based on v1.44 of database semantic conventions. For details on the default set of attributes that are added, check out the Traces and Metrics sections below.

[!WARNING] Instrumentation is not working with Microsoft.Data.SqlClient v3.* due to the issue. It was fixed in 4.0 and later.

Steps to enable OpenTelemetry.Instrumentation.SqlClient

Step 1: Install Package

Add a reference to the OpenTelemetry.Instrumentation.SqlClient package. Also, add any other instrumentations & exporters you will need.

dotnet add package OpenTelemetry.Instrumentation.SqlClient

Step 2: Enable SqlClient Instrumentation at application startup

SqlClient instrumentation must be enabled at application startup.

Traces

The following example demonstrates adding SqlClient traces instrumentation to a console application. This example also sets up the OpenTelemetry Console exporter, which requires adding the package OpenTelemetry.Exporter.Console to the application.

using OpenTelemetry.Trace;

public class Program
{
    public static void Main(string[] args)
    {
        using var tracerProvider = Sdk.CreateTracerProviderBuilder()
            .AddSqlClientInstrumentation()
            .AddConsoleExporter()
            .Build();
    }
}

The instrumentation adheres to the semantic conventions for database client spans. An activity emitted by the instrumentation will include the following list of attributes:

• error.type
• db.namespace
• db.operation.name
• db.query.summary
• db.query.text
• db.response.status_code
• db.stored_procedure.name
• db.system.name
• server.address
• server.port

Metrics

The following example demonstrates adding SqlClient metrics instrumentation to a console application. This example also sets up the OpenTelemetry Console exporter, which requires adding the package OpenTelemetry.Exporter.Console to the application.

using OpenTelemetry.Metrics;

public class Program
{
    public static void Main(string[] args)
    {
        using var meterProvider = Sdk.CreateMeterProviderBuilder()
            .AddSqlClientInstrumentation()
            .AddConsoleExporter()
            .Build();
    }
}

The instrumentation adheres to the semantic conventions for database client metrics.

Currently, the instrumentation supports the following metric and attributes.

• error.type
• db.namespace
• db.operation.name
• db.query.summary
• db.response.status_code
• db.stored_procedure.name
• db.system.name
• server.address
• server.port

ASP.NET Core

For an ASP.NET Core application, adding instrumentation is typically done in the ConfigureServices of your Startup class. Refer to documentation for OpenTelemetry.Instrumentation.AspNetCore.

ASP.NET

For an ASP.NET application, adding instrumentation is typically done in the Global.asax.cs. Refer to the documentation for OpenTelemetry.Instrumentation.AspNet.

Advanced configuration

This instrumentation can be configured to change the default behavior by using SqlClientTraceInstrumentationOptions.

EnrichWithSqlCommand

[!NOTE] EnrichWithSqlCommand is available on .NET runtimes only.

This option can be used to enrich the activity with additional information from the raw SqlCommand object. The EnrichWithSqlCommand action is called only when activity.IsAllDataRequested is true. It contains the activity itself (which can be enriched), the name of the event, and the actual raw object.

Currently there is only one event name reported, "OnCustom". The actual object is Microsoft.Data.SqlClient.SqlCommand for Microsoft.Data.SqlClient and System.Data.SqlClient.SqlCommand for System.Data.SqlClient.

The following code snippet shows how to add additional tags using EnrichWithSqlCommand.

using var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSqlClientInstrumentation(opt => opt.EnrichWithSqlCommand
        = (activity, obj) =>
    {
        if (obj is SqlCommand cmd)
        {
            activity.SetTag("db.commandTimeout", cmd.CommandTimeout);
        }
    })
    .Build();

Processor, is the general extensibility point to add additional properties to any activity. The EnrichWithSqlCommand option is specific to this instrumentation, and is provided to get access to SqlCommand object.

RecordException

[!NOTE] RecordException is available on .NET runtimes only.

This option can be set to instruct the instrumentation to record SqlExceptions as Activity events.

The default value is false and can be changed by the code like below.

using var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSqlClientInstrumentation(
        options => options.RecordException = true)
    .AddConsoleExporter()
    .Build();

Filter

[!NOTE] Filter is available on .NET runtimes only.

This option can be used to filter out activities based on the properties of the SqlCommand object being instrumented using a Func<object, bool>. The function receives an instance of the raw SqlCommand and should return true if the telemetry is to be collected, and false if it should not. The parameter of the Func delegate is of type object and needs to be cast to the appropriate type of SqlCommand, either Microsoft.Data.SqlClient.SqlCommand or System.Data.SqlClient.SqlCommand. The example below filters out all commands that are not stored procedures.

using var traceProvider = Sdk.CreateTracerProviderBuilder()
   .AddSqlClientInstrumentation(
       opt =>
       {
           opt.Filter = cmd =>
           {
               if (cmd is SqlCommand command)
               {
                   return command.CommandType == CommandType.StoredProcedure;
               }

               return false;
           };
       })
   .AddConsoleExporter()
   .Build();

Experimental features

[!NOTE] Experimental features are not enabled by default and can only be activated with environment variables. They are subject to change or removal in future releases.

DB query parameters

[!NOTE] This feature is available on .NET runtimes only.

The OTEL_DOTNET_EXPERIMENTAL_SQLCLIENT_ENABLE_TRACE_DB_QUERY_PARAMETERS environment variable controls whether db.query.parameter.<key> attributes are emitted.

Query parameters may contain sensitive data, so only enable this experimental feature if your queries and/or environment are appropriate for enabling this option.

OTEL_DOTNET_EXPERIMENTAL_SQLCLIENT_ENABLE_TRACE_DB_QUERY_PARAMETERS is implicitly false by default. When set to true, the instrumentation will set db.query.parameter.<key> attributes for each of the query parameters associated with a database command.

Returned rows

[!NOTE] This feature is available on .NET runtimes only.

The OTEL_DOTNET_EXPERIMENTAL_SQLCLIENT_ENABLE_RECORD_RETURNED_ROWS environment variable controls whether the db.response.returned_rows attribute is emitted.

OTEL_DOTNET_EXPERIMENTAL_SQLCLIENT_ENABLE_RECORD_RETURNED_ROWS is implicitly false by default. When set to true, the instrumentation records the number of rows the command returned, derived from the SqlClient connection statistics that are collected automatically while the instrumentation is enabled.

[!NOTE] The attribute is only recorded for commands executed with ExecuteNonQuery() or ExecuteScalar(), including their asynchronous overloads. The connection statistics the value is derived from are only updated as the response from the server is consumed, which for ExecuteReader() and ExecuteXmlReader() happens after the command has finished executing and the span has already ended. No attribute is emitted for those commands, rather than one whose value does not describe the rows the command returned.

SqlClient only starts collecting the statistics that the value is derived from when a connection is opened, so a connection which was already open before the instrumentation was registered does not report a value. Either open connections after the TracerProvider has been built, or set StatisticsEnabled to true on such connections yourself.

Trace Context Propagation

[!NOTE] Only CommandType.Text commands are supported for trace context propagation. Only .NET runtimes are supported.

Other command types do not get their own trace context. They observe whatever was last set on the connection. See below.

Database trace context propagation can be enabled by setting OTEL_DOTNET_EXPERIMENTAL_SQLCLIENT_ENABLE_TRACE_CONTEXT_PROPAGATION environment variable to true. This uses the SET CONTEXT_INFO command to set traceparent information for the current connection, which results in an additional round-trip to the database.

CONTEXT_INFO is session state. It is scoped to the connection rather than to the command that set it, and the instrumentation only ever overwrites it, never clears it. This has a few consequences worth understanding before enabling the feature.

• Commands other than CommandType.Text run with the traceparent of the most recent text command on the same connection, if any, which may belong to an unrelated trace. A stored procedure is therefore not merely missing its own trace context, it can be attributed server-side to a different one.
• With MultipleActiveResultSets=true, a command interleaved on the same connection overwrites CONTEXT_INFO while an earlier reader is still streaming, so the still-running query is re-attributed server-side to the trace of the interleaved command.
• Connection pooling does not carry the value across connections. A pooled connection is reset before it is reused, and that reset clears CONTEXT_INFO, so the first command on the reused connection does not observe the previous one's traceparent. The reset is deferred until the connection is next used, so an idle pooled connection still reports the previous traceparent in sys.dm_exec_sessions.
• The additional round-trip is paid for nearly every text command, not only for the sampled ones. A command whose span is dropped by the sampler generally still sets CONTEXT_INFO, with the sampled flag of the traceparent cleared.
• Filter does not suppress propagation. It is evaluated after CONTEXT_INFO has been set, so a command excluded from telemetry still writes its traceparent to the database, where it refers to a span that is never exported.

Activity Duration calculation

Activity.Duration represents the time the underlying connection takes to execute the command/query. Completing the operation includes the time up to determining that the request was successful. It doesn't include the time spent reading the results from a query set (for example enumerating all the rows returned by a data reader).

This is illustrated by the code snippet below:

using var connection = new SqlConnection("...");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "select top 100000 * from Users";

// Activity duration starts
using var reader = command.ExecuteReader();
// Activity duration ends

// Not included in the Activity duration
while (reader.Read())
{
}

References

• OpenTelemetry Project
• Semantic conventions for database client spans
• Semantic conventions for database client metrics
• Semantic conventions for Microsoft SQL Server client operations