OpenTelemetry in Action: Optimizing Database Operations

The Basics of Observability and OpenTelemetry

Using OpenTelemetry to monitor databases

Using OpenTelemetry to Detect Database Performance Issues

Exporting OTEL traces from .NET application

Demonstration

Spinning up the database

docker run \
-e "ACCEPT_EULA=Y" \
-e "SA_PASSWORD=Str0ngPa$$w0rd" \
-p 1433:1433 \
--name monolith-db \
--hostname sql1 \
-d mcr.microsoft.com/mssql/server:2019-latest
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'EMSDb')
BEGIN
CREATE DATABASE EMSDb
END
GO
USE EMSDbIF OBJECT_ID('[dbo].[Timekeeping]', 'U') IS NULL
BEGIN
CREATE TABLE [Timekeeping] (
[EmployeeId] INT NOT NULL,
[ProjectId] INT NOT NULL,
[WeekClosingDate] DATETIME NOT NULL,
[HoursWorked] INT NOT NULL,
CONSTRAINT [PK_Timekeeping] PRIMARY KEY CLUSTERED ([EmployeeId] ASC, [ProjectId] ASC, [WeekClosingDate] ASC)
)
END
GO
IF OBJECT_ID('[dbo].[Payroll]', 'U') IS NULL
BEGIN
CREATE TABLE [Payroll] (
[EmployeeId] INT NOT NULL,
[PayRateInUSD] MONEY DEFAULT 0 NOT NULL,
CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
)
END
GO
TRUNCATE TABLE Payroll
TRUNCATE TABLE Timekeeping
INSERT INTO Payroll Values(1, 100)
INSERT INTO Payroll Values(2, 200)
INSERT INTO Payroll Values(3, 300)
INSERT INTO Timekeeping Values(1, 1111, GETDATE(), 10)
INSERT INTO Timekeeping Values(1, 2222, GETDATE(), 15)
INSERT INTO Timekeeping Values(2, 1111, GETDATE(), 15)
INSERT INTO Timekeeping Values(3, 2222, GETDATE(), 20)
GO

Implementing the API endpoints

