Toán tử trong kế hoạch thực thi và Key Lookup

Màn hình chăm sóc khách của BanHang mở nhanh với khách lẻ nhưng chậm hẳn với đại lý lớn, dù cùng câu SQL và cùng chỉ mục. Lý do nằm ở toán tử Key Lookup: khoảng 3 page cho mỗi dòng, rẻ với 22 dòng và đắt với 82.000 dòng. Đọc xong bạn biết mỗi toán tử hay gặp được chọn khi nào, tốn ở đâu, và viết truy vấn EF Core không kéo theo Key Lookup không cần thiết.

1. Bảng tra các toán tử #

Kế hoạch trong bài viết thành cây văn bản như ở bài trước: thụt vào là con, dòng thụt sâu nhất là toán tử ở tận cùng bên phải. Bảng dưới là các toán tử gặp hằng ngày trên BanHang.

Memory grant, spill và Parallelism được đọc kỹ ở Memory grant và tràn tempdb. Bài này đi qua phần còn lại.

2. Seek, scan và Key Lookup #

Seek không tự động rẻ. Clustered Index Seek theo NgayTao trên cả năm 2026 đọc 16.514 page: toàn bộ partition 3, chỉ khác scan ở tên gọi. Scan cũng không tự động đắt: scan PK_SanPham đọc khoảng 64 page cho 5.000 sản phẩm. Thứ cần đếm là số page đọc và số dòng trả ra.

Chỉ mục không clustered chứa cột khóa của nó cộng khóa clustered. Cột còn thiếu phải lấy bằng một lần seek vào PK_DonHang cho mỗi dòng, nên Key Lookup luôn đứng ở phía trong một Nested Loops. Mỗi lần lookup trên partition năm 2026 tốn khoảng 3 logical reads, vì cây cao 3 mức.

3. Ví dụ B: danh sách đơn của một khách và Key Lookup #

Màn hình chăm sóc khách hiện các đơn năm 2026 của khách 1507. Khách này có 60 đơn kể từ năm 2024, trong đó 22 đơn năm 2026.

SELECT DonHangId, NgayTao, TrangThai, TongTien
FROM dbo.DonHang
WHERE KhachHangId = 1507
  AND NgayTao >= CAST('20260101' AS datetime2(0))
  AND NgayTao < CAST('20270101' AS datetime2(0))
ORDER BY NgayTao DESC;

IX_DonHang_KhachHang (KhachHangId, NgayTao) có KhachHangId, NgayTao, và DonHangId vì khóa clustered đi kèm mọi chỉ mục không clustered. TrangThai và TongTien không có.

-- minh họa: kế hoạch thực tế, khách 1507
SELECT                                             Optimization Level FULL
  Nested Loops (Inner Join)                        est 30          act 22
    Index Seek IX_DonHang_KhachHang (backward)     est 30          act 22         Number of Executions 1
      Seek Keys: Prefix PtnId1000 = 3, KhachHangId = 1507, NgayTao >= '2026-01-01' AND < '2027-01-01'
    Key Lookup (Clustered) PK_DonHang
      Seek Predicate: NgayTao = [NgayTao] AND DonHangId = [DonHangId]
      Output List: TrangThai, TongTien
      Estimated Number of Rows Per Execution 1     Estimated Number of Executions 30
      Actual Number of Rows for All Executions 22  Number of Executions 22

SET STATISTICS IO của lần chạy này (minh họa): Table 'DonHang'. Scan count 1, logical reads 69. Đọc:

• Index Seek chạy 1 lần, trả 22 dòng. Ước lượng 30, đủ gần.
• Key Lookup chạy 22 lần, mỗi lần 1 dòng. Estimated Number of Rows Per Execution = 1 không mâu thuẫn với 22 dòng thực tế: một số là mỗi lần, số kia là tổng.
• Output List của Key Lookup chỉ có TrangThai và TongTien. Đó là hai cột chỉ mục thiếu.
• Reads: 3 page cho seek (cây partition 3 của IX_DonHang_KhachHang cao 3 mức), cộng 22 × 3 page cho lookup, bằng 69.

Với 22 dòng, Key Lookup rẻ và không cần sửa. Chi phí của nó là khoảng 3 page nhân Number of Executions:

So với đọc thẳng partition 3 của PK_DonHang (16.514 page), điểm hòa vốn về số page nằm quanh 5.500 dòng. Trình tối ưu dùng mô hình chi phí riêng, nên điểm nó đổi sang scan không trùng khít con số này, nhưng cùng bậc. Khi phiên bản có tham số của câu này chạy cho khách 42 bằng seek + lookup, xem Parameter Compiled Value trước: nhiều khả năng kế hoạch được biên dịch cho một khách nhỏ.

