sqlinsight-pro/tests/SQLInsight.Agent.Tests/MetricAssessmentTests.cs

using SQLInsight.Core.Data;
using SQLInsight.Reports;
using SQLInsight.Repository;

namespace SQLInsight.Agent.Tests;

public class MetricAssessmentTests
{
    private static readonly DateTime Start = new(2026, 10, 1, 8, 0, 0, DateTimeKind.Utc);

    private static List<MetricPoint> Series(params double[] values) =>
        values.Select((v, i) => new MetricPoint(Start.AddMinutes(5 * i), v)).ToList();

    [Fact]
    public void Ple_threshold_scales_with_the_buffer_pool()
    {
        var ple = Series(1000, 1100, 1050, 1000);

        Assert.Equal(AssessmentLevel.Ok, MetricAssessment.Assess(Metrics.PageLifeExpectancy, ple, new MetricContext(BufferPoolGb: 8, MaxServerMemorySet: true)).Level);
        var big = MetricAssessment.Assess(Metrics.PageLifeExpectancy, ple, new MetricContext(BufferPoolGb: 64, MaxServerMemorySet: true)); // 4800 s
        Assert.Equal(AssessmentLevel.Act, big.Level);
        Assert.Contains("4.800", big.Summary, StringComparison.Ordinal);
    }

    [Fact]
    public void Ple_dips_of_more_than_half_are_noted_and_unset_max_server_memory_is_mentioned()
    {
        var dip = MetricAssessment.Assess(Metrics.PageLifeExpectancy, Series(3000, 3100, 900, 3000, 3050), new MetricContext(BufferPoolGb: 4, MaxServerMemorySet: true));
        var unset = MetricAssessment.Assess(Metrics.PageLifeExpectancy, Series(3000, 3100), new MetricContext(BufferPoolGb: 4, MaxServerMemorySet: false));

        Assert.Equal(AssessmentLevel.Note, dip.Level);
        Assert.Contains("50 %", dip.Summary, StringComparison.Ordinal);
        Assert.Equal(AssessmentLevel.Note, unset.Level);
        Assert.Contains("max server memory", unset.Summary, StringComparison.Ordinal);
    }

    [Theory]
    [InlineData(1, AssessmentLevel.Ok)]
    [InlineData(15, AssessmentLevel.Note)]
    [InlineData(30, AssessmentLevel.Act)]
    public void Compilations_are_judged_against_batches(double compilations, AssessmentLevel expected)
    {
        var context = new MetricContext(BatchRequests: Series(100, 100, 100));

        Assert.Equal(expected, MetricAssessment.Assess(Metrics.CompilationsPerSecond, Series(compilations, compilations, compilations), context).Level);
    }

    [Fact]
    public void Counts_above_zero_must_be_sustained_to_act()
    {
        Assert.Equal(AssessmentLevel.Ok, MetricAssessment.Assess(Metrics.MemoryGrantsPending, Series(0, 0, 0)).Level);
        Assert.Equal(AssessmentLevel.Note, MetricAssessment.Assess(Metrics.MemoryGrantsPending, Series(0, 2, 0, 0)).Level);
        var blocked = MetricAssessment.Assess(Metrics.ProcessesBlocked, Series(0, 3, 4, 5, 0));
        Assert.Equal(AssessmentLevel.Act, blocked.Level);
        Assert.Equal("analyse#blocking", blocked.Link);
    }

    [Fact]
    public void High_cpu_from_other_processes_points_at_them()
    {
        var sql = Series(30, 35, 30, 32);
        var other = Series(55, 55, 60, 58);

        var cpu = MetricAssessment.Assess(Metrics.CpuSqlServer, sql, new MetricContext(CpuOther: other));
        var own = MetricAssessment.Assess(Metrics.CpuSqlServer, Series(90, 92, 95, 91), new MetricContext(CpuOther: Series(2, 2, 2, 2)));

        Assert.Equal(AssessmentLevel.Act, cpu.Level);
        Assert.Contains("andre processer", cpu.Summary, StringComparison.Ordinal);
        Assert.Equal(AssessmentLevel.Act, own.Level);
        Assert.Contains("SQL Server bruger", own.Summary, StringComparison.Ordinal);
        Assert.Equal(AssessmentLevel.Ok, MetricAssessment.Assess(Metrics.CpuSqlServer, Series(20, 25), new MetricContext(CpuOther: Series(1, 1))).Level);
    }

    [Theory]
    [InlineData(5, 3, 1, AssessmentLevel.Ok)]
    [InlineData(15, 3, 1, AssessmentLevel.Note)]
    [InlineData(30, 3, 1, AssessmentLevel.Act)]
    [InlineData(1, 8, 0, AssessmentLevel.Note)]   // log drive: 8 ms per write
    [InlineData(1, 20, 0, AssessmentLevel.Act)]
    public void Disk_latency_thresholds(double readMs, double writeMs, int dataFiles, AssessmentLevel expected)
    {
        var disk = new DiskActivity("L:", dataFiles, 1, 1000, 1000, 0, 0, (long)(readMs * 1000), (long)(writeMs * 1000), 300);

        Assert.Equal(expected, MetricAssessment.AssessDisk(disk).Level);
    }

    [Fact]
    public void Every_metric_has_an_explanation_and_no_data_is_unknown()
    {
        foreach (var (metric, _, _, _, _, _) in TimeWindowAnalysis.MetricDefinitions)
        {
            Assert.NotEqual("Ingen forklaring.", MetricAssessment.Explanation(metric));
            Assert.Equal(AssessmentLevel.Unknown, MetricAssessment.Assess(metric, []).Level);
        }
    }

    [Fact]
    public void Context_uses_max_server_memory_capped_by_physical_memory()
    {
        var config = new ConfigurationSnapshot { CapturedAt = DateTimeOffset.UtcNow, Options = [new ConfigurationOption("max server memory (MB)", 28672, 28672)] };
        var server = new ServerSnapshot { CapturedAt = DateTimeOffset.UtcNow, LogicalCpuCount = 8, PhysicalMemoryMb = 32768, SqlServerStartTime = Start };
        var unset = config with { Options = [new ConfigurationOption("max server memory (MB)", 2147483647, 2147483647)] };

        Assert.Equal(28, MetricContext.From(config, server).BufferPoolGb);
        Assert.Equal((32.0, false), (MetricContext.From(unset, server).BufferPoolGb!.Value, MetricContext.From(unset, server).MaxServerMemorySet!.Value));
    }
}