using System.Data.SqlClient;
using System.Diagnostics;
using Dapper;
using OpenTelemetry.Exporter;
using OpenTelemetry.Resources;
using OpenTelemetry.Trace;
var builder = WebApplication.CreateBuilder(args);var lsToken = builder.Configuration.GetValue<string>("LsToken");builder.Services.AddScoped(_ =>
new SqlConnection(
builder.Configuration.GetConnectionString("EmployeeDbConnectionString")
)
);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();app.UseSwagger();
app.UseSwaggerUI();
app.MapGet("/ems/pay/{empId}", async (int empId, SqlConnection db) =>
{
// op 1
var payroll =
await db.QuerySingleOrDefaultAsync<Payroll>("SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId",
new { EmpId = empId });
// op 2
var projects = await db.QueryAsync<Timekeeping>("SELECT EmployeeId,ProjectId,WeekClosingDate,HoursWorked FROM Timekeeping WHERE EmployeeId=@EmpId",
new { EmpId = empId });
var moneyEarned = projects.Sum(p => p.HoursWorked) * payroll.PayRateInUSD;
return Results.Ok(moneyEarned);
})
.WithName("GetPayment")
.Produces(StatusCodes.Status200OK);
app.MapPost("/ems/billing/pay-raise/", async (SqlConnection db) =>
{
var recordsAffected = await db.ExecuteAsync("UPDATE Payroll SET PayRateInUSD = 300 WHERE PayRateInUSD < 300");
return Results.Ok(recordsAffected);
})
.WithName("Pay-Raise")
.Produces(StatusCodes.Status200OK);
app.MapPost("/ems/payroll/remove/{empId}", async (int empId, SqlConnection db) =>
{
Payroll payrollRecord = new();
async Task DeleteRecord()
{
db.Open();
await using var tr = await db.BeginTransactionAsync();
await db.ExecuteAsync("DELETE FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId }, tr);
Thread.Sleep(5000);
await tr.CommitAsync();
}
async Task GetRecord()
{
await using var db1 =
new SqlConnection(builder.Configuration.GetConnectionString("EmployeeDbConnectionString"));
Thread.Sleep(100);
db1.Open();
payrollRecord =
await db1.QuerySingleOrDefaultAsync<Payroll>(
"SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId });
await db1.CloseAsync();
}
await Task.WhenAll(DeleteRecord(), GetRecord()); return Results.Ok(payrollRecord);
})
.WithName("RemoveEmployeeFromPayroll")
.Produces(StatusCodes.Status200OK);
app.MapPost("/ems/add-employee/{empId}", async (int empId, SqlConnection db) =>
{
//op 1
await db.ExecuteAsync("INSERT INTO Payroll Values(@EmployeeId, @PayRateInUSD)",
new Payroll { EmployeeId = empId, PayRateInUSD = 100 });
// Simulate service call
// Mock network call delay
Thread.Sleep(1000);
//op 2
await db.ExecuteAsync(
"INSERT INTO Timekeeping Values(@EmployeeId, @ProjectId, @WeekClosingDate, @HoursWorked)",
new Timekeeping
{ EmployeeId = empId, HoursWorked = 0, ProjectId = 1, WeekClosingDate = DateTime.Today });
return Results.Ok(); })
.WithName("AddEmployee")
.Produces(StatusCodes.Status201Created);
app.Run();
public class Timekeeping
{
public int EmployeeId { get; set; }
public int ProjectId { get; set; }
public DateTime WeekClosingDate { get; set; }
public int HoursWorked { get; set; }
}
public class Payroll
{
public int EmployeeId { get; set; }
public decimal PayRateInUSD { get; set; }
}

Adding instrumentation

<PackageReference Include="OpenTelemetry" Version="1.2.0-rc2" />
<PackageReference Include="OpenTelemetry.Exporter.OpenTelemetryProtocol" Version="1.2.0-rc2" />
<PackageReference Include="OpenTelemetry.Extensions.Hosting" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.AspNetCore" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.Http" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.SqlClient" Version="1.0.0-rc9" />
// Configure tracing
builder.Services.AddOpenTelemetryTracing(builder => builder
// Customize the traces gathered by the HTTP request handler
.AddAspNetCoreInstrumentation(options =>
{
// Only capture the spans generated from the ems/* endpoints
options.Filter = context => context.Request.Path.Value?.Contains("ems") ?? false;
options.RecordException = true;
// Add metadata for the request such as the HTTP method and response length
options.Enrich = (activity, eventName, rawObject) =>
{
switch (eventName)
{
case "OnStartActivity":
{
if (rawObject is not HttpRequest httpRequest)
{
return;
}
activity.SetTag("requestProtocol", httpRequest.Protocol);
activity.SetTag("requestMethod", httpRequest.Method);
break;
}
case "OnStopActivity":
{
if (rawObject is HttpResponse httpResponse)
{
activity.SetTag("responseLength", httpResponse.ContentLength);
}
break;
}
}
};
})
// Customize the telemetry generated by the SqlClient
.AddSqlClientInstrumentation(options =>
{
options.EnableConnectionLevelAttributes = true;
options.SetDbStatementForStoredProcedure = true;
options.SetDbStatementForText = true;
options.RecordException = true;
options.Enrich = (activity, x, y) => activity.SetTag("db.type", "sql");
})
.AddSource("my-corp.ems.ems-api")
// Create resources (key-value pairs) that describe your service such as service name and version
.SetResourceBuilder(ResourceBuilder.CreateDefault().AddService("ems-api")
.AddAttributes(new[] { new KeyValuePair<string, object>("service.version", "1.0.0.0") }))
// Ensures that all activities are recorded and sent to exporter
.SetSampler(new AlwaysOnSampler())
// Exports spans to Lightstep
.AddOtlpExporter(otlpOptions =>
{
otlpOptions.Endpoint = new Uri("https://ingest.lightstep.com:443/traces/otlp/v0.9");
otlpOptions.Headers = $"lightstep-access-token={lsToken}";
otlpOptions.Protocol = OtlpExportProtocol.HttpProtobuf;
}));
var activitySource = new ActivitySource("my-corp.ems.ems-api");
app.MapGet("/ems/pay/{empId}", async (int empId, SqlConnection db) =>
{
using var activity = activitySource.StartActivity("Chatty db operation", ActivityKind.Server);
activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);
// op 1
var payroll =
await db.QuerySingleOrDefaultAsync<Payroll>("SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId",
new { EmpId = empId });
// op 2
var projects = await db.QueryAsync<Timekeeping>("SELECT EmployeeId,ProjectId,WeekClosingDate,HoursWorked FROM Timekeeping WHERE EmployeeId=@EmpId",
new { EmpId = empId });
var moneyEarned = projects.Sum(p => p.HoursWorked) * payroll.PayRateInUSD;
return Results.Ok(moneyEarned);
})
.WithName("GetPayment")
.Produces(StatusCodes.Status200OK);

Sending Instrumentation Data to Lightstep

Your API key in Lightstep
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"EmployeeDbConnectionString": "Server=localhost;Database=EMSDb;User Id=sa;Password=Str0ngPa$$w0rd;"
},
"LsToken": "<Lightstep token>"
}