Cột cuối là khi IX_DonHang_KhachHang có thêm TrangThai, TongTien trong INCLUDE: một dòng lá rộng khoảng 32 byte, 238 dòng mỗi page theo phép tính ở Chỉ mục phủ, không còn Key Lookup. Khách 42 có khoảng 300 đơn mỗi ngày, nên năm 2026 có khoảng 300.000 × 275 / 1.005 ≈ 82.000 đơn: 82.000 / 238 ≈ 345 page lá cộng vài page trên lá.

Chiều ngược lại còn đắt hơn: kế hoạch biên dịch cho khách 42 (scan ngược PK_DonHang dừng sớm nhờ TOP) được dùng lại cho mọi khách nhỏ và đọc gần hết bảng mỗi lần gọi. Sự cố đó được điều tra đầy đủ ở Điều tra truy vấn chậm; cơ chế dùng lại kế hoạch nằm ở Parameter sniffing và Query Store.

4. Ba kiểu nối và Adaptive Join #

• Nested Loops: lấy từng dòng phía ngoài, tìm dòng khớp ở phía trong. Rẻ khi phía ngoài ít dòng và phía trong có chỉ mục. Ước lượng phía ngoài thấp hơn thực tế nhiều lần là cách kế hoạch tệ nhất hay xuất hiện: phía trong chạy hàng trăm nghìn lần.
• Merge Join: đọc song song hai đầu vào đã sắp theo cột nối. DonHang và ChiTietDonHang cùng khóa clustered bắt đầu bằng (NgayTao, DonHangId), nên nối hai bảng trên một khoảng ngày có thể dùng Merge Join mà không cần Sort. Nếu phải chèn Sort để có thứ tự, chi phí nằm ở Sort.
• Hash Match: dựng bảng băm từ đầu vào nhỏ hơn (build), rồi dò từng dòng của đầu vào kia (probe). Không cần thứ tự, không cần chỉ mục, nhưng cần memory grant. Build vượt grant thì tràn xuống tempdb.

Adaptive Join dựng phía build như hash join, đếm số dòng thực tế, rồi chọn chạy tiếp hash join hay chuyển sang nested loops theo ngưỡng Adaptive Threshold Rows có sẵn trong kế hoạch. Kế hoạch thực tế cho biết Estimated Join Type và Actual Join Type. Có từ SQL Server 2017 (compatibility level 140) khi câu có columnstore; từ SQL Server 2019 (compatibility level 150) còn qua batch mode on rowstore. Chỉ áp cho SELECT.

flowchart LR
  B["Dựng phía build, đếm dòng thực tế"] --> Q{"Vượt ngưỡng?"}
  Q -- "có" --> H["Chạy tiếp Hash Match"]
  Q -- "không" --> N["Chuyển sang Nested Loops, seek phía trong cho từng dòng build"]

5. Sort, tổng hợp và các toán tử còn lại #

Sort là blocking và cần memory grant theo số dòng nhân độ rộng dòng. Top N Sort phục vụ TOP (n) ... ORDER BY và chỉ giữ n dòng tốt nhất, nên với n nhỏ nó cần ít bộ nhớ hơn Sort đầy đủ. Một Sort lớn ngay dưới Merge Join hay Stream Aggregate là dấu hiệu chỉ mục không cho sẵn thứ tự kế hoạch cần.

Stream Aggregate nhận dòng đã sắp theo cột nhóm và trả mỗi nhóm khi gặp giá trị khóa mới: streaming, không cần grant. Hash Aggregate giữ một bảng băm với mỗi nhóm một mục, nên grant tính theo số nhóm ước lượng.

Compute Scalar định nghĩa biểu thức. Giá trị thường được tính muộn hơn, ở toán tử đầu tiên cần đến nó, nên chi phí hiển thị gần 0 không chứng minh biểu thức rẻ; hàm vô hướng do người dùng viết là trường hợp điển hình. Filter đứng riêng nghĩa là điều kiện không đẩy xuống được seek hay scan: điều kiện trên kết quả tổng hợp (HAVING), trên biểu thức tính sau, hoặc startup expression chỉ quyết định có chạy nhánh con hay không.

Spool ghi một tập dòng vào worktable trong tempdb để đọc lại; SET STATISTICS IO hiện phần này dưới tên Worktable. Eager Index Spool trên bảng lớn gần như luôn là trình tối ưu tự dựng chỉ mục mà bảng đang thiếu, và dựng lại ở mỗi lần câu chạy.

