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

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

namespace SQLInsight.Rules.Tests;

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

    [Fact]
    public void Default_max_server_memory_is_high_with_recommendation()
    {
        var result = Run(new MaxServerMemoryDefaultRule(), With(Config(("max server memory (MB)", 2147483647)), Server(memoryMb: 65536)));

        var finding = Assert.Single(result.Findings);
        Assert.Equal(Severity.High, finding.Severity);
        Assert.Contains("58.983", finding.Recommendation, StringComparison.Ordinal); // 65536 - 6553 (10 %)
        Assert.Contains("58983", finding.FixScript, StringComparison.Ordinal);
    }

    [Fact]
    public void Default_max_server_memory_without_server_data_still_reported()
    {
        var result = Run(new MaxServerMemoryDefaultRule(), With(Config(("max server memory (MB)", 2147483647))));

        Assert.Equal(RuleOutcome.Findings, result.Outcome);
        Assert.Null(Assert.Single(result.Findings).FixScript);
    }

    [Theory]
    [InlineData(16384, 4096, 12288)]  // small server: keep 4 GB
    [InlineData(262144, 26214, 235930)] // large server: keep 10 %
    public void Memory_guidance_keeps_the_larger_of_4gb_and_10_percent(long physical, long reserve, long recommended)
    {
        Assert.Equal(reserve, MemoryGuidance.Reserve(physical));
        Assert.Equal(recommended, MemoryGuidance.Recommended(physical));
    }

    [Theory]
    [InlineData(64000, RuleOutcome.Findings)]
    [InlineData(58000, RuleOutcome.Passed)]
    [InlineData(2147483647, RuleOutcome.Passed)] // reported by CFG-001 instead
    public void Max_server_memory_too_high(long value, RuleOutcome expected)
    {
        Assert.Equal(expected, Run(new MaxServerMemoryTooHighRule(), With(Config(("max server memory (MB)", value)), Server(memoryMb: 65536))).Outcome);
    }

    [Theory]
    [InlineData(5, 0, 8, RuleOutcome.Findings)]
    [InlineData(50, 0, 8, RuleOutcome.Passed)]
    [InlineData(5, 1, 8, RuleOutcome.Passed)] // MAXDOP 1: no parallelism at all
    [InlineData(5, 0, 1, RuleOutcome.Passed)] // single CPU
    public void Cost_threshold(long ctfp, long maxdop, int cpus, RuleOutcome expected)
    {
        var context = With(Config(("cost threshold for parallelism", ctfp), ("max degree of parallelism", maxdop)), Server(cpus));
        Assert.Equal(expected, Run(new CostThresholdRule(), context).Outcome);
    }

    [Theory]
    [InlineData(0, 8, 1, RuleOutcome.Passed, null)]    // one node, ≤ 8: 0 is within guidance
    [InlineData(0, 16, 1, RuleOutcome.Findings, 8)]    // one node, > 8: 8
    [InlineData(12, 16, 1, RuleOutcome.Findings, 8)]
    [InlineData(0, 16, 2, RuleOutcome.Findings, 8)]    // several nodes, ≤ 16 per node: per node
    [InlineData(0, 12, 2, RuleOutcome.Findings, 6)]
    [InlineData(16, 32, 2, RuleOutcome.Passed, null)]  // 16 per node: up to 16 is fine
    [InlineData(24, 64, 2, RuleOutcome.Findings, 16)]  // > 16 per node: half, at most 16
    [InlineData(16, 64, 2, RuleOutcome.Passed, null)]
    public void MaxDop_follows_numa_guidance(long maxdop, int cpus, int numa, RuleOutcome expected, int? recommended)
    {
        var result = Run(new MaxDopRule(), With(Config(("max degree of parallelism", maxdop)), Server(cpus, numa: numa)));

        Assert.Equal(expected, result.Outcome);
        if (recommended is { } r)
        {
            Assert.Contains($"'max degree of parallelism', {r};", Assert.Single(result.Findings).FixScript, StringComparison.Ordinal);
        }
    }

    [Fact]
    public void Ad_hoc_bloat_needs_share_and_size()
    {
        var bloated = new PlanCacheSnapshot { CapturedAt = Now, TotalPlans = 10000, TotalMb = 2000, SingleUseAdhocPlans = 8000, SingleUseAdhocMb = 1200 };
        var small = bloated with { TotalMb = 200, SingleUseAdhocMb = 80 };
        var off = Config(("optimize for ad hoc workloads", 0));

        Assert.Equal(RuleOutcome.Findings, Run(new OptimizeForAdHocRule(), With(off, bloated)).Outcome);
        Assert.Equal(RuleOutcome.Passed, Run(new OptimizeForAdHocRule(), With(off, small)).Outcome);
        Assert.Equal(RuleOutcome.Passed, Run(new OptimizeForAdHocRule(), With(Config(("optimize for ad hoc workloads", 1)), bloated)).Outcome);
    }

    [Fact]
    public void Missing_option_on_old_version_is_not_measurable()
    {
        var result = Run(new OptimizeForAdHocRule(), WithVersion("9.00.5000.00", Config(), new PlanCacheSnapshot { CapturedAt = Now, TotalPlans = 1, TotalMb = 1, SingleUseAdhocPlans = 0, SingleUseAdhocMb = 0 }));

        Assert.Equal(RuleOutcome.NotMeasurable, result.Outcome);
        Assert.Contains("SQL Server 2005", result.Reason, StringComparison.Ordinal);
    }

    [Theory]
    [InlineData("SQLI-CFG-006", "priority boost", 1, Severity.High)]
    [InlineData("SQLI-CFG-007", "lightweight pooling", 1, Severity.High)]
    [InlineData("SQLI-CFG-008", "xp_cmdshell", 1, Severity.Medium)]
    [InlineData("SQLI-CFG-009", "backup compression default", 0, Severity.Low)]
    [InlineData("SQLI-CFG-010", "remote admin connections", 0, Severity.Low)]
    public void Simple_option_rules(string id, string option, long badValue, Severity severity)
    {
        var rule = SimpleConfigurationRules.Create().Single(r => r.Id == id);

        var bad = Run(rule, With(Config((option, badValue))));
        var good = Run(rule, With(Config((option, 1 - badValue))));

        Assert.Equal(severity, Assert.Single(bad.Findings).Severity);
        Assert.Equal(RuleOutcome.Passed, good.Outcome);
    }

    [Fact]
    public void Backup_compression_is_not_reported_on_express()
    {
        var rule = SimpleConfigurationRules.Create().Single(r => r.Id == "SQLI-CFG-009");
        var context = Context("16.0.4135.4", edition: Core.Instances.EngineEdition.Express);
        context.Data.Set(Config(("backup compression default", 0)));

        Assert.Equal(RuleOutcome.Passed, Run(rule, context).Outcome);
    }

    [Fact]
    public void Pending_configuration_ignores_min_server_memory()
    {
        var config = new ConfigurationSnapshot
        {
            CapturedAt = Now,
            Options =
            [
                new ConfigurationOption("min server memory (MB)", 0, 16),
                new ConfigurationOption("max degree of parallelism", 8, 0),
            ],
        };

        var finding = Assert.Single(Run(new PendingConfigurationRule(), With(config)).Findings);

        var evidence = Assert.Single(finding.Evidence);
        Assert.Equal("max degree of parallelism", evidence.Name);
    }

    [Theory]
    [InlineData("9.00.5000.00", new int[0], RuleOutcome.Findings)]
    [InlineData("9.00.5000.00", new[] { 1222 }, RuleOutcome.Passed)]
    [InlineData("8.00.2039", new[] { 1204 }, RuleOutcome.Passed)]
    [InlineData("16.0.4135.4", new int[0], RuleOutcome.Passed)] // system_health covers it
    public void Deadlock_trace_flag_only_matters_without_system_health(string version, int[] flags, RuleOutcome expected)
    {
        var context = WithVersion(version, new TraceFlagSnapshot(Now, flags));

        Assert.Equal(expected, Run(new DeadlockTraceFlagRule(), context).Outcome);
    }

    [Fact]
    public void Configuration_changes_are_information_for_a_day()
    {
        var snapshot = new InstanceConfigurationSnapshot
        {
            CapturedAt = Now,
            RecentChanges =
            [
                new ConfigurationChange(ConfigurationFingerprint.SpConfigure, "max degree of parallelism", "0", "1", Now.AddHours(-2)),
                new ConfigurationChange(ConfigurationFingerprint.TraceFlag, "1222", null, "til", Now.AddMinutes(-15)),
                new ConfigurationChange(ConfigurationFingerprint.ServiceAccount, "SQL Server (MSSQLSERVER)", @"NT Service\MSSQLSERVER", @"KUNDE\svc-sql", Now),
                new ConfigurationChange(ConfigurationFingerprint.Instance, "Version", "16.0.4135.4 RTM", "16.0.4175.1 RTM", Now), // an update, not a finding
            ],
        };

        var result = Run(new ConfigurationChangedRule(), With(snapshot));

        Assert.Equal(3, result.Findings.Count);
        Assert.All(result.Findings, f => Assert.Equal(Severity.Info, f.Severity));
        Assert.Contains(result.Findings, f => f.Title == "max degree of parallelism er ændret fra 0 til 1");
        Assert.Contains(result.Findings, f => f.Title == "Trace flag 1222 er slået til");
        var account = result.Findings.Single(f => f.Title.StartsWith("Tjenestekontoen", StringComparison.Ordinal));
        Assert.DoesNotContain("svc-sql", account.Title, StringComparison.Ordinal);
        Assert.True(account.Evidence.Single(e => e.Name == "Til").Sensitive);
    }
}