Finding Common Database Issues

Chatty/Sequential interaction with database

Request to the pay endpoint
List of operations
Span captured from the first database operation
Span captured from the second database operation

Unoptimized queries

app.MapPost("/ems/billing/pay-raise/", async (SqlConnection db) =>
{
using var activity = activitySource.StartActivity("Non optimized query", ActivityKind.Server);
var recordsAffected = await db.ExecuteAsync("UPDATE Payroll SET PayRateInUSD = 300 WHERE PayRateInUSD < 300");
return Results.Ok(recordsAffected);
})
.WithName("Pay-Raise")
.Produces(StatusCodes.Status200OK);
Span presenting the query duration

Database Locks

app.MapPost("/ems/payroll/remove/{empId}", async (int empId, SqlConnection db) =>
{
using var activity = activitySource.StartActivity("Db lock", ActivityKind.Server);
activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);
Payroll payrollRecord = new();
async Task DeleteRecord()
{
db.Open();
await using var tr = await db.BeginTransactionAsync();
await db.ExecuteAsync("DELETE FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId }, tr);
Thread.Sleep(5000);
await tr.CommitAsync();
}
async Task GetRecord()
{
await using var db1 =
new SqlConnection(builder.Configuration.GetConnectionString("EmployeeDbConnectionString"));
Thread.Sleep(100);
db1.Open();
payrollRecord =
await db1.QuerySingleOrDefaultAsync<Payroll>(
"SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId });
await db1.CloseAsync();
}
await Task.WhenAll(DeleteRecord(), GetRecord()); return Results.Ok(payrollRecord);
})
.WithName("RemoveEmployeeFromPayroll")
.Produces(StatusCodes.Status200OK);
Operations causing database lock

Business transactions spanning multiple services

app.MapPost("/ems/add-employee/{empId}", async (int empId, SqlConnection db) =>
{
using var activity =
activitySource.StartActivity("Multiple ops in a business transaction", ActivityKind.Server);
activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);
//op 1
await db.ExecuteAsync("INSERT INTO Payroll Values(@EmployeeId, @PayRateInUSD)",
new Payroll { EmployeeId = empId, PayRateInUSD = 100 });
// Simulate service call by creating another span
using var innerActivity = activitySource.StartActivity("Second operation of business transaction", ActivityKind.Server);
{
// Mock network call delay
Thread.Sleep(1000);
//op 2
await db.ExecuteAsync(
"INSERT INTO Timekeeping Values(@EmployeeId, @ProjectId, @WeekClosingDate, @HoursWorked)",
new Timekeeping
{ EmployeeId = empId, HoursWorked = 0, ProjectId = 1, WeekClosingDate = DateTime.Today });
}
return Results.Ok();
})
.WithName("AddEmployee")
.Produces(StatusCodes.Status201Created);
Business operations that requires communication between two services

Database Exceptions

Exceptions highlighted in explorer window
Exceptions details captured as event

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store