6. Columnstore và batch mode #

Batch mode xử lý từng lô dòng thay vì từng dòng, và giảm CPU cho scan, hash join, hash aggregate, sort. Kế hoạch ghi Actual Execution Mode = Batch trên từng toán tử. Columnstore Index Scan có Storage = ColumnStore; SET STATISTICS IO báo page columnstore ở cột lob logical reads và thêm dòng Segment reads, segment skipped, với segment skipped là rowgroup bị loại nhờ giá trị nhỏ nhất và lớn nhất của cột.

Nếu đã tạo NCCI_DonHang ở Kỹ thuật thường dùng, trình tối ưu xét batch mode cho câu doanh thu tháng của bài trước, kể cả trên SQL Server 2017 hay bản Standard: chỉ cần một bảng trong câu có columnstore. Trên SQL Server 2019 Enterprise, compatibility level 150, batch mode on rowstore cho cùng hiệu ứng mà không cần columnstore.

Trong kế hoạch minh họa, logical reads giữ nguyên khoảng 8.000 page, còn CPU giảm, ví dụ từ 641 ms xuống khoảng 230 ms (minh họa). Adaptive Join có ba con: phía build, nhánh probe của hash, và nhánh seek dành cho nested loops; 373.100 dòng vượt ngưỡng 2.150, nên nhánh nested loops chạy 0 lần.

Câu này nối theo DonHangId, cột mà chương 2 không liệt kê trong NCCI_DonHang. Đừng giả định kế hoạch sẽ đọc DonHang từ columnstore: mở toán tử lá và xem tên chỉ mục. Câu doanh thu theo ngày của chương 2, đổi khoảng thành tháng 9, chỉ cần NgayTao và TongTien, nên đọc thẳng columnstore.

-- minh họa: Enterprise, compatibility level 150, có NCCI_DonHang
SELECT                                          DOP 4
  Parallelism (Gather Streams)
    Hash Match (Inner Join)                     Actual Execution Mode Batch
      Clustered Index Scan PK_SanPham
      Hash Match (Aggregate)                    Actual Execution Mode Batch     act 4.986
        Adaptive Join                           Actual Execution Mode Batch
                                                Estimated Join Type Hash Match   Actual Join Type Hash Match
                                                Adaptive Threshold Rows 2.150
          Clustered Index Seek PK_DonHang                                       act 373.100
          Clustered Index Seek PK_ChiTietDonHang    (nhánh probe của hash)      act 1.178.100
          Clustered Index Seek PK_ChiTietDonHang    (nhánh nested loops)        Number of Executions 0

-- minh họa: SUM(TongTien) GROUP BY CAST(NgayTao AS date), tháng 9/2026, có NCCI_DonHang
Hash Match (Aggregate)                          Actual Execution Mode Batch     act 30
  Compute Scalar                                CAST(NgayTao AS date)
    Columnstore Index Scan NCCI_DonHang         Storage ColumnStore   Actual Execution Mode Batch
                                                Actual Partition Count 1   Actual Partitions Accessed 3

Table 'DonHang'. Scan count 4, logical reads 0, ..., lob logical reads 412, ...
Table 'DonHang'. Segment reads 2, segment skipped 2.

Partition 3 có khoảng 3,6 triệu dòng, mỗi rowgroup chứa tối đa 1.048.576 dòng, nên có ít nhất 4 rowgroup. Trong lần chạy minh họa có đúng 4, và hai rowgroup bị bỏ nhờ khoảng NgayTao. Số rowgroup bị bỏ tùy dữ liệu được nạp có theo thứ tự ngày hay không: rowgroup trộn nhiều tháng thì không loại được.

7. Áp dụng trong .NET: projection trong EF Core và Key Lookup #

Màn hình chăm sóc khách viết bằng EF Core thường lấy cả entity DonHang, nên EF Core sinh SELECT mọi cột đã map, kể cả TrangThai và TongTien, và kế hoạch cần Key Lookup cho mỗi đơn. Chỉ lấy cột có sẵn trong IX_DonHang_KhachHang thì chỉ mục tự phủ câu.

// Cả entity: SELECT mọi cột; chỉ mục thiếu TrangThai, TongTien nên mỗi dòng một Key Lookup
var don = await DonNam2026(db, khach)
    .TagWith("don-cua-khach: ca entity").ToListAsync();

// Projection: chỉ cột có sẵn trong IX_DonHang_KhachHang, không còn Key Lookup
var dong = await DonNam2026(db, khach)
    .TagWith("don-cua-khach: projection")
    .Select(d => new { d.DonHangId, d.NgayTao })
    .ToListAsync();

