Parameter sniffing và so kế hoạch trong Query Store

EXEC sys.sp_recompile N'dbo.usp_KhachHang_TheoTrangThai';
GO
-- Bật Ctrl+M trước khi chạy
EXEC dbo.usp_KhachHang_TheoTrangThai @TrangThai = 1;   -- biên dịch cho 1
EXEC dbo.usp_KhachHang_TheoTrangThai @TrangThai = 4;   -- dùng lại kế hoạch của 1
GO
EXEC sys.sp_recompile N'dbo.usp_KhachHang_TheoTrangThai';
GO
EXEC dbo.usp_KhachHang_TheoTrangThai @TrangThai = 4;   -- biên dịch cho 4
EXEC dbo.usp_KhachHang_TheoTrangThai @TrangThai = 1;   -- dùng lại kế hoạch của 4
GO

-- minh họa: kế hoạch biên dịch cho @TrangThai = 1, chạy với @TrangThai = 4
SELECT                                           DOP 4   Granted 6.144 KB   MaxUsed 6.144 KB
  Top                                            TOP 20
    Parallelism (Gather Streams)                 giữ thứ tự
      Sort (Top N Sort)                          ORDER BY SUM(TongTien) DESC
        Hash Match (Aggregate)                   GROUP BY KhachHangId    est 61.800    act 199.700
                                                 CẢNH BÁO: spill level 1, 4 spilled thread(s)
          Parallelism (Repartition Streams)      Hash KhachHangId
            Clustered Index Scan PK_DonHang      Predicate: TrangThai = @TrangThai
                                                 est 100.000   act 9.000.000   Rows Read 10.000.000

CREATE OR ALTER PROCEDURE dbo.usp_KhachHang_TheoTrangThai_Recompile
    @TrangThai tinyint
AS
SET NOCOUNT ON;
SELECT TOP (20) KhachHangId, COUNT_BIG(*) AS SoDon, SUM(TongTien) AS TongGiaTri
FROM dbo.DonHang
WHERE TrangThai = @TrangThai
GROUP BY KhachHangId
ORDER BY SUM(TongTien) DESC
OPTION (RECOMPILE);
GO

SET STATISTICS IO ON;
EXEC dbo.usp_KhachHang_TheoTrangThai_Recompile @TrangThai = 1;
SET STATISTICS IO OFF;

-- SQL Server 2022 trở lên
SELECT p.plan_id, p.query_id, p.plan_type_desc,
       v.parent_query_id, v.dispatcher_plan_id
FROM sys.query_store_plan AS p
LEFT JOIN sys.query_store_query_variant AS v
    ON v.query_variant_query_id = p.query_id
WHERE p.plan_type IN (1, 2);  -- 1 Dispatcher Plan, 2 Query Variant Plan

USE BanHang;
GO

SELECT
    p.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.last_compile_start_time,
    SUM(rs.count_executions) AS so_lan_chay,
    SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS tb_ms,
    SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) AS tb_logical_reads,
    MAX(rs.max_query_max_used_memory) * 8 AS grant_max_kb
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID(N'dbo.usp_KhachHang_TheoTrangThai')
  AND i.start_time >= DATEADD(DAY, -7, SYSDATETIMEOFFSET())
GROUP BY p.query_id, p.plan_id, p.is_forced_plan, p.last_compile_start_time
ORDER BY p.query_id, p.plan_id;

SELECT
    SWITCHOFFSET(i.start_time, '+07:00') AS bat_dau_gio_vn,
    rs.plan_id,
    SUM(rs.count_executions) AS so_lan_chay,
    SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS tb_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID(N'dbo.usp_KhachHang_TheoTrangThai')
  AND i.start_time >= DATEADD(DAY, -2, SYSDATETIMEOFFSET())
GROUP BY i.start_time, rs.plan_id
ORDER BY i.start_time, rs.plan_id;

#:property PublishAot=false
#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12

using System.Data.Common;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_kehoach;Integrated Security=true;TrustServerCertificate=true";
var options = new DbContextOptionsBuilder<BanHangDb>()
    .UseSqlServer(Cs)
    .AddInterceptors(new RecompileInterceptor())
    .Options;

await using (var db = new BanHangDb(options))
{
    await db.Database.ExecuteSqlRawAsync("ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;");
    await db.Database.ExecuteSqlRawAsync("ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;");
    Console.WriteLine($".NET {Environment.Version}");
    Console.WriteLine();
    Console.WriteLine(TopKhach(db, 1, "bao-cao-top-khach").ToQueryString());
    Console.WriteLine();
}

// Lần gọi đầu với trạng thái 1 (Mới), sau đó trạng thái 4 (Hoàn tất)
foreach (var trangThai in new byte[] { 1, 4, 4, 1 })
{
    await using var db = new BanHangDb(options);
    await TopKhach(db, trangThai, "bao-cao-top-khach").ToListAsync();
}
// Cùng câu, gắn thêm tag "recompile" để interceptor nối OPTION (RECOMPILE)
foreach (var trangThai in new byte[] { 1, 4, 4, 1 })
{
    await using var db = new BanHangDb(options);
    await TopKhach(db, trangThai, "bao-cao-top-khach-rc").TagWith("recompile").ToListAsync();
}

