Điều tra một truy vấn chậm từ cảnh báo đến bản sửa

Bài này là nhật ký điều tra một sự cố trên BanHang. Sáng thứ Hai 2026-10-05, API liệt kê đơn hàng của một khách chậm từ 80 ms lên vài giây, rồi bắt đầu timeout. Mỗi bước ghi ba thứ: thấy gì, lệnh nào cho thấy điều đó, và kết luận rút ra. Lý thuyết nằm ở các chương khác: histogram và chi phí seek, scan, lookup ở Chỉ mục, thống kê, toán tử và parameter sniffing ở Đọc kế hoạch thực thi, cách bật Query Store ở Kỹ thuật thường dùng. Bài này chỉ có phần thực hành.

Môi trường là BanHang của các chương trước: SQL Server 2019 bản Enterprise, compatibility level 150, 8 core, MAXDOP 4, Query Store bật với chu kỳ gom số liệu mặc định 60 phút. Số APM, wait và Query Store theo giờ là minh họa. Hình dạng kế hoạch, số logical reads và các phép thử cách sửa đã được dựng lại trên một bản sao thử 10 triệu đơn cùng phân phối, chạy SQL Server 2019 CU27. Riêng phép thử PSP chạy trên SQL Server 2025. Máy thử khác máy production, nên thời gian CPU chỉ đúng về bậc độ lớn.

1. Bối cảnh: một endpoint, một thủ tục #

Màn hình chăm sóc khách hàng và cổng đại lý gọi GET /api/khach-hang/{id}/don-hang để lấy 50 đơn mới nhất của một khách. API gọi một thủ tục:

CREATE OR ALTER PROCEDURE dbo.usp_DonHang_CuaKhach
    @KhachHangId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (50)
        DonHangId,
        NgayTao,
        TrangThai,
        TongTien
    FROM dbo.DonHang
    WHERE KhachHangId = @KhachHangId
    ORDER BY NgayTao DESC;
END;

Lời gọi trong API, ASP.NET Core với Dapper trên Microsoft.Data.SqlClient:

using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;

app.MapGet("/api/khach-hang/{id:int}/don-hang", async (int id, IConfiguration cfg) =>
{
    await using var conn = new SqlConnection(cfg.GetConnectionString("BanHang"));

    var donHang = await conn.QueryAsync<DonHangTomTat>(
        "dbo.usp_DonHang_CuaKhach",
        new { KhachHangId = id },              // int trong C# thành tham số int
        commandType: CommandType.StoredProcedure,
        commandTimeout: 5);                    // giây; quá hạn thì SqlClient hủy lệnh trên server

    return Results.Ok(donHang);
});

public sealed record DonHangTomTat(long DonHangId, DateTime NgayTao, byte TrangThai, decimal TongTien);

Dapper suy kiểu tham số từ kiểu C#. int thành int, khớp với @KhachHangId. Không có chuyển kiểu ngầm như khi gửi chuỗi bằng AddWithValue (xem Kiểu dữ liệu, collation và khóa chính). Nguyên nhân đó bị loại ngay từ đầu.

dbo.DonHang có ba chỉ mục, cả ba trên ps_DonHang_Ngay: PK_DonHang (NgayTao, DonHangId), IX_DonHang_KhachHang (KhachHangId, NgayTao) và chỉ mục lọc IX_DonHang_DangMo. Bảng chưa có NCCI_DonHang.

Hai sự kiện có vẻ không liên quan:

• Từ thứ Tư 2026-09-30, hệ ERP của đại lý 42 gọi chính endpoint này 15 phút một lần, từ 06:00 đến 22:00, để đồng bộ đơn mới. Khách 42 có khoảng 300.000 đơn, gấp 15.000 lần khách trung vị (20 đơn).
• Job bảo trì tối Chủ nhật bắt đầu 23:30. Bước UPDATE STATISTICS dbo.DonHang WITH FULLSCAN xong lúc 01:12 thứ Hai. Từ 01:12 đến 06:00, endpoint gần như không có lượt gọi.

2. Bước 1 — Định nghĩa triệu chứng bằng số #

08:33, APM cảnh báo: p95 của endpoint trên 2 giây liên tục 5 phút. Người trực mở bảng số liệu trước khi mở SQL Server. Mục tiêu của bước này là biết chính xác cái gì chậm, từ lúc nào, chậm bao nhiêu.