static IQueryable<DonHang> DonNam2026(BanHangDb db, int khach) =>
    db.DonHang.AsNoTracking()
        .Where(d => d.KhachHangId == khach
                 && d.NgayTao >= new DateTime(2026, 1, 1) && d.NgayTao < new DateTime(2027, 1, 1))
        .OrderByDescending(d => d.NgayTao);

EF Core 10.0.12 không chuyển message STATISTICS IO tới InfoMessage (bài trước). Chương trình đo bằng cách khác: TagWith đặt dòng -- don-cua-khach: ... ở đầu batch, nên tìm được câu trong sys.dm_exec_sql_text và đọc last_logical_reads của nó từ sys.dm_exec_query_stats. Chạy bằng dotnet run KeyLookup.cs (file có #:property PublishAot=false vì EF Core sinh code lúc chạy) trên .NET 10.0.12, LocalDB SQL Server 2019, database thử Kumeo_kehoach, bản thu nhỏ 1/10 của BanHang nên khách 1507 chỉ có 8 đơn năm 2026 (script ở Kế hoạch thực thi và plan cache):

Projection giảm reads ở cả hai khách vì không còn Key Lookup. Câu cả entity của khách 42 còn dùng lại kế hoạch seek + lookup biên dịch cho khách 1507, tốn gấp khoảng 38 lần kế hoạch biên dịch cho chính khách 42; số này dao động khoảng 1–2% giữa các lần chạy. Đó là parameter sniffing, chuyện của bài cuối nhóm. Nếu màn hình cần TrangThai và TongTien, thêm hai cột vào INCLUDE của chỉ mục.

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

using Microsoft.EntityFrameworkCore;

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

await using var db = new BanHangDb(new DbContextOptionsBuilder<BanHangDb>().UseSqlServer(Cs).Options);
await db.Database.OpenConnectionAsync();
await db.Database.ExecuteSqlRawAsync("ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;");
Console.WriteLine($".NET {Environment.Version}, SQL Server {db.Database.GetDbConnection().ServerVersion} (LocalDB)");

foreach (var khach in new[] { 1507, 42 })
{
    // Cả entity: cần TrangThai, TongTien, hai cột IX_DonHang_KhachHang không có
    var soDong = (await DonNam2026(db, khach).TagWith("don-cua-khach: ca entity").ToListAsync()).Count;
    var caEntity = await LanCuoi(db, "don-cua-khach: ca entity");
    // Chỉ lấy cột chỉ mục đã có: KhachHangId, NgayTao và khóa clustered DonHangId
    await DonNam2026(db, khach).TagWith("don-cua-khach: projection")
        .Select(d => new { d.DonHangId, d.NgayTao }).ToListAsync();
    var projection = await LanCuoi(db, "don-cua-khach: projection");
    Console.WriteLine($"Khách {khach,-5} {soDong,6} dòng   cả entity: {caEntity,6} logical reads   projection: {projection,3} logical reads");
}

// Kế hoạch trong cache của hai câu: có Key Lookup không, biên dịch cho khách nào
var keHoach = await db.Database.SqlQueryRaw<KeHoach>("""
    SELECT CASE WHEN st.text LIKE N'%-- don-cua-khach: ca entity%' THEN N'cả entity' ELSE N'projection' END AS Cau,
           CAST(CASE WHEN CAST(qp.query_plan AS nvarchar(max)) LIKE N'%Lookup="1"%' THEN 1 ELSE 0 END AS bit) AS CoKeyLookup
    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'%-- don-cua-khach%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
    """).ToListAsync();
foreach (var k in keHoach)
    Console.WriteLine($"Kế hoạch {k.Cau,-11} Key Lookup: {k.CoKeyLookup}");

// Biên dịch lại cho khách 42: trình tối ưu bỏ Key Lookup, đọc thẳng partition 2026 của PK_DonHang
await db.Database.ExecuteSqlRawAsync("ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;");
await DonNam2026(db, 42).TagWith("don-cua-khach: ca entity").ToListAsync();
Console.WriteLine($"Khách 42 cả entity, kế hoạch biên dịch cho 42: {await LanCuoi(db, "don-cua-khach: ca entity")} logical reads");

Console.WriteLine();
Console.WriteLine(DonNam2026(db, 1507).TagWith("don-cua-khach: ca entity").ToQueryString());

static IQueryable<DonHang> DonNam2026(BanHangDb db, int khach) =>
    db.DonHang.AsNoTracking()
        .Where(d => d.KhachHangId == khach && d.NgayTao >= new DateTime(2026, 1, 1) && d.NgayTao < new DateTime(2027, 1, 1))
        .OrderByDescending(d => d.NgayTao);

// Logical reads của lần chạy gần nhất của câu mang tag, đọc từ sys.dm_exec_query_stats
static async Task<long> LanCuoi(BanHangDb db, string tag) =>
    await db.Database.SqlQueryRaw<long>("""
        SELECT qs.last_logical_reads 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'%-- ' + {0} + N'%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
        """, tag).SingleAsync();

record KeHoach(string Cau, bool CoKeyLookup);

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.NgayTao).HasPrecision(0);
        e.Property(x => x.TongTien).HasPrecision(18, 2);
    });
}

