Søren Eggert Lundsteen Olsen
6cc65e3c75
The index collector now sums index reads and writes between collections
across restarts of SQL Server (and of the agent, by seeding from the
stored snapshot) and keeps them per day in insight.IndexUsageDaily
(migration 0015, HourlyDays). SQLI-IDX-002 judges an index once it has
been followed for 7 days instead of needing 7 days of uptime.
GET /api/v1/instances/{name}/index-usage lists usage per index.
SQLI-STAT-001 also reports statistics that were never updated on tables
with rows; statistic rows now come from the table's heap or clustered
index, as a never-updated statistic has no row count of its own.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
66 lines
3.7 KiB
C#
66 lines
3.7 KiB
C#
using Microsoft.AspNetCore.Hosting;
|
|
using Microsoft.AspNetCore.Mvc.Testing;
|
|
using SQLInsight.Collectors.Databases;
|
|
using SQLInsight.Core.Data;
|
|
using SQLInsight.Core.Instances;
|
|
using SQLInsight.Repository;
|
|
|
|
namespace SQLInsight.IntegrationTests;
|
|
|
|
/// <summary>Index usage followed over time, and statistics that were never updated.</summary>
|
|
[Collection(SqlServerTestGroup.Name)]
|
|
[Trait("Category", "Integration")]
|
|
public class IndexUsageTests(SqlServerFixture sql)
|
|
{
|
|
[Fact]
|
|
public async Task Usage_between_collections_is_stored_per_day_and_never_updated_statistics_are_seen()
|
|
{
|
|
var table = "Usage_" + Guid.NewGuid().ToString("N")[..6];
|
|
await sql.ExecuteAsSaAsync($"""
|
|
USE [{SqlServerFixture.TestDatabase}];
|
|
CREATE TABLE dbo.{table} (Id int IDENTITY PRIMARY KEY, Code int NOT NULL, Name nvarchar(20));
|
|
CREATE INDEX IX_{table}_Code ON dbo.{table} (Code);
|
|
CREATE STATISTICS ST_{table}_Name ON dbo.{table} (Name) WITH NORECOMPUTE;
|
|
INSERT dbo.{table} (Code, Name) SELECT TOP (500) object_id % 100, N'x' FROM sys.all_objects;
|
|
""");
|
|
var database = "SQLInsightIdx_" + Guid.NewGuid().ToString("N")[..8];
|
|
await sql.ExecuteAsSaAsync($"CREATE DATABASE [{database}];");
|
|
try
|
|
{
|
|
await using var session = await AgentSession.OpenAsync(sql);
|
|
var first = await session.CollectAsync<IndexSnapshot>(new IndexCollector());
|
|
await sql.ExecuteAsSaAsync($"USE [{SqlServerFixture.TestDatabase}]; SELECT COUNT(*) FROM dbo.{table} WITH (INDEX(IX_{table}_Code)) WHERE Code = 7;");
|
|
var second = await session.CollectAsync(new IndexCollector(), first);
|
|
|
|
var index = second.Databases.Single(d => d.Name == SqlServerFixture.TestDatabase).Indexes.Single(i => i.Name == $"IX_{table}_Code");
|
|
Assert.True(index.TrackedReads >= 1);
|
|
Assert.NotNull(index.TrackedSince);
|
|
var delta = Assert.Single(second.UsageDeltas, d => d.Index == $"IX_{table}_Code");
|
|
Assert.True(delta.Reads >= 1);
|
|
var never = second.Databases.Single(d => d.Name == SqlServerFixture.TestDatabase).Statistics.Single(s => s.Name == $"ST_{table}_Name");
|
|
Assert.Null(never.LastUpdated);
|
|
Assert.Equal(500, never.Rows); // the table's rows, not the statistic's
|
|
|
|
var repository = new InsightRepository(sql.DatabaseConnectionString(database));
|
|
await repository.MigrateAsync();
|
|
var id = await repository.UpsertInstanceAsync("prod-sql01", session.Instance, "SqlClient", false, DateTimeOffset.UtcNow);
|
|
await repository.SaveCollectedAsync(id, "indexes", second);
|
|
var usage = await repository.GetIndexUsageAsync(id, DateTimeOffset.UtcNow.AddDays(-1), DateTimeOffset.UtcNow);
|
|
Assert.Contains(usage, u => u.IndexName == $"IX_{table}_Code" && u.Reads >= 1 && u.Days == 1);
|
|
|
|
await using var factory = new WebApplicationFactory<Program>().WithWebHostBuilder(b =>
|
|
{
|
|
b.UseEnvironment("Development");
|
|
b.UseSetting("SQLInsight:Repository:ConnectionString", sql.DatabaseConnectionString(database));
|
|
b.UseSetting("SQLInsight:Web:Authentication", "None");
|
|
});
|
|
var api = await factory.CreateClient().GetStringAsync("/api/v1/instances/prod-sql01/index-usage?days=7");
|
|
Assert.Contains($"IX_{table}_Code", api, StringComparison.Ordinal);
|
|
}
|
|
finally
|
|
{
|
|
await sql.ExecuteAsSaAsync($"ALTER DATABASE [{database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{database}];");
|
|
await sql.ExecuteAsSaAsync($"USE [{SqlServerFixture.TestDatabase}]; DROP TABLE dbo.{table};");
|
|
}
|
|
}
|
|
}
|