Đọc bảng:

• Lỗi là SqlException với Number = -2: lệnh vượt commandTimeout 5 giây. p95 dừng ở 5,0 s vì đó là trần timeout, không phải vì hệ thống đã ổn.
• Chậm không bắt đầu lúc 08:30. p95 đã khoảng 1 giây từ sau 06:02, gấp 12 lần bình thường. Ngưỡng cảnh báo tuyệt đối 2 giây không bắt được mức đó.
• CPU tăng theo lưu lượng gần như tuyến tính: 3 lần/giây ứng với 33%, 9 lần/giây ứng với 86%. Mỗi lần gọi tốn một lượng CPU lớn và cố định.
• Các endpoint khác chậm thêm 20–40 ms, cùng lúc CPU lên 100%. Endpoint này chậm gấp hơn 60 lần. Một endpoint kéo cả máy, không phải cả máy kéo một endpoint.

Giả thuyết sau bước 1: câu SELECT trong dbo.usp_DonHang_CuaKhach đang tốn CPU trên SQL Server. Cần xác nhận ở phía database.

3. Bước 2 — Xác nhận vấn đề nằm ở database #

08:41, ảnh chụp các request đang chạy, gom theo thủ tục và trạng thái:

SELECT
    OBJECT_NAME(t.objectid, t.dbid) AS module,
    r.status,
    r.last_wait_type,
    COUNT(*) AS so_request,
    SUM(CASE WHEN r.blocking_session_id <> 0 THEN 1 ELSE 0 END) AS bi_chan,
    AVG(r.cpu_time) AS avg_cpu_ms,
    AVG(r.total_elapsed_time) AS avg_elapsed_ms,
    AVG(r.logical_reads) AS avg_reads,
    SUM(r.granted_query_memory) * 8 AS memory_kb
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
GROUP BY OBJECT_NAME(t.objectid, t.dbid), r.status, r.last_wait_type
ORDER BY so_request DESC;

Kết quả minh họa:

79 request cùng một thủ tục. 8 đang running, đúng bằng số core. 71 đang runnable: sẵn sàng chạy, xếp hàng chờ CPU. Request runnable không có wait_type. Cột last_wait_type cho biết chúng vừa tự nhường CPU sau một quantum 4 ms. Mỗi request mới dùng 236 ms CPU trong 2.410 ms tồn tại, khoảng 10% một core. 8 core chia cho 79 request cũng ra khoảng 10%.

Không request nào bị chặn. memory_kb bằng 0: kế hoạch không có Sort hay Hash nên không xin memory grant. sys.dm_os_schedulers cho cùng bức tranh: mỗi scheduler có 8 đến 9 task ở runnable_tasks_count.

Lấy chênh lệch wait trong 60 giây. Con số tích lũy từ lúc khởi động instance không nói được gì về 60 giây vừa qua.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w1
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:01:00';

SELECT
    w2.wait_type,
    w2.waiting_tasks_count - w1.waiting_tasks_count AS so_lan,
    w2.wait_time_ms - w1.wait_time_ms AS cho_ms,
    w2.signal_wait_time_ms - w1.signal_wait_time_ms AS signal_ms
FROM sys.dm_os_wait_stats AS w2
JOIN #w1 AS w1
    ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
  AND w2.wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'WAITFOR', N'XE_TIMER_EVENT',
        N'XE_DISPATCHER_WAIT', N'REQUEST_FOR_DEADLOCK_SEARCH', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'DIRTY_PAGE_POLL',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP')
ORDER BY cho_ms DESC;

DROP TABLE #w1;

Kết quả minh họa:

4.254.300 ms wait trong 60.000 ms đồng hồ nghĩa là trung bình khoảng 71 task đứng chờ cùng lúc, khớp với 71 request runnable. signal_ms gần bằng toàn bộ cho_ms: thời gian nằm trong hàng đợi CPU, không chờ tài nguyên nào. 119.840 lần yield trong 60 giây là 8 core × 250 quantum mỗi giây.

Wait vắng mặt cũng là bằng chứng. Không có RESOURCE_SEMAPHORE: không câu nào xếp hàng xin memory grant. Không có CXPACKET hay CXCONSUMER: câu nóng không chạy song song. Không có LCK_M_*: không có chặn khóa (Transaction, khóa, isolation). PAGEIOLATCH_SH chỉ 410 ms: dữ liệu đã nằm trong buffer pool, chi phí là đọc page trong RAM, đo bằng logical reads chứ không phải I/O đĩa.

