Điều tra một truy vấn chậm: ép kế hoạch và tìm nguyên nhân gốc

Lúc 09:00 thủ phạm đã rõ: một kế hoạch biên dịch cho khách 42 đang được dùng lại cho mọi khách, và CPU máy SQL ở 100%. Xóa plan cache hay biên dịch lại thủ tục đều có thể ra đúng kế hoạch xấu đó lần nữa. Phần 2 tắt sự cố bằng ép kế hoạch cũ sau khi kiểm tra nó an toàn, lần ra vì sao kế hoạch đổi đúng sáng thứ Hai, và chọn cách sửa gốc.

1. Đến 09:00 đã biết gì #

Phần 1 lần theo số đo từ cảnh báo APM lúc 08:33 tới kế hoạch thủ phạm. Endpoint GET /api/khach-hang/{id}/don-hang gọi thủ tục dbo.usp_DonHang_CuaKhach, lấy TOP (50) đơn mới nhất của một khách theo KhachHangId, sắp theo NgayTao giảm dần. Query Store giữ hai kế hoạch của cùng query 4187:

Với plan 5521, mỗi lần gọi cho một khách thường tốn khoảng 0,7 s CPU, nên 8 core chỉ gánh được khoảng 11 lần gọi mỗi giây. Lúc 09:00 lưu lượng là 35 lần/giây và 63% lượt gọi lỗi timeout.

2. Bước 5 — Giảm nhẹ lúc 09:05: ép kế hoạch cũ #

09:00, ba cách nhanh được đặt cạnh nhau:

Trước khi ép, kiểm tra một lo ngại có thật: kế hoạch seek có thể bắt khách 42 làm 300.000 lần Key Lookup. Điều đó xảy ra nếu giữa Index Seek và Top có một Sort. Khi đó mọi dòng của khách 42 phải được lookup trước khi sắp xếp, mỗi lần 3 page vì cây PK_DonHang ở mỗi partition sâu 3 tầng: khoảng 900.000 logical reads mỗi lần gọi.

Plan 3907 không có Sort. Index Seek chạy Ordered, BACKWARD, trả dòng theo NgayTao giảm dần qua cả bốn partition. Nested Loops lookup từng dòng và Top dừng sau dòng thứ 50. Khách 42 chỉ tốn khoảng 50 × 3 + 6 ≈ 156 logical reads.

EXEC sys.sp_query_store_force_plan @query_id = 4187, @plan_id = 3907;

SELECT plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE query_id = 4187;

Lệnh ép có hiệu lực từ lần thực thi kế tiếp. Kế hoạch đang nằm trong cache bị thay, không cần xóa cache.

Cái giá của bước này:

• Khách 42 từ 12 lên khoảng 156 logical reads mỗi lần gọi. Với 65 lần gọi mỗi ngày, đó là khoảng 10.000 page mỗi ngày, không đáng kể.
• Kế hoạch bị ghim chỉ còn tác dụng khi engine dựng lại được đúng hình dạng đó. Đổi hoặc xóa chỉ mục mà kế hoạch dùng có thể làm lần ép thất bại mà không báo lỗi cho ứng dụng (phần 3).
• Nguyên nhân chưa đổi. Câu khác lọc theo KhachHangId với TOP vẫn có thể bị đúng chuyện này.

3. Bước 6 — Nguyên nhân gốc: dữ liệu lệch và lần biên dịch đầu tiên #

Truy vấn dưới đây đọc ba bước đầu của histogram trên IX_DonHang_KhachHang, vector mật độ, và lần cập nhật thống kê gần nhất.

SELECT
    h.step_number,
    h.range_high_key,
    h.range_rows,
    h.equal_rows,
    h.distinct_range_rows,
    h.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS h
WHERE s.object_id = OBJECT_ID(N'dbo.DonHang')
  AND s.name = N'IX_DonHang_KhachHang'
  AND h.step_number <= 3;

DBCC SHOW_STATISTICS (N'dbo.DonHang', N'IX_DonHang_KhachHang') WITH DENSITY_VECTOR;

SELECT s.name, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.DonHang')
  AND s.name = N'IX_DonHang_KhachHang';

Kết quả minh họa:

step_number  range_high_key  range_rows  equal_rows  distinct_range_rows  average_range_rows
1            1               0           12          0                    1
2            42              1310        300000      40                   32.75
3            1270            62400       1390        1227                 50.86

All density   Average Length  Columns
5E-06         4               KhachHangId
...

name                  last_updated         rows      rows_sampled  steps  modification_counter
IX_DonHang_KhachHang  2026-10-05 01:12:40  10038950  10038950      137    2140

Khách 42 là một bước riêng của histogram, với EQ_ROWS = 300.000. Mật độ của KhachHangId là 1 / 200.000 = 0,000005; nhân với 10 triệu dòng ra 50, đúng số đơn trung bình mỗi khách. Trình tối ưu có ba cách ước lượng cho cùng một câu:

Với khách thường, scan không có lợi từ row goal: muốn gặp 50 đơn của một khách có vài chục đơn thì phải đọc cả bảng, nên seek thắng. Ba mắt xích làm kế hoạch đổi đúng sáng thứ Hai:

1. Kế hoạch của một câu trong thủ tục được biên dịch một lần với giá trị tham số của lần gọi đầu, rồi dùng lại cho mọi lần gọi sau, cho đến khi có sự kiện buộc biên dịch lại.
2. Cập nhật thống kê là một sự kiện như vậy. Từ SQL Server 2016 với compatibility 130 trở lên, ngưỡng tự cập nhật thống kê của bảng n dòng là MIN(500 + 0,20 × n, √(1000 × n)). Với 10 triệu dòng, ngưỡng là √(10.000.000.000) = 100.000 thay đổi. Một tuần có khoảng 13.000 đơn mỗi ngày × 7 ≈ 91.000 dòng mới, chưa tới ngưỡng. Thống kê chỉ đổi khi job Chủ nhật chạy FULLSCAN, và lần thực thi kế tiếp biên dịch lại.
3. Trước 30/09, người gọi đầu tiên sáng thứ Hai là nhân viên chăm sóc khách hàng, khoảng 07:00, với một khách bình thường. Plan 3907 sống qua 7 lần bảo trì như vậy. Job ERP của đại lý 42 chạy từ 06:00 đưa khách 42 lên đầu hàng. Lần bảo trì đầu tiên sau 30/09 là đêm 04/10.

flowchart TD
  A["30/09: ERP đại lý 42 gọi từ 06:00"] --> D
  B["04/10 23:30: UPDATE STATISTICS FULLSCAN"] --> C["Kế hoạch phải biên dịch lại"]
  C --> D["06:02:14: lần gọi đầu là khách 42"]
  D --> E["Plan 5521: scan ngược PK_DonHang + Top"]
  E --> F["Khách thường: ~46.000 page, 0,7 s CPU mỗi lần"]
  F --> G["08:30: lưu lượng vượt ~11 lần/giây, CPU 100%"]

Không có mắt xích nào là lỗi riêng lẻ. Thống kê đúng, histogram đúng, kế hoạch đúng cho giá trị nó được biên dịch. Lỗi là một kế hoạch duy nhất phải phục vụ hai phân phối khác nhau gấp 15.000 lần.

4. Các nguyên nhân khác hay gặp với cùng triệu chứng #

"API đột nhiên chậm" có nhiều nguyên nhân, và ảnh chụp request, chênh lệch wait, Query Store ở phần 1 phân biệt chúng nhanh:

Mỗi nguyên nhân có một truy vấn xác nhận. Truy vấn 4 để dạng chú thích vì sys.dm_db_wait_stats chỉ có trên Azure SQL Database. Với truy vấn 5, so sqlserver_start_time với initial_compile_start_time của các kế hoạch trong Query Store.