.NET 10.0.12, SQL Server 15.00.4382 (LocalDB)
Khách 1507       8 dòng   cả entity:     57 logical reads   projection:   9 logical reads
Khách 42     10800 dòng   cả entity:  63255 logical reads   projection:  42 logical reads
Kế hoạch cả entity   Key Lookup: True
Kế hoạch projection  Key Lookup: False
Khách 42 cả entity, kế hoạch biên dịch cho 42: 1667 logical reads

DECLARE @khach int = 1507;

-- don-cua-khach: ca entity

SELECT [d].[NgayTao], [d].[DonHangId], [d].[KhachHangId], [d].[TongTien], [d].[TrangThai]
FROM [DonHang] AS [d]
WHERE [d].[KhachHangId] = @khach AND [d].[NgayTao] >= '2026-01-01T00:00:00' AND [d].[NgayTao] < '2027-01-01T00:00:00'
ORDER BY [d].[NgayTao] DESC

Những chỗ hay hiểu sai #

• "Index Seek tốt, Index Scan xấu." Seek trên cả năm đọc 16.514 page; scan SanPham đọc 64 page. Đếm page và dòng, không đếm tên toán tử.
• "Phía trong Nested Loops ước lượng 1 dòng mà thực tế 22 dòng là ước lượng sai." Một số là mỗi lần chạy, số kia là tổng của 22 lần chạy.
• "Key Lookup lúc nào cũng phải xóa." Key Lookup chạy 22 lần tốn 66 page. Nó thành vấn đề khi Number of Executions lên hàng chục nghìn.
• "EF Core chỉ đọc những cột code dùng tới." Truy vấn trả entity sinh SELECT mọi cột đã map; chỉ Select sang kiểu nhỏ hơn mới bớt cột.

Kết luận #

Đọc toán tử bằng số page và Number of Executions, không bằng tên. Key Lookup rẻ với vài chục dòng và đắt với hàng chục nghìn dòng; cách bỏ nó là cho chỉ mục phủ câu, bằng INCLUDE hoặc bằng cách chỉ lấy cột chỉ mục đã có.

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

• Với màn hình danh sách, dùng Select sang DTO hoặc kiểu ẩn danh thay vì trả cả entity; kiểm SQL sinh ra bằng ToQueryString().
• Gắn TagWith("tên-màn-hình") cho truy vấn quan trọng, để tìm nó trong sys.dm_exec_query_stats và đọc last_logical_reads, execution_count.
• Khi màn hình cần thêm cột, so chi phí INCLUDE cột đó với số Key Lookup mỗi lần gọi, đo trên khách lớn nhất chứ không phải khách mẫu.
• Trên kế hoạch của truy vấn EF Core chậm, mở Key Lookup và xem Output List: đó là danh sách cột cần INCLUDE hoặc cần bỏ khỏi projection.

Đọc tiếp #

• Bài trước: Đọc kế hoạch thực thi: tìm chỗ ước lượng lệch.
• Bài sau: Memory grant và tràn tempdb, bộ nhớ của Sort và Hash Match, cảnh báo, song song.
• Chọn cột cho INCLUDE: Chỉ mục phủ, thứ tự cột khóa và điều kiện seek được.
• Cái giá khi ghi của mỗi chỉ mục: Chỉ mục lọc, cái giá khi ghi và chỉ mục thừa.
• Một sự cố parameter sniffing với khách 42, từ lúc phát hiện tới ép kế hoạch và sửa bằng chỉ mục phủ: Điều tra truy vấn chậm.
• Columnstore NCCI_DonHang và chỉ mục lọc dùng trong nhóm bài: Kỹ thuật thường dùng.

Nguồn #

• Joins, gồm Adaptive Joins
• Intelligent query processing details
• Editions and supported features of SQL Server 2019
• Editions and supported features of SQL Server 2022
• sys.dm_exec_query_stats
• EF Core: Efficient querying, project only properties you need
• EF Core: Query tags