Kết luận bước 2: nghẽn nằm trong SQL Server, ở CPU, do một thủ tục. Mỗi lần thực thi làm nhiều việc hơn bình thường. Câu hỏi tiếp theo là kế hoạch nào.

4. Bước 3 — Query Store: một câu, hai kế hoạch #

Thủ tục chỉ có một câu lệnh, nên tìm theo object_id:

SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    SWITCHOFFSET(p.initial_compile_start_time, '+07:00') AS bien_dich_luc,
    SWITCHOFFSET(p.last_execution_time, '+07:00') AS chay_cuoi_luc,
    p.count_compiles
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
WHERE q.object_id = OBJECT_ID(N'dbo.usp_DonHang_CuaKhach')
ORDER BY p.plan_id;

Kết quả minh họa:

Plan 3907 đã được biên dịch 8 lần từ 10/08: lần đầu, rồi sau mỗi đợt bảo trì Chủ nhật. Lần nào cũng ra cùng hình dạng, nên Query Store gộp vào cùng plan_id. Plan 5521 xuất hiện lúc 06:02:14 và được dùng từ đó.

Số liệu chạy theo từng khung giờ:

SELECT
    p.plan_id,
    SWITCHOFFSET(i.start_time, '+07:00') AS tu,
    rs.execution_type_desc AS loai,
    rs.count_executions AS so_lan,
    CAST(rs.avg_duration / 1000.0 AS decimal(10, 1)) AS avg_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(10, 1)) AS avg_cpu_ms,
    CAST(rs.avg_logical_io_reads AS bigint) AS avg_reads
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p
    ON p.plan_id = rs.plan_id
WHERE p.query_id = 4187
  AND i.start_time >= TODATETIMEOFFSET('2026-10-05T06:00:00', '+07:00')
ORDER BY i.start_time, p.plan_id, rs.execution_type;

Kết quả minh họa:

plan_id  tu                          loai     so_lan  avg_ms  avg_cpu_ms  avg_reads
5521     2026-10-05 06:00:00 +07:00  Regular    3960   811.4       702.6      45310
5521     2026-10-05 07:00:00 +07:00  Regular    9540   856.0       707.1      45420
5521     2026-10-05 08:00:00 +07:00  Regular   23180  2940.7       711.3      45390
5521     2026-10-05 08:00:00 +07:00  Aborted   19460  5000.2       318.4      20700
5521     2026-10-05 09:00:00 +07:00  Regular    1150  4120.5       709.0      45360
5521     2026-10-05 09:00:00 +07:00  Aborted    4900  5000.1       305.2      19900
3907     2026-10-05 09:00:00 +07:00  Regular  358400     5.2         0.2         85

Cùng truy vấn cho khung 08:00 thứ Hai 28/09: plan 3907, 51.840 lần, 2,8 ms, CPU 0,2 ms, 86 reads.

• Logical reads mỗi lần tăng từ 86 lên khoảng 45.400, gấp hơn 500 lần. CPU tăng từ 0,2 ms lên 0,7 s.
• Dòng Aborted là những lần client hủy khi hết 5 giây. Chúng vẫn tiêu CPU trước khi bị hủy, trung bình khoảng 0,3 s mỗi lần.
• Thời gian trung bình của plan 5521 lúc 06:00 là 0,8 s dù CPU còn rảnh. Kế hoạch này chậm ngay cả khi không có hàng đợi.

Đối chiếu với log của API: lần gọi đầu tiên sau 01:12 là 06:02:14, id = 42, từ client ERP của đại lý. Thời điểm biên dịch plan 5521 trùng đến từng giây.

5. Bước 4 — Đọc kế hoạch xấu #

Kế hoạch lưu trong Query Store là kế hoạch ước lượng, kèm giá trị tham số lúc biên dịch:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    p.plan_id,
    x.qp.value('(//ParameterList/ColumnReference[@Column="@KhachHangId"]/@ParameterCompiledValue)[1]', 'nvarchar(20)') AS gia_tri_bien_dich,
    r.n.value('@PhysicalOp', 'nvarchar(60)') AS toan_tu,
    r.n.value('(*/@ScanDirection)[1]', 'nvarchar(10)') AS chieu,
    r.n.value('@EstimateRows', 'float') AS uoc_luong,
    r.n.value('@EstimateRowsWithoutRowGoal', 'float') AS khong_row_goal,
    r.n.value('@EstimatedTotalSubtreeCost', 'float') AS chi_phi
