sqlinsight-pro/tests/SQLInsight.Rules.Tests/OperationRuleTests.cs

using SQLInsight.Core.Collection;
using SQLInsight.Core.Data;
using SQLInsight.Core.Findings;
using SQLInsight.Rules.Engine;
using SQLInsight.Rules.Performance;
using static SQLInsight.Rules.Tests.TestInstances;

namespace SQLInsight.Rules.Tests;

public class OperationRuleTests
{
    private static RuleResult Run(IRule rule, RuleContext context) => Assert.Single(new RuleEngine([rule]).Evaluate(context));

    private static LongOperation Backup(DateTimeOffset started, DateTimeOffset seen, string db = "AX") =>
        new(61, "BACKUP DATABASE", db, @"KUNDE\svc-backup", started, 40, seen.AddMinutes(30), seen);

    /// <summary>Earlier runs of 20, 22, 25, 21 and 60 minutes (median 22).</summary>
    private static LongOperationSnapshot WithHistory()
    {
        LongOperationSnapshot? snapshot = null;
        var day = Now.AddDays(-10);
        foreach (var minutes in new[] { 20, 22, 25, 21, 60 })
        {
            snapshot = LongOperationHistory.Advance(snapshot, [Backup(day, day.AddMinutes(minutes))], day.AddMinutes(minutes));
            snapshot = LongOperationHistory.Advance(snapshot, [], day.AddMinutes(minutes + 1));
            day = day.AddDays(1);
        }

        return snapshot!;
    }

    [Fact]
    public void History_keeps_finished_runs_per_command_and_database()
    {
        var snapshot = WithHistory();

        Assert.Equal(5, snapshot.History["BACKUP DATABASE|AX"].Count);
        Assert.Equal(TimeSpan.FromMinutes(22), LongOperationHistory.Median(snapshot, Backup(Now, Now)));
        Assert.Null(LongOperationHistory.Median(snapshot, Backup(Now, Now, "Andet")));
    }

    [Theory]
    [InlineData(40, RuleOutcome.Passed)]   // under 2 × 22
    [InlineData(50, RuleOutcome.Findings)]
    public void Backups_running_more_than_twice_the_median_are_found(int minutes, RuleOutcome expected)
    {
        var running = WithHistory() with { CapturedAt = Now, Running = [Backup(Now.AddMinutes(-minutes), Now)] };

        var result = Run(new SlowBackupRule(), With(running));

        Assert.Equal(expected, result.Outcome);
        if (expected == RuleOutcome.Findings)
        {
            var finding = Assert.Single(result.Findings);
            Assert.Equal(Severity.Medium, finding.Severity);
            Assert.True(finding.Evidence.Single(e => e.Name == "Login").Sensitive);
        }
    }

    [Fact]
    public void Other_operations_and_unknown_history_are_not_judged()
    {
        var dbcc = new LongOperation(62, "DBCC", "AX", null, Now.AddHours(-5), 10, null, Now);
        var snapshot = new LongOperationSnapshot { CapturedAt = Now, Running = [dbcc, Backup(Now.AddHours(-5), Now, "Ny")] };

        Assert.Equal(RuleOutcome.Passed, Run(new SlowBackupRule(), With(snapshot)).Outcome);
    }

    private static CumulativeSample<QueryStatsSnapshot, QueryStatsDelta> Queries(double threshold, params QueryStat[] queries) => new(
        new QueryStatsSnapshot { CapturedAt = Now, ServerTime = Now.UtcDateTime, SqlServerStartTime = Now.UtcDateTime.AddDays(-3), Queries = queries, ActivityThresholdPercent = threshold },
        null);

    private static QueryStat Statement(string key, double hours, double cpuSeconds) => new()
    {
        Key = key, Executions = 1000, WorkerTimeUs = (long)(cpuSeconds * 1_000_000), ElapsedTimeUs = 0, LogicalReads = 0, PhysicalReads = 0, LogicalWrites = 0,
        CreatedAt = Now.UtcDateTime.AddHours(-hours), LastExecutionAt = Now.UtcDateTime.AddMinutes(-1), Text = "SELECT * FROM INVENTSUM",
    };

    [Fact]
    public void A_statement_using_more_than_half_a_core_for_hours_is_found()
    {
        var busy = Statement("0xBUSY", 4, 4 * 3600 * 0.8);   // 80 %
        var calm = Statement("0xCALM", 4, 4 * 3600 * 0.2);   // 20 %
        var young = Statement("0xNEW", 0.5, 1800);           // 100 %, but only half an hour

        var result = Run(new ConstantActivityRule(), With(Queries(50, busy, calm, young)));

        var finding = Assert.Single(result.Findings);
        Assert.Equal("query: 0xBUSY", finding.Subject);
        Assert.DoesNotContain("INVENTSUM", finding.Title, StringComparison.Ordinal);
        Assert.True(finding.Evidence.Single(e => e.Name == "Sætning").Sensitive);
        Assert.Equal(RuleOutcome.Passed, Run(new ConstantActivityRule(), With(Queries(90, busy))).Outcome); // configurable
        Assert.InRange(busy.ActivityPercent!.Value, 79, 82);
    }
}