Mỗi chỉ mục làm đơn mới ghi thêm: chỉ mục thiếu, chỉ mục thừa và gỡ an toàn

SELECT
    i.name AS index_name, i.type_desc, i.is_unique, i.is_disabled,
    STUFF((SELECT N', ' + c.name FROM sys.index_columns AS ic
           JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0
           ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, N'') AS key_columns,
    STUFF((SELECT N', ' + c.name FROM sys.index_columns AS ic
           JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
           FOR XML PATH('')), 1, 2, N'') AS included_columns,
    (SELECT SUM(ps.used_page_count) FROM sys.dm_db_partition_stats AS ps
      WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id) AS used_pages,
    ISNULL(us.user_seeks, 0) AS user_seeks, ISNULL(us.user_scans, 0) AS user_scans,
    ISNULL(us.user_lookups, 0) AS user_lookups, ISNULL(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.DonHang')
ORDER BY key_columns, i.name;

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

SELECT TOP (20)
    mid.statement AS table_name,
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.user_seeks, migs.user_scans, migs.avg_total_user_cost,
    migs.avg_user_impact, migs.last_user_seek
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact
         * (migs.user_seeks + migs.user_scans) DESC;

SELECT DISTINCT qsq.query_id, LEFT(qt.query_sql_text, 200) AS query_text
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_query AS qsq ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = qsq.query_text_id
WHERE qsp.query_plan LIKE N'%IX_DonHang_GoiY%';

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name, OBJECT_NAME(m.object_id) AS module_name
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%IX_DonHang_GoiY%';

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.3
#:property PublishAot=false
// Tạo nguyên văn gợi ý chỉ mục thiếu, đo mỗi chỉ mục tốn bao nhiêu khi ghi 5.000 đơn mới,
// xem bộ đếm sử dụng, gỡ chỉ mục thừa bằng DISABLE, rồi in SQL của hai migration gỡ chỉ mục.
// Chạy: dotnet run ChiPhiGhi.cs   (cần database Kumeo_chimuc đã dựng ở bài đầu chương; khoảng 4 phút)
using System.Data.Common;
using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

const string cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_chimuc;Integrated Security=true;TrustServerCertificate=true";
await using var db = new BanHangDb(cs);
db.Database.SetCommandTimeout(600);
var conn = (SqlConnection)db.Database.GetDbConnection();
long reads = 0;
conn.InfoMessage += (_, e) =>
{
    foreach (Match m in Regex.Matches(e.Message, @"logical reads (\d+)"))
        reads += long.Parse(m.Groups[1].Value);
};
// Giữ một kết nối mở suốt chương trình: LocalDB bật AUTO_CLOSE, database đóng là mất mọi bộ đếm
await db.Database.OpenConnectionAsync();

// 0. Bộ chỉ mục của các bài trước: PK, IX_DonHang_KhachHang gốc, IX_DonHang_DangMo
await Sql("""
    IF INDEXPROPERTY(OBJECT_ID(N'dbo.DonHang'), N'IX_DonHang_GoiY', 'IndexID') IS NOT NULL
        DROP INDEX IX_DonHang_GoiY ON dbo.DonHang;
    CREATE INDEX IX_DonHang_KhachHang ON dbo.DonHang (KhachHangId, NgayTao)
        WITH (DROP_EXISTING = ON) ON ps_DonHang_Ngay (NgayTao);
    IF INDEXPROPERTY(OBJECT_ID(N'dbo.DonHang'), N'IX_DonHang_DangMo', 'IndexID') IS NULL
        CREATE INDEX IX_DonHang_DangMo ON dbo.DonHang (KhachHangId, NgayTao)
            INCLUDE (TongTien) WHERE TrangThai = 1;
    """);

// 1. Màn hình lịch sử đơn của khách 42 chạy 5 lần, rồi đọc gợi ý chỉ mục thiếu
for (int i = 0; i < 5; i++) await LichSu();
var g = await db.Database.SqlQueryRaw<GoiY>("""
    SELECT mid.equality_columns AS Bang, mid.inequality_columns AS Khoang, mid.included_columns AS KemTheo,
           migs.user_seeks AS LanSeek, migs.avg_user_impact AS TacDong
    FROM sys.dm_db_missing_index_details AS mid
    JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
    JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
    WHERE mid.database_id = DB_ID() AND mid.object_id = OBJECT_ID(N'dbo.DonHang')
    """).SingleAsync();
Console.WriteLine($"Gợi ý: equality {g.Bang}, inequality {g.Khoang ?? "NULL"}, included {g.KemTheo ?? "NULL"}, " +
                  $"user_seeks {g.LanSeek}, avg_user_impact {g.TacDong}");

// 2. Tạo nguyên văn gợi ý đó, rồi so với chỉ mục đang có
var include = g.KemTheo is null ? "" : $"INCLUDE ({g.KemTheo})";
await Sql($"CREATE INDEX IX_DonHang_GoiY ON dbo.DonHang ({g.Bang}) {include} ON ps_DonHang_Ngay (NgayTao)");
foreach (var c in await db.Database.SqlQueryRaw<CauTruc>("""
    SELECT i.name AS Ten, SUM(ps.used_page_count) AS Page,
           (SELECT MAX(p.avg_record_size_in_bytes)
              FROM sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'DETAILED') AS p
             WHERE p.index_level = 0) AS DongLa
    FROM sys.indexes AS i
    JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
    WHERE i.object_id = OBJECT_ID(N'dbo.DonHang') AND i.name IN (N'IX_DonHang_KhachHang', N'IX_DonHang_GoiY')
    GROUP BY i.object_id, i.index_id, i.name
    """).ToListAsync())
    Console.WriteLine($"   {c.Ten,-22} {c.Page,6:N0} page, dòng lá {c.DongLa} byte");
Console.WriteLine($"   Lịch sử đơn khách 42 sau khi tạo: {await DemDoc(LichSu):N0} logical reads");

// 3. Ghi 5.000 đơn mới với từng bộ chỉ mục, năm vòng xen kẽ: log sinh ra, CPU của lệnh INSERT, page lá cấp mới.
//    Bộ 3 chính là trạng thái sau khi disable IX_DonHang_GoiY: chỉ mục bị disable không còn được ghi.
string[] phu = ["IX_DonHang_KhachHang", "IX_DonHang_DangMo", "IX_DonHang_GoiY"];
var boChiMuc = new (string Ten, string[] Bat, List<LanGhi> Lan)[]
{
    ("1. Chỉ PK_DonHang", [], []),
    ("2. + IX_DonHang_KhachHang", phu[..1], []),
    ("3. + IX_DonHang_DangMo", phu[..2], []),
    ("4. + IX_DonHang_GoiY", phu, []),
};
for (int vong = 0; vong < 5; vong++)
    foreach (var bo in boChiMuc) bo.Lan.Add(await DoGhi(bo.Bat));
Console.WriteLine();
Console.WriteLine($"{"Bộ chỉ mục",-28} {"log MB",7} {"CPU ms",7} {"thấp–cao",-10} page lá cấp mới");
foreach (var (ten, _, lan) in boChiMuc)
{
    var cpu = lan.Select(x => x.CpuMs).Order().ToList();
    var log = lan.Select(x => x.LogMb).Order().ToList();
    Console.WriteLine($"{ten,-28} {log[2],7:N2} {cpu[2],7} {$"{cpu[0]}–{cpu[4]}",-10} {lan[^1].TachPage}");
}

// 4. Một lượt của ba màn hình đọc: số page đọc, và chỉ mục nào được dùng
Console.WriteLine();
await LuotDoc("Bốn chỉ mục");

// 5. Gỡ bản sao bằng DISABLE, rồi chạy lại ba màn hình
await Sql("ALTER INDEX IX_DonHang_GoiY ON dbo.DonHang DISABLE");
await LuotDoc("Đã disable IX_DonHang_GoiY");

// 6. Không câu nào chậm đi: xóa hẳn, rồi dựng lại các chỉ mục còn lại cho các bài khác
await Sql("DROP INDEX IX_DonHang_GoiY ON dbo.DonHang");
await Sql("ALTER INDEX ALL ON dbo.DonHang REBUILD");

// 7. Trong dự án: gỡ bằng hai migration, DISABLE trước, DROP ở lần phát hành sau
Console.WriteLine();
var sinhSql = db.GetService<IMigrationsSqlGenerator>();
foreach (var m in new Migration[] { new TatChiMucGoiY(), new XoaChiMucGoiY() })
    foreach (var lenh in sinhSql.Generate(m.UpOperations))
        Console.WriteLine($"{m.GetType().Name}: {lenh.CommandText.Trim()}");

async Task<LanGhi> DoGhi(string[] bat)
{
    // Mỗi lần đo bắt đầu từ cùng trạng thái: PK và chỉ mục được bật vừa rebuild, chỉ mục còn lại disable
    await Sql("ALTER INDEX PK_DonHang ON dbo.DonHang REBUILD");
    foreach (var ix in phu)
        await Sql($"ALTER INDEX {ix} ON dbo.DonHang {(bat.Contains(ix) ? "REBUILD" : "DISABLE")}");
    var capTruoc = await CapPage();
    await Sql("ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE");  // bộ đếm CPU của INSERT về 0
    await Sql("CHECKPOINT");  // dọn log cũ, để phần log tăng thêm chỉ là của 5.000 đơn
    long maxId = await db.DonHang.MaxAsync(d => d.DonHangId);
    await using var tx = await db.Database.BeginTransactionAsync();
    var logTruoc = await LogDung();
    var dau = new DateTime(2026, 10, 2, 12, 0, 0);
    db.DonHang.AddRange(Enumerable.Range(1, 5000).Select(i => new DonHang
    {
        DonHangId = maxId + i,
        NgayTao = dau.AddSeconds(i * 6),
        KhachHangId = 1 + (int)((i * 7919L) % 48500),
        TrangThai = 1,
        TongTien = 250000m,
    }));
    await db.SaveChangesAsync();
    var logMb = (await LogDung() - logTruoc) / 1048576.0;
    var cpu = await CpuInsert();
    var capSau = await CapPage();
    await tx.RollbackAsync();  // trả bảng về như cũ cho lần đo sau
    db.ChangeTracker.Clear();
    var tachPage = string.Join(", ", capSau.Select(s => (s.Key, Moi: s.Value - capTruoc.GetValueOrDefault(s.Key)))
        .Where(s => s.Moi > 0).OrderBy(s => s.Key).Select(s => $"{s.Key.Replace("IX_DonHang_", "")} {s.Moi}"));
    return new LanGhi(logMb, cpu, tachPage);
}

async Task LuotDoc(string nhan)
{
    var truoc = await DocDung();
    var lichSu = await DemDoc(LichSu);
    var chonDon = await DemDoc(() => db.DonHang.Where(d => d.KhachHangId == 77)
        .Select(d => new { d.DonHangId, d.NgayTao }).ToListAsync());
    var dangMo = await DemDoc(() => db.DonHang.Where(d => d.KhachHangId == 42 && d.TrangThai == 1)
        .Select(d => new { d.NgayTao, d.TongTien }).ToListAsync());
    var sau = await DocDung();
    Console.WriteLine($"-- {nhan}: lịch sử đơn {lichSu:N0}, chọn đơn {chonDon:N0}, đơn đang mở {dangMo:N0} logical reads");
    Console.WriteLine("   Lượt đọc của từng chỉ mục: " +
        string.Join(", ", sau.OrderBy(s => s.Key).Select(s => $"{s.Key} {s.Value - truoc.GetValueOrDefault(s.Key)}")));
}

Task LichSu() => db.DonHang.Where(d => d.KhachHangId == 42)
    .Select(d => new { d.DonHangId, d.NgayTao, d.TrangThai, d.TongTien }).ToListAsync();

async Task<long> DemDoc(Func<Task> q)
{
    await Sql("SET STATISTICS IO ON");
    reads = 0;
    await q();
    await Sql("SET STATISTICS IO OFF");
    return reads;
}

async Task<long> LogDung() => await db.Database.SqlQueryRaw<long>(
    "SELECT used_log_space_in_bytes AS Value FROM sys.dm_db_log_space_usage").SingleAsync();

// Tổng CPU (ms) của các lệnh INSERT vào DonHang trong plan cache
async Task<long> CpuInsert() => await db.Database.SqlQueryRaw<long>("""
    SELECT ISNULL(SUM(qs.total_worker_time), 0) / 1000 AS Value
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    WHERE st.text LIKE N'%INSERT INTO \[DonHang\]%' ESCAPE N'\'
    """).SingleAsync();

async Task<Dictionary<string, long>> CapPage() =>
    (await db.Database.SqlQueryRaw<SoDem>("""
        SELECT i.name AS Ten, SUM(os.leaf_allocation_count) AS So
        FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'dbo.DonHang'), NULL, NULL) AS os
        JOIN sys.indexes AS i ON i.object_id = os.object_id AND i.index_id = os.index_id
        GROUP BY i.name
        """).ToListAsync()).ToDictionary(s => s.Ten, s => s.So);

async Task<Dictionary<string, long>> DocDung() =>
    (await db.Database.SqlQueryRaw<SoDem>("""
        SELECT i.name AS Ten, ISNULL(us.user_seeks + us.user_scans + us.user_lookups, 0) AS So
        FROM sys.indexes AS i
        LEFT JOIN sys.dm_db_index_usage_stats AS us
          ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
        WHERE i.object_id = OBJECT_ID(N'dbo.DonHang')
        """).ToListAsync()).ToDictionary(s => s.Ten, s => s.So);

Task Sql(string sql) => db.Database.ExecuteSqlRawAsync(sql);

record GoiY(string Bang, string? Khoang, string? KemTheo, long LanSeek, double TacDong);
record CauTruc(string Ten, long Page, double DongLa);
record SoDem(string Ten, long So);
record LanGhi(double LogMb, long CpuMs, string TachPage);

// Bản phát hành 1: chỉ disable, Down bật lại bằng REBUILD
class TatChiMucGoiY : Migration
{
    protected override void Up(MigrationBuilder b) =>
        b.Sql("ALTER INDEX IX_DonHang_GoiY ON dbo.DonHang DISABLE;");
    protected override void Down(MigrationBuilder b) =>
        b.Sql("ALTER INDEX IX_DonHang_GoiY ON dbo.DonHang REBUILD;");
}

// Bản phát hành 2, sau một chu kỳ nghiệp vụ không có câu nào chậm đi
class XoaChiMucGoiY : Migration
{
    protected override void Up(MigrationBuilder b) =>
        b.DropIndex(name: "IX_DonHang_GoiY", table: "DonHang");
    protected override void Down(MigrationBuilder b) =>
        b.CreateIndex(name: "IX_DonHang_GoiY", table: "DonHang", column: "KhachHangId");
}

class DonHang
{
    public long DonHangId { get; set; }
    public DateTime NgayTao { get; set; }
    public int KhachHangId { get; set; }
    public byte TrangThai { get; set; }
    public decimal TongTien { get; set; }
}

class BanHangDb(string cs) : DbContext
{
    public DbSet<DonHang> DonHang => Set<DonHang>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) =>
        o.UseSqlServer(cs).AddInterceptors(new DocHetThongBao());
    protected override void OnModelCreating(ModelBuilder b)
    {
        var e = b.Entity<DonHang>();
        e.ToTable("DonHang");
        e.HasKey(d => new { d.NgayTao, d.DonHangId });
        e.Property(d => d.NgayTao).HasColumnType("datetime2(0)");
        e.Property(d => d.TongTien).HasColumnType("decimal(18, 2)");
    }
}

// SqlClient chỉ phát InfoMessage của STATISTICS IO khi reader được đọc tới hết.
class DocHetThongBao : DbCommandInterceptor
{
    public override InterceptionResult DataReaderClosing(DbCommand c, DataReaderClosingEventData e, InterceptionResult r)
    {
        while (e.DataReader.NextResult()) { }
        return r;
    }
    public override async ValueTask<InterceptionResult> DataReaderClosingAsync(DbCommand c, DataReaderClosingEventData e, InterceptionResult r)
    {
        while (await e.DataReader.NextResultAsync()) { }
        return r;
    }
}