DeveloperToolkit.SqlPerformance 1.0.0

DeveloperToolkit.SqlPerformance

A production-ready .NET 8 NuGet package that automatically monitors SQL performance in ASP.NET Core applications using Entity Framework Core.

Features

• EF Core Interceptor - Automatically captures SQL queries, parameters, execution time, and more
• Slow Query Detection - Configurable threshold with actionable suggestions
• Duplicate Query Detection - Identifies N+1 query problems
• SELECT * Detection - Warns against retrieving all columns
• Missing WHERE Detection - Catches potentially dangerous DML without WHERE clauses
• Large Result Set Detection - Suggests pagination for oversized results
• Index Suggestions - Recommends indexes for slow queries
• Parameter Logging - Logs parameters with sensitive data masking
• SQL Formatter - Pretty-prints SQL for readability
• Built-in Dashboard - Real-time HTML dashboard at /sql-performance
• Export - JSON and CSV export support

Installation

dotnet add package DeveloperToolkit.SqlPerformance

Quick Start

// Program.cs
var builder = WebApplication.CreateBuilder(args);

// Add SQL Performance Monitor (one line!)
builder.Services.AddSqlPerformanceMonitor();

// Configure your DbContext with the interceptor
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    options.UseSqlServer(connectionString);
    options.AddInterceptors(sp.GetRequiredService<SqlPerformanceInterceptor>());
});

var app = builder.Build();

// Enable middleware and dashboard
app.UseSqlPerformanceMonitor();
app.UseSqlPerformanceDashboard();

app.Run();

Configuration

builder.Services.AddSqlPerformanceMonitor(options =>
{
    options.SlowQueryThreshold = 500;         // ms (default: 500)
    options.EnableDashboard = true;            // default: true
    options.EnableDuplicateDetection = true;   // default: true
    options.EnableParameterLogging = true;     // default: true
    options.EnableSuggestions = true;          // default: true
    options.EnableFormatting = true;           // default: true
    options.EnableExport = true;              // default: true
    options.LargeResultSetThreshold = 1000;   // rows (default: 1000)
    options.MaxStoredExecutions = 1000;        // default: 1000
    options.DashboardPath = "/sql-performance"; // default

    // Ignore specific tables
    options.IgnoredTables.Add("__EFMigrationsHistory");
    options.IgnoredTables.Add("AuditLog");

    // Ignore specific query patterns
    options.IgnoredQueries.Add("INFORMATION_SCHEMA");

    // Add custom sensitive parameter names
    options.SensitiveParameters.Add("my_secret_field");
});

Dashboard

Navigate to /sql-performance to access the built-in dashboard showing:

• Total Queries
• Slow Queries
• Average Execution Time
• Longest Query
• Duplicate Queries
• Failed Queries
• Detailed execution table with suggestions

API Endpoints

How It Works

1. Interceptor captures every SQL command via EF Core's DbCommandInterceptor
2. Analyzers (Strategy Pattern) inspect each execution for issues:
   • SlowQueryAnalyzer - duration threshold
   • DuplicateQueryAnalyzer - repeated query detection
   • SelectStarAnalyzer - SELECT * detection
   • MissingWhereAnalyzer - dangerous DML detection
   • LargeResultAnalyzer - oversized result detection
   • IndexSuggestionAnalyzer - index recommendations
3. Collector aggregates results thread-safely
4. Logger outputs structured warnings via ILogger
5. Dashboard presents real-time metrics

Performance

• Thread-safe using ConcurrentQueue and Interlocked operations
• Async throughout
• Minimal memory allocations
• No reflection in hot paths
• Low overhead (<2% in typical applications)
• No breaking changes to EF Core behavior

Requirements

• .NET 8.0 or later
• Entity Framework Core 8.0 or later
• ASP.NET Core (for middleware and dashboard)

License

MIT License - see LICENSE for details.

Author

Nilesh More

Contributing

See CONTRIBUTING.md for guidelines.