await using (var db = new BanHangDb(options))
{
    // Câu không RECOMPILE: tag nằm trong văn bản batch của plan cache, nên tìm được qua sys.dm_exec_sql_text
    var c = await db.Database.SqlQueryRaw<TrongCache>("""
        SELECT qs.query_hash AS QueryHash, qs.execution_count AS SoLan,
               qs.min_grant_kb AS GrantMinKb, qs.max_grant_kb AS GrantMaxKb,
               qs.min_used_grant_kb AS UsedMinKb, qs.max_used_grant_kb AS UsedMaxKb, qs.max_spills AS SpillMax,
               CAST(qp.query_plan AS nvarchar(max)) AS KeHoach
        FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
        WHERE st.text LIKE N'%-- bao-cao-top-khach%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
        """).SingleAsync();
    var bienDich = Regex.Match(c.KeHoach, "Column=\"@trangThai\"[^>]*ParameterCompiledValue=\"([^\"]+)\"").Groups[1].Value;
    Console.WriteLine($"Không RECOMPILE: {c.SoLan} lần chạy, @trangThai biên dịch cho {bienDich}, " +
                      $"grant {c.GrantMinKb}–{c.GrantMaxKb} KB, dùng {c.UsedMinKb}–{c.UsedMaxKb} KB, spill tối đa {c.SpillMax} page");

    // Query Store bỏ comment trước câu lệnh, nên tag không có trong query_sql_text: nối qua query_hash
    await db.Database.ExecuteSqlRawAsync("EXEC sys.sp_query_store_flush_db;");
    var ke = await db.Database.SqlQueryRaw<TrongQueryStore>("""
        SELECT CAST(CASE WHEN qt.query_sql_text LIKE N'%OPTION (RECOMPILE)%' THEN 1 ELSE 0 END AS bit) AS Recompile,
               q.query_id AS QueryId, p.plan_id AS PlanId, SUM(rs.count_executions) AS SoLan,
               MAX(rs.max_query_max_used_memory) * 8 AS GrantKb   -- cột này là memory grant, tính bằng page 8 KB
        FROM sys.query_store_query AS q
        JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
        JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
        JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
        WHERE q.query_hash = {0}
           OR (qt.query_sql_text LIKE N'%OPTION (RECOMPILE)%' AND CHARINDEX(N'[d].[TrangThai] = @trangThai', qt.query_sql_text) > 0)
        GROUP BY qt.query_sql_text, q.query_id, p.plan_id
        ORDER BY q.query_id, p.plan_id
        """, c.QueryHash).ToListAsync();
    Console.WriteLine();
    Console.WriteLine($"{"RECOMPILE",-10} {"query_id",8} {"plan_id",7} {"lần",4} {"grant KB",9}");
    foreach (var r in ke)
        Console.WriteLine($"{(r.Recompile ? "có" : "không"),-10} {r.QueryId,8} {r.PlanId,7} {r.SoLan,4} {r.GrantKb,9}");
}

static IQueryable<object> TopKhach(BanHangDb db, byte trangThai, string tag) =>
    db.DonHang.TagWith(tag)
        .Where(d => d.TrangThai == trangThai)
        .GroupBy(d => d.KhachHangId)
        .Select(g => new { KhachHangId = g.Key, SoDon = g.LongCount(), TongGiaTri = g.Sum(d => d.TongTien) })
        .OrderByDescending(x => x.TongGiaTri)
        .Take(20);

// Câu nào mang tag "recompile" thì nối OPTION (RECOMPILE) vào cuối.
sealed class RecompileInterceptor : DbCommandInterceptor
{
    static void Them(DbCommand command)
    {
        if (command.CommandText.Contains("-- recompile", StringComparison.Ordinal))
            command.CommandText += Environment.NewLine + "OPTION (RECOMPILE)";
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        Them(command);
        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        Them(command);
        return ValueTask.FromResult(result);
    }
}

record TrongCache(byte[] QueryHash, long SoLan, long GrantMinKb, long GrantMaxKb, long UsedMinKb, long UsedMaxKb, long SpillMax, string KeHoach);
record TrongQueryStore(bool Recompile, long QueryId, long PlanId, long SoLan, long GrantKb);

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(DbContextOptions<BanHangDb> options) : DbContext(options)
{
    public DbSet<DonHang> DonHang => Set<DonHang>();
    protected override void OnModelCreating(ModelBuilder b) => b.Entity<DonHang>(e =>
    {
        e.ToTable("DonHang");
        e.HasKey(x => new { x.NgayTao, x.DonHangId });
        e.Property(x => x.TongTien).HasPrecision(18, 2);
    });
}

.NET 10.0.12

DECLARE @p int = 20;
DECLARE @trangThai tinyint = CAST(1 AS tinyint);

-- bao-cao-top-khach

SELECT TOP(@p) [d].[KhachHangId], COUNT_BIG(*) AS [SoDon], COALESCE(SUM([d].[TongTien]), 0.0) AS [TongGiaTri]
FROM [DonHang] AS [d]
WHERE [d].[TrangThai] = @trangThai
GROUP BY [d].[KhachHangId]
ORDER BY COALESCE(SUM([d].[TongTien]), 0.0) DESC

Không RECOMPILE: 4 lần chạy, @trangThai biên dịch cho (1), grant 2992–2992 KB, dùng 1352–2608 KB, spill tối đa 138 page

RECOMPILE  query_id plan_id  lần  grant KB
không             1       1    4      2992
có                2       2    2      1432
có                2       3    2      3928