-- 1. Chuyển kiểu ngầm: kế hoạch trong cache có cảnh báo PlanAffectingConvert
SELECT TOP (20) qs.execution_count, qs.total_worker_time, st.text
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 CAST(qp.query_plan AS nvarchar(max)) LIKE N'%PlanAffectingConvert%'
ORDER BY qs.total_worker_time DESC;
-- 2. Thống kê cũ: số thay đổi kể từ lần cập nhật cuối
SELECT s.name, sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.DonHang');
-- 3. Chặn khóa: ai đang chờ ai
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0;
-- 4. Trần log, chỉ chạy trên Azure SQL Database
-- SELECT wait_type, waiting_tasks_count, wait_time_ms
-- FROM sys.dm_db_wait_stats WHERE wait_type = N'LOG_RATE_GOVERNOR';
-- 5. Plan cache trống: instance khởi động lúc nào
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
-- 6. Hàng đợi memory grant
SELECT session_id, requested_memory_kb, granted_memory_kb, wait_time_ms
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL;

5. Chọn cách sửa #

PSP (Parameter Sensitive Plan optimization) có từ SQL Server 2022 với compatibility 160. Nó chỉ xét điều kiện bằng, đúng dạng KhachHangId = @KhachHangId, và chỉ bật khi histogram đủ lệch. Microsoft không công bố ngưỡng. Khảo sát của Paul White cho thấy tỷ lệ giữa EQ_ROWS lớn nhất và nhỏ nhất trong histogram phải từ 100.000 trở lên.

Bỏ điều kiện step_number <= 3 ở truy vấn histogram mục 3 và lấy MIN, MAX của equal_rows: trên BanHang là 10 và 300.000, tỷ lệ 30.000. Trên bản thử SQL Server 2025, compatibility 160 và 170, sự kiện Extended Events parameter_sensitive_plan_optimization_skipped_reason báo SkewnessThresholdNotMet, và kế hoạch vẫn là scan biên dịch cho khách 42. Nâng phiên bản để có PSP không sửa được ca này.

Cách được chọn là chỉ mục phủ, vì nó gỡ đúng mắt xích kỹ thuật: lý do để scan thắng seek khi biên dịch cho khách 42. Khi chỉ mục chứa đủ TrangThai và TongTien, seek có thứ tự cộng Top không cần lookup, và rẻ hơn scan với mọi giá trị. Ai gọi trước cũng ra cùng một kế hoạch. Phần 3 dựng và kiểm chứng chỉ mục đó.

6. Áp dụng trong .NET #

Câu LINQ của EF Core cũng là câu có tham số. EF Core gửi nó qua sp_executesql, SQL Server biên dịch với giá trị đầu tiên và dùng lại kế hoạch y như với thủ tục. TagWith đặt một dòng comment ở đầu câu SQL, để khi điều tra biết kế hoạch nào thuộc endpoint nào:

var donHang = await db.DonHang
    .TagWith("GET /api/khach-hang/{id}/don-hang")   // hằng số
    .Where(d => d.KhachHangId == id)
    .OrderByDescending(d => d.NgayTao)
    .Take(50)
    .AsNoTracking()
    .ToListAsync();

Bản chạy thử dùng Microsoft.EntityFrameworkCore.SqlServer 10.0.12 trên cùng database LocalDB 4 triệu đơn của phần 1: khách 42 gọi trước, rồi khách 7315. Thêm hai câu cố ý đưa mã khách vào tag (TagWith($"khach {id}"), khách 18420 và 18421). Chương trình đọc lại Query Store và plan cache:

query_id | plan_id | so_lan | bien_dich_voi | dau_cau
1 | 1 | 2 | (42) | (@id int,@p int)SELECT TOP(@p) [d].[NgayTao], [d].[DonHangId
2 | 2 | 2 | (18420) | (@id int,@p int)SELECT TOP(@p) [d].[NgayTao], [d].[DonHangId

query_id | count_compiles | so_lan | last_logical_reads | dau_batch
1 | 1 | 2 | 18448 | (@p int,@id int)-- GET /api/khach-hang/{id}/don-hang
2 | 2 | 1 | 6 | (@p int,@id int)-- khach 18420    SELECT TOP(@p) [d].[
2 | 2 | 1 | 6 | (@p int,@id int)-- khach 18421    SELECT TOP(@p) [d].[

Ba điều đọc được:

• Câu LINQ được biên dịch cho khách 42 rồi dùng lại cho khách 7315. Lần chạy cuối đọc 18.448 page, gần trọn tầng lá 18.404 page của bảng thử. Câu EF Core bị đúng chuyện của thủ tục, và ép kế hoạch hay chỉ mục phủ áp dụng y như vậy.
• query_sql_text của Query Store bắt đầu từ SELECT, không có tag. Tag nằm trong văn bản batch của plan cache: nối query_hash của Query Store với sys.dm_exec_query_stats rồi đọc sys.dm_exec_sql_text, khi batch còn trong cache. Ảnh chụp request ở bước 2 của phần 1 cũng thấy tag, vì nó đọc văn bản batch.
• Tag phải là hằng số. Đưa mã khách vào tag làm mỗi giá trị một batch: plan cache giữ hai mục, count_compiles lên 2, còn Query Store gộp cả hai vào một query_id.

Với Dapper hay SQL thô, đặt dòng -- tên endpoint ở đầu chuỗi SQL cho cùng hiệu quả, vì EF Core cũng chỉ làm đúng như vậy. Toàn bộ chương trình chạy bằng dotnet run TagQueryStore.cs trên .NET SDK 10.0.401, runtime 10.0.12. Dòng #:property PublishAot=false là bắt buộc: file-based app mặc định bật PublishAot, và EF Core khi đó báo Model building is not supported when publishing with NativeAOT.

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

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

const string ChuoiKetNoi = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_dieutra;Integrated Security=true;TrustServerCertificate=true";

await using (var conn = new SqlConnection(ChuoiKetNoi))
{
    await conn.OpenAsync();
    // Làm sạch để dễ đọc kết quả: xóa Query Store và plan cache của database thử.
    await new SqlCommand("ALTER DATABASE CURRENT SET QUERY_STORE CLEAR; ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;", conn).ExecuteNonQueryAsync();
}

await using var db = new BanHangDb(ChuoiKetNoi);

// Cùng câu hỏi với dbo.usp_DonHang_CuaKhach, viết bằng LINQ. Khách 42 gọi trước.
foreach (var id in new[] { 42, 7315 })
{
    var donHang = await db.DonHang
        .TagWith("GET /api/khach-hang/{id}/don-hang")   // hằng số
        .Where(d => d.KhachHangId == id)
        .OrderByDescending(d => d.NgayTao)
        .Take(50)
        .AsNoTracking()
        .ToListAsync();
    Console.WriteLine($"khach {id}: {donHang.Count} don");
}

// Sai cách: đưa giá trị vào tag thì mỗi giá trị là một câu khác trong plan cache và Query Store.
foreach (var id in new[] { 18420, 18421 })
    await db.DonHang.TagWith($"khach {id}").Where(d => d.KhachHangId == id).Take(1).AsNoTracking().ToListAsync();

await using (var conn = new SqlConnection(ChuoiKetNoi))
{
    await conn.OpenAsync();
    await new SqlCommand("EXEC sys.sp_query_store_flush_db;", conn).ExecuteNonQueryAsync();
    // 1. Query Store: câu nào, kế hoạch nào, biên dịch với giá trị nào.
    await In(conn, """
        WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
        SELECT q.query_id, p.plan_id,
               (SELECT SUM(count_executions) FROM sys.query_store_runtime_stats AS r WHERE r.plan_id = p.plan_id) AS so_lan,
               TRY_CAST(p.query_plan AS xml).value('(//ParameterList/ColumnReference[@Column="@id"]/@ParameterCompiledValue)[1]', 'nvarchar(20)') AS bien_dich_voi,
               LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), ' '), CHAR(10), ' '), 60) AS dau_cau
        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
        WHERE qt.query_sql_text LIKE N'%FROM `[dbo`].`[DonHang`] AS `[d`]%' ESCAPE '`'
        ORDER BY q.query_id;
        """);
    // 2. Query Store không giữ tag trong query_sql_text. Nối query_hash sang plan cache:
    //    văn bản batch có tag, mỗi batch một mục cache, kèm số page đọc ở lần chạy cuối.
    await In(conn, """
        SELECT q.query_id, q.count_compiles, qs.execution_count AS so_lan, qs.last_logical_reads,
               LEFT(REPLACE(REPLACE(st.text, CHAR(13), ' '), CHAR(10), ' '), 54) AS dau_batch
        FROM sys.query_store_query AS q
        JOIN sys.dm_exec_query_stats AS qs ON qs.query_hash = q.query_hash
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
        WHERE pa.attribute = 'dbid' AND CONVERT(int, pa.value) = DB_ID()
          AND (st.text LIKE N'%-- GET /api/khach-hang%' OR st.text LIKE N'%-- khach 1842%')
        ORDER BY q.query_id, qs.creation_time;
        """);
}
static async Task In(SqlConnection conn, string sql)
{
    await using var r = await new SqlCommand(sql, conn).ExecuteReaderAsync();
    Console.WriteLine();
    Console.WriteLine(string.Join(" | ", Enumerable.Range(0, r.FieldCount).Select(r.GetName)));
    while (await r.ReadAsync())
        Console.WriteLine(string.Join(" | ", Enumerable.Range(0, r.FieldCount).Select(i => r[i])));
}
sealed 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; }
}

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

Những chỗ hay hiểu sai #

• "Cập nhật thống kê làm kế hoạch tệ đi." Thống kê và histogram đều đúng. Cập nhật chỉ mở lại lần biên dịch, và người gọi đầu tiên sau đó chọn kế hoạch cho mọi người.
• "DBCC FREEPROCCACHE tắt sự cố nhanh nhất." Mọi câu biên dịch lại cùng lúc trên CPU đang 100%, và lần gọi kế tiếp vẫn có thể là khách 42.
• "Ép kế hoạch là sửa xong." Nguyên nhân chưa đổi, và kế hoạch bị ghim có thể hết tác dụng khi chỉ mục đổi.
• "Lên SQL Server 2022 thì PSP tự xử lý." Histogram ở đây lệch 30.000 lần, dưới ngưỡng khoảng 100.000 lần mà PSP cần.
• "Kế hoạch nhanh với giá trị đang gõ là kế hoạch tốt." Khi tham số trải từ 1 đến 300.000 dòng, câu hỏi đúng là kế hoạch nào tốt cho mọi giá trị.

Kết luận #

Ép kế hoạch là phanh tay: tắt sự cố trong vài phút và hoàn tác được, nhưng không chạm tới nguyên nhân. Nguyên nhân là một kế hoạch duy nhất phải phục vụ hai phân phối khác nhau 15.000 lần, nên sửa gốc là làm cho mọi giá trị ra cùng một kế hoạch tốt.

Trong dự án .NET của bạn:

• Giữ trong runbook sys.sp_query_store_force_plan cùng truy vấn đọc plan XML, và đọc kế hoạch định ép với giá trị lớn nhất trước khi ép.
• Gắn TagWith("tên endpoint") hằng số cho các câu EF Core nóng; với Dapper, đặt dòng -- tên endpoint ở đầu câu SQL. Không đưa giá trị vào tag.
• Xem việc thêm một khách, đại lý hay job có dữ liệu lệch nhiều bậc như một thay đổi có rủi ro: thử endpoint với giá trị lớn nhất và một giá trị điển hình, theo cả hai thứ tự biên dịch.
• Đặt lịch job cập nhật thống kê cạnh lịch gọi của các client, để biết ai là người gọi đầu tiên sau mỗi đợt bảo trì.

Đọc tiếp #

• Phần 1: từ cảnh báo đến kế hoạch thủ phạm: số APM, request, wait, Query Store và plan XML.
• Phần 3: sửa gốc bằng chỉ mục phủ và hậu kiểm: dựng chỉ mục an toàn, kiểm chứng, gỡ ép và giám sát.
• Thống kê, histogram và ngưỡng tự cập nhật: histogram, mật độ, ngưỡng tự cập nhật thống kê.
• Chỉ mục B-tree, seek, scan và key lookup: chi phí lookup.
• Parameter sniffing và Query Store: parameter sniffing, PSP và ép kế hoạch bằng Query Store.

Nguồn #

• sys.query_store_plan
• sys.query_store_query
• Parameter Sensitive Plan optimization
• Query Store hints
• sys.sp_query_store_set_hints
• Query processing architecture guide: parameter sensitivity, recompilation
• Statistics: AUTO_UPDATE_STATISTICS thresholds
• Paul White: Parameter Sensitive Plan Optimization thresholds
• Query Tags - EF Core