FROM sys.query_store_plan AS p
CROSS APPLY (SELECT TRY_CAST(p.query_plan AS xml) AS qp) AS x
CROSS APPLY x.qp.nodes('//RelOp') AS r(n)
WHERE p.query_id = 4187
ORDER BY p.plan_id, r.n.value('@NodeId', 'int');

Kết quả minh họa:

plan_id  gia_tri_bien_dich  toan_tu               chieu     uoc_luong  khong_row_goal  chi_phi
3907     (118230)           Top                                  50                    0.181
3907     (118230)           Nested Loops                         50            66.9    0.181
3907     (118230)           Index Seek            BACKWARD       50            66.9    0.013
3907     (118230)           Clustered Index Seek  FORWARD         1                    0.168
5521     (42)               Top                                  50                    0.021
5521     (42)               Clustered Index Scan  BACKWARD       50          300000    0.020

Plan 3907, biên dịch cho khách 118230: Index Seek có thứ tự, đọc ngược IX_DonHang_KhachHang qua bốn partition, Key Lookup sang PK_DonHang lấy TrangThai và TongTien, rồi Top. Dòng Clustered Index Seek mang thuộc tính Lookup, SSMS vẽ nó thành Key Lookup. Plan 5521, biên dịch cho khách 42: scan ngược PK_DonHang với điều kiện KhachHangId = @KhachHangId, rồi Top.

Plan 5521 là kế hoạch tốt cho khách 42. Trình tối ưu thấy 300.000 trên 10 triệu dòng khớp, tức 3%. Đọc ngược PK_DonHang từ đơn mới nhất, cứ khoảng 33 dòng gặp một đơn của khách 42. 50 đơn đầu tiên nằm trong khoảng 50 / 0,03 ≈ 1.667 dòng, chừng 8 page lá. Đó là row goal: TOP (50) cho phép dừng sớm, nên chi phí ước lượng chỉ 0,021. Với khách 42, ước lượng này đúng: 12 logical reads. Seek cộng 50 lần Key Lookup tốn 0,18, đắt gần 9 lần, nên bị loại.

Cùng kế hoạch đó với khách 7315 (20 đơn): scan đi ngược từ đơn mới nhất về đơn cũ nhất mà không gom đủ 50 dòng. Nó đọc toàn bộ tầng lá, 10.000.000 / 218 ≈ 45.872 page. Khách có 50 đơn trải trên ba năm cũng phải đọc gần hết bảng mới gặp đơn thứ 50.

MAXDOP 4 không đóng vai trò gì. Chi phí 0,021 thấp xa ngưỡng cost threshold for parallelism, kế hoạch chạy một luồng. Vì vậy wait là SOS_SCHEDULER_YIELD, không phải CXPACKET.

Để thấy số thật, lấy actual plan cho một khách thường:

SET ARITHABORT OFF;   -- cùng giá trị với SqlClient, để dùng chung mục plan cache với ứng dụng
SET STATISTICS IO ON;
SET STATISTICS XML ON;

EXEC dbo.usp_DonHang_CuaKhach @KhachHangId = 7315;

SET STATISTICS XML OFF;
SET STATISTICS IO OFF;

Ước lượng 50, thực tế 20 dòng ra, nhưng 10 triệu dòng đọc. Cột cần nhìn là Number of Rows Read, không phải Actual Number of Rows.

Sức chứa của máy với kế hoạch này: 8 core chia 0,7 s mỗi lần gọi, tối đa khoảng 11 lần gọi mỗi giây, khi CPU không làm gì khác. Lúc 08:30 lưu lượng là 13 lần/giây. Lúc cao điểm 300 lần/giây, kế hoạch này cần 300 × 0,7 = 210 giây CPU mỗi giây, gấp 26 lần số core.

6. 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. Nó 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 (mục 10).
• 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.

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

Histogram của thống kê trên IX_DonHang_KhachHang:

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. Ba cách trình tối ưu có thể ướ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 bảo trì Chủ nhật chạy FULLSCAN. Lần thực thi kế tiếp sau đó 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 LR
  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.

8. 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 7 và lấy MIN, MAX của equal_rows: trên BanHang là 10 và 300.000, tỷ lệ 30.000. Bản thử chạy trên 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 sẽ 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ới khách 42, seek đọc khoảng 50 dòng ở page cuối của partition 2026, chi phí ước lượng 0,013. Scan ngược vẫn là 0,021. Seek thắng với mọi giá trị, nên ai gọi trước cũng ra cùng một kế hoạch.

Khóa giữ NgayTao tăng dần, không đổi sang DESC. Kế hoạch trên bản thử đọc ngược chỉ mục tăng dần, không có Sort, và với một kế hoạch một luồng dừng sau 50 dòng, đọc xuôi hay ngược tốn như nhau. Giữ khóa thì DROP_EXISTING chỉ thêm INCLUDE, các câu khác dùng chỉ mục này không đổi hành vi. SWITCH cũng đòi chỉ mục của bảng staging trùng chiều sắp xếp từng cột khóa, nên staging chỉ phải thêm INCLUDE.

Thứ tự giữa các partition cần kiểm chứng, không nên giả định. Chỉ mục được sắp theo (KhachHangId, NgayTao) bên trong từng partition. KB 2965553 của Microsoft mô tả TOP, MAX và MIN trên bảng phân vùng có thể phải quét toàn bộ chỉ mục khi cột đó không phải cột phân vùng. Ở đây cột sắp xếp chính là NgayTao, cột phân vùng, và cột đứng trước nó có điều kiện bằng. Trên SQL Server 2019 CU27, kế hoạch là Index Seek Ordered, BACKWARD qua các partition, không có Sort. Với khách 42, engine chỉ đụng partition 4 (trống) và partition 3.

9. Triển khai chỉ mục phủ an toàn #

Kích thước, theo cùng công thức ước lượng với chương lưu trữ. Mỗi dòng lá của chỉ mục không clustered mang khóa, khóa clustered và cột INCLUDE. NgayTao đã có trong khóa nên không lặp. Header dòng 1 byte, null bitmap 3 byte, slot 2 byte.

Chênh lệch khoảng 12.343 page, chừng 96 MB. Bản thử đo được dòng lá 22 và 32 byte, cây sâu 3 tầng ở mỗi partition, khớp ước lượng. Đo trên máy thật trước khi làm:

SELECT
    i.name,
    SUM(ps.used_page_count) AS page_dung,
    SUM(ps.used_page_count) * 8 / 1024 AS mb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i
    ON i.object_id = ps.object_id
   AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID(N'dbo.DonHang')
GROUP BY i.name;

Ba ràng buộc của lần dựng:

• Log: BanHang ở recovery model FULL, nên dựng chỉ mục được ghi log đầy đủ. Ước lượng log phát sinh xấp xỉ kích thước chỉ mục mới, vài trăm MB. Log 8 GB với log backup 15 phút một lần đủ chỗ. Xem used_log_space_in_percent trong sys.dm_db_log_space_usage trước khi chạy.
• tempdb: SORT_IN_TEMPDB = ON đưa phần sắp xếp sang tempdb, cần chỗ cỡ kích thước chỉ mục.
• Khóa: với ONLINE = ON, lệnh giữ khóa S rất ngắn lúc bắt đầu và khóa Sch-M rất ngắn lúc kết thúc, vì đây là dựng lại một chỉ mục không clustered. Sch-M phải chờ các lần gọi đang chạy, và lần gọi mới xếp sau nó. Chạy lúc lưu lượng thấp. Từ SQL Server 2022, CREATE INDEX nhận thêm WAIT_AT_LOW_PRIORITY để giới hạn lần chờ này. SQL Server 2019 chưa có.

Lệnh chạy lúc 22:00 thứ Ba 2026-10-06:

CREATE INDEX IX_DonHang_KhachHang
    ON dbo.DonHang (KhachHangId, NgayTao)
    INCLUDE (TrangThai, TongTien)
    WITH (DROP_EXISTING = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4)
    ON ps_DonHang_Ngay (NgayTao);

Chỉ mục phải nằm trên cùng partition scheme với bảng, nếu không SWITCH bị chặn:

SELECT i.name, ds.name AS noi_dat, ds.type_desc
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds
    ON ds.data_space_id = i.data_space_id
WHERE i.object_id = OBJECT_ID(N'dbo.DonHang');

Mọi dòng phải có type_desc = PARTITION_SCHEME và noi_dat = ps_DonHang_Ngay.

Căn chỉnh partition là chưa đủ. Bảng staging ở mục SWITCH của Kỹ thuật thường dùng đang tạo chỉ mục (KhachHangId, NgayTao) không có INCLUDE. Trên bản thử, SWITCH với staging đó thất bại với lỗi 4947: không có chỉ mục giống hệt trong bảng nguồn. Script staging phải đổi dòng tạo chỉ mục thành:

CREATE INDEX IX_DonHang_Staging_KhachHang
    ON dbo.DonHang_Staging (KhachHangId, NgayTao)
    INCLUDE (TrangThai, TongTien)
    ON FG_ARCHIVE;

Một chi tiết về thống kê. Khi tạo hoặc dựng lại chỉ mục có phân vùng, SQL Server tạo thống kê bằng lấy mẫu mặc định, không quét toàn bộ như với chỉ mục không phân vùng. Kế hoạch của thủ tục không còn phụ thuộc vào ước lượng cho khách 42. Các câu khác dùng histogram này thì có. Job Chủ nhật chạy FULLSCAN sẽ đưa thống kê về đầy đủ.

10. Kiểm chứng và gỡ ép #

22:09:38, lệnh dựng xong. Query Store ngay sau đó:

SELECT plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc,
       SWITCHOFFSET(initial_compile_start_time, '+07:00') AS bien_dich_luc
FROM sys.query_store_plan
WHERE query_id = 4187;

Plan 3907 có Key Lookup. Trên chỉ mục mới, cột cần lấy đã nằm sẵn trong chỉ mục, và engine không dựng lại được đúng hình dạng có lookup: NO_PLAN. Câu được biên dịch tự do thành plan 6034. Bản thử cho đúng kết quả này.

Phép thử quyết định: biên dịch với khách lớn nhất trước, rồi dùng lại cho khách thường. Lần này cố ý để SSMS ở mặc định ARITHABORT ON. SSMS có mục plan cache riêng, nên lần biên dịch đầu của mục đó chắc chắn là khách 42, không tranh với lượt gọi của ứng dụng.

EXEC sp_recompile N'dbo.usp_DonHang_CuaKhach';

SET STATISTICS IO, TIME ON;
EXEC dbo.usp_DonHang_CuaKhach @KhachHangId = 42;      -- biên dịch với khách 300.000 đơn
EXEC dbo.usp_DonHang_CuaKhach @KhachHangId = 7315;    -- dùng lại kế hoạch đó, khách 20 đơn
EXEC dbo.usp_DonHang_CuaKhach @KhachHangId = 18420;   -- khách 50 đơn
SET STATISTICS IO, TIME OFF;

Table 'DonHang'. Scan count 2, logical reads 3, physical reads 0, ...
Table 'DonHang'. Scan count 4, logical reads 9, physical reads 0, ...
Table 'DonHang'. Scan count 4, logical reads 10, physical reads 0, ...

Scan count là số partition được seek. Khách 42 chỉ cần partition 4 (trống) và partition 3. Khách thường cần cả bốn, mỗi partition 3 page từ gốc xuống lá.

Chạy lại theo thứ tự ngược, biên dịch với khách 7315 trước, cho cùng kế hoạch và cùng số reads.

Gỡ ép, vì plan 3907 không còn dựng lại được và không còn cần:

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

Một ngày sau, Query Store ghi plan 6034 trung bình 9 logical reads, CPU dưới 0,1 ms, p95 của API 61 ms lúc cao điểm. Thứ Hai 12/10, 06:02, job của đại lý 42 lại là người gọi đầu tiên sau bảo trì. last_compile_start_time của plan 6034 nhảy lên 06:02, không có plan_id mới: biên dịch cho khách 42 vẫn ra đúng kế hoạch đó.

11. Hậu kiểm #

Tác động: suy giảm từ 06:02 đến 09:07, khoảng 24.400 lần gọi lỗi (theo các dòng Aborted trong Query Store), 37 phút từ cảnh báo đến hồi phục.

Làm tốt:

• Query Store đã bật từ trước. Lịch sử kế hoạch và thời điểm đổi có sẵn, không phải dựng lại từ trí nhớ.
• Đo trước khi sửa. Wait và request loại trừ khóa và memory grant trong 3 phút.
• Đọc plan 3907 trước khi ép, để chắc khách 42 không biến thành 900.000 reads. Ép kế hoạch thay vì xóa plan cache: hoàn tác được, tác động một câu.
• Bản sửa gốc được thử trên bản sao, với cả hai thứ tự biên dịch, trước khi chạy trên production.

Làm chưa tốt:

• Hai giờ rưỡi suy giảm không ai biết. Cảnh báo dùng ngưỡng tuyệt đối 2 giây, trong khi p95 đã tăng 12 lần.
• Không có tín hiệu nào khi một câu nóng đổi kế hoạch.
• Thêm một khách hàng gọi API với khối dữ liệu gấp 15.000 lần trung vị không được xem là thay đổi có rủi ro.
• Lần đo đầu trong SSMS dùng sai ARITHABORT.

12. Giám sát thêm sau sự cố #

Ba truy vấn Query Store chạy bằng SQL Server Agent 15 phút một lần, gửi cảnh báo khi có dòng trả về.

Câu có CPU mỗi lần chạy trong giờ qua gấp hơn 10 lần trung bình 7 ngày trước đó:

DECLARE @bay_gio datetimeoffset = SYSDATETIMEOFFSET();

WITH so_lieu AS (
    SELECT
        p.query_id,
        CASE WHEN i.start_time >= DATEADD(HOUR, -1, @bay_gio) THEN 'gan' ELSE 'truoc' END AS ky,
        SUM(rs.avg_cpu_time * rs.count_executions) AS cpu_us,
        SUM(rs.count_executions) AS so_lan
    FROM sys.query_store_runtime_stats AS rs
    JOIN sys.query_store_runtime_stats_interval AS i
        ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE i.start_time >= DATEADD(DAY, -7, @bay_gio)
    GROUP BY p.query_id,
             CASE WHEN i.start_time >= DATEADD(HOUR, -1, @bay_gio) THEN 'gan' ELSE 'truoc' END
)
SELECT
    g.query_id,
    CAST(t.cpu_us / t.so_lan / 1000.0 AS decimal(12, 2)) AS cpu_ms_truoc,
    CAST(g.cpu_us / g.so_lan / 1000.0 AS decimal(12, 2)) AS cpu_ms_gan,
    g.so_lan AS so_lan_gan
FROM so_lieu AS g
JOIN so_lieu AS t
    ON t.query_id = g.query_id
   AND t.ky = 'truoc'
WHERE g.ky = 'gan'
  AND g.so_lan >= 100
  AND g.cpu_us / g.so_lan > 10 * (t.cpu_us / t.so_lan);

Câu đã có kế hoạch cũ vừa có thêm kế hoạch mới trong giờ qua. Nếu đã có từ trước, kiểm tra này sẽ báo ở lần chạy 06:15 sáng 05/10:

SELECT
    p.query_id,
    p.plan_id,
    SWITCHOFFSET(p.initial_compile_start_time, '+07:00') AS bien_dich_luc
FROM sys.query_store_plan AS p
WHERE p.initial_compile_start_time >= DATEADD(HOUR, -1, SYSDATETIMEOFFSET())
  AND EXISTS (
        SELECT 1
        FROM sys.query_store_plan AS cu
        WHERE cu.query_id = p.query_id
          AND cu.plan_id <> p.plan_id);

Kế hoạch đang ép nhưng ép thất bại:

SELECT query_id, plan_id, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE is_forced_plan = 1
  AND force_failure_count > 0;

Ngưỡng cho wait và APM:

Trên Enterprise từ SQL Server 2017, automatic plan correction tự ép lại kế hoạch tốt gần nhất khi phát hiện kế hoạch mới tệ hơn. Tùy chọn này mặc định tắt trên SQL Server và bật bằng ALTER DATABASE BanHang SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);. Tài liệu nói engine tự ép khi lợi ích CPU ước tính trên 10 giây. Ở đây mỗi lần gọi phí khoảng 0,7 s, nên ngưỡng đó đạt sau vài chục lần gọi. Thời điểm phát hiện chính xác do engine quyết định, tài liệu không nêu. Đây là lưới an toàn, không thay chỉ mục. Kế hoạch được ép tự động cũng gặp đúng giới hạn ở mục 10.

13. 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. Bước 2 và 3 ở trên phân biệt chúng nhanh. Mỗi nguyên nhân có một truy vấn xác nhận.

-- 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;

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: nhiều kế hoạch mới ngay sau giờ khởi động là dấu hiệu của cache trống.

14. Checklist: khi một API đột nhiên chậm #

1. Ghi số trước khi kết luận: endpoint nào, từ lúc nào, p50, p95, tỷ lệ lỗi, so với cùng giờ tuần trước.
2. Đọc loại lỗi: timeout (SqlException -2), hết connection pool, deadlock (1205), hay lỗi nghiệp vụ.
3. Xem CPU, I/O và bộ nhớ của máy SQL trong cùng khung giờ. Nếu tăng tuyến tính theo lưu lượng, một câu đang tốn cố định mỗi lần gọi.
4. Chụp sys.dm_exec_requests: câu nào, bao nhiêu request, status, wait_type, blocking_session_id, granted_query_memory.
5. Lấy chênh lệch sys.dm_os_wait_stats trong 60 giây. Đọc cả wait có mặt lẫn wait vắng mặt.
6. Mở Query Store cho câu đó: các kế hoạch, giờ biên dịch đổi sang giờ địa phương, số liệu theo giờ, các dòng Aborted.
7. Đặt giờ đổi kế hoạch cạnh lịch sự kiện: deploy, job bảo trì, nạp dữ liệu, failover, khách hàng hay job mới.
8. Đọc kế hoạch: ParameterCompiledValue so với giá trị đang chạy, ước lượng so với Number of Rows Read. Đo trong SSMS với SET ARITHABORT OFF.
9. Giảm nhẹ bằng biện pháp hoàn tác được và tác động hẹp: ép kế hoạch đã biết là tốt, sau khi đọc nó với giá trị lớn nhất. Không dùng DBCC FREEPROCCACHE.
10. Sửa gốc trên bản sao. Thử 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.
11. Triển khai, kiểm tra force_failure_count, gỡ ép, và theo dõi lần biên dịch kế tiếp sau đợt bảo trì sau.

15. Bài học #

• Một kế hoạch là lời giải cho một ước lượng. Khi một 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ị, không phải kế hoạch nào tốt cho giá trị đang gõ.
• Kế hoạch rẻ nhất cho khách lớn có thể là kế hoạch tệ nhất cho khách nhỏ. Row goal làm khoảng cách đó lớn hơn: scan dừng sớm với khách 42, đọc hết bảng với khách 7315.
• Cập nhật thống kê không làm kế hoạch tệ đi. Nó mở lại lần biên dịch, và ai gọi trước sau bảo trì trở thành một phần của thiết kế. Thêm một khách có dữ liệu lệch là thay đổi cần xét rủi ro, như một lần deploy.
• Ép kế hoạch tắt sự cố trong một phút nhưng có thể hết tác dụng mà không báo khi chỉ mục đổi. Chỉ mục khớp đúng câu hỏi làm kế hoạch hết nhạy với tham số, cho đúng câu đó.

Đọc tiếp #

Cơ chế histogram, mật độ, ngưỡng tự cập nhật thống kê và chi phí lookup nằm ở Chỉ mục, thống kê. Cách đọc toán tử, Parameter Compiled Value và Runtime Value, PSP và ép kế hoạch bằng Query Store nằm ở Đọc kế hoạch thực thi. Chẩn đoán chặn khóa nằm ở Transaction, khóa, isolation. Bật và cấu hình Query Store, cùng script SWITCH cần sửa staging, nằm ở Kỹ thuật thường dùng. Cấu trúc page và phép tính 218 dòng mỗi page nằm ở Kiến trúc lưu trữ.

Nguồn #

• sys.query_store_plan
• Automatic tuning
• 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
• CREATE INDEX: ONLINE, DROP_EXISTING, statistics on partitioned indexes
• KB 2965553: decreased performance with TOP, MAX, MIN on partitioned tables
• Editions and supported features of SQL Server 2019
• Craig Freedman: Partitioned indexes in SQL Server 2008
• Paul White: Parameter Sensitive Plan Optimization thresholds
• Erland Sommarskog: Slow in the Application, Fast in SSMS?