Đọc kế hoạch thực thi

Kế hoạch thực thi (execution plan) là cây toán tử mà engine chạy để trả kết quả cho một câu SQL. Chương này đi từ lúc câu lệnh được biên dịch, qua các cách lấy kế hoạch, tới thứ tự đọc thuộc tính và các toán tử hay gặp trên database BanHang. Sau đó là hai ví dụ có số liệu, cơ chế parameter sniffing, cách so kế hoạch theo thời gian trong Query Store, và một danh sách kiểm tra năm phút.

Mốc là SQL Server 2019. Tính năng của SQL Server 2022 và giới hạn theo edition được ghi tại chỗ. Bảng phân vùng DonHang lấy từ Kiến trúc lưu trữ. SanPham và ChiTietDonHang (khóa (NgayTao, DonHangId, DongSo), cùng partition scheme với DonHang) được giải thích ở Kiểu dữ liệu, collation và khóa chính. Query Store, chỉ mục lọc IX_DonHang_DangMo và columnstore NCCI_DonHang lấy từ Kỹ thuật thường dùng. Cách thiết kế chỉ mục và đọc histogram nằm ở Chỉ mục và thống kê; bài này dùng kết quả của chương đó, không dạy lại.

1. Từ văn bản đến kết quả #

Một báo cáo chạy nhanh buổi sáng, chậm buổi chiều, dù câu SQL không đổi một ký tự. Một ứng dụng ghép chuỗi WHERE KhachHangId = 1507 làm plan cache đầy kế hoạch chỉ dùng một lần. Hai chuyện này cùng một gốc: kế hoạch được biên dịch một lần, cất vào cache, rồi được dùng lại.

Các bước #

1. Tra plan cache. Câu ad hoc được so theo văn bản, từng ký tự, kể cả khoảng trắng. Thủ tục được tìm theo đối tượng. Các SET option của phiên phải khớp với lúc biên dịch. Có kế hoạch còn hợp lệ thì chuyển thẳng sang bước 5.
2. Parse. Kiểm tra cú pháp và dựng cây cú pháp. Sai cú pháp thì dừng ở đây.
3. Bind (algebrize). Gắn tên với bảng, cột thật trong metadata, suy kiểu dữ liệu của từng biểu thức, chèn CONVERT_IMPLICIT khi hai vế so sánh khác kiểu, kiểm tra GROUP BY. Kết quả là cây truy vấn logic.
4. Optimize. Trình tối ưu đơn giản hóa cây, rồi xem có đúng một cách chạy hợp lý không. Có thì dừng ở trivial plan. Không thì tối ưu theo chi phí: dùng thống kê để ước lượng số dòng qua từng bước, gán chi phí cho từng phương án (thứ tự nối, kiểu nối, chỉ mục, song song hay không), giữ phương án rẻ nhất tìm được. Nó dừng khi thấy kế hoạch đủ tốt hoặc hết ngân sách tìm kiếm. Nó không duyệt hết mọi phương án.
5. Execute. Kế hoạch được cất vào cache, trừ các trường hợp không cache. Engine tạo execution context chứa giá trị tham số của lần chạy này, rồi chạy cây toán tử.

flowchart TB
  T["Văn bản câu lệnh hoặc batch"] --> L{"Có kế hoạch hợp lệ trong plan cache?"}
  L -- "có" --> X["Thực thi: cây toán tử, execution context"]
  L -- "không, hoặc phải biên dịch lại" --> P["Parse: cây cú pháp"]
  P --> B["Bind / algebrize: tên, kiểu, chuyển kiểu ngầm"]
  B --> S["Đơn giản hóa cây truy vấn"]
  S --> V{"Chỉ có một cách chạy hợp lý?"}
  V -- "có: trivial plan" --> C["Cất kế hoạch vào plan cache"]
  V -- "không" --> O["Tối ưu theo chi phí: thống kê, ước lượng số dòng, so chi phí"]
  O --> C
  C --> X

Kế hoạch đã cache bị đánh dấu hết hiệu lực khi schema của bảng đổi, khi thống kê mà nó dựa vào được cập nhật, khi gọi sp_recompile, hoặc khi thủ tục chạy WITH RECOMPILE. Lần chạy kế tiếp biên dịch lại.

Trivial plan và tối ưu đầy đủ #

Với SELECT Ten, DonGia FROM dbo.SanPham WHERE SanPhamId = 42, seek trên PK_SanPham là cách chạy hợp lý duy nhất. Nút gốc SELECT của kế hoạch có Optimization Level = TRIVIAL (trong XML là StatementOptmLevel="TRIVIAL"), và câu dạng này thường được tham số hóa đơn giản: văn bản trong kế hoạch đổi 42 thành @1. Query Store ghi cùng thông tin ở cột is_trivial_plan của sys.query_store_plan.

Câu nối nhiều bảng ở ví dụ A có Optimization Level = FULL, kèm Reason For Early Termination Of Statement Optimization. Giá trị GoodEnoughPlanFound nghĩa là trình tối ưu thấy đủ tốt và dừng. TimeOut nghĩa là hết ngân sách: kế hoạch là phương án tốt nhất trong phần đã xét, không phải tốt nhất có thể. Câu nối nhiều bảng dễ gặp TimeOut; tách câu thành vài bước, ví dụ qua bảng tạm, cho trình tối ưu những bài toán nhỏ hơn. MemoryLimitExceeded hiếm gặp và cũng là dừng sớm.

Cái gì được cache và dùng lại #

Không được cache: câu có OPTION (RECOMPILE), thủ tục chạy WITH RECOMPILE, lệnh bulk trên rowstore, câu chứa chuỗi hằng dài hơn 8 KB. SET option khác nhau cho ra hai mục cache khác nhau cho cùng một văn bản. ARITHABORT là ví dụ hay gặp: SSMS bật mặc định, nhiều driver ứng dụng thì không. Câu chạy trong SSMS vì vậy dùng một kế hoạch khác với kế hoạch ứng dụng đang dùng.

So hai câu ad hoc chỉ khác hằng số với cùng câu đó gửi qua sp_executesql:

USE BanHang;
GO

SELECT DonHangId, NgayTao, TongTien FROM dbo.DonHang
WHERE KhachHangId = 1507 AND NgayTao >= CAST('20260101' AS datetime2(0));
GO
SELECT DonHangId, NgayTao, TongTien FROM dbo.DonHang
WHERE KhachHangId = 1508 AND NgayTao >= CAST('20260101' AS datetime2(0));
GO

DECLARE @sql nvarchar(400) = N'SELECT DonHangId, NgayTao, TongTien FROM dbo.DonHang
WHERE KhachHangId = @KhachHangId AND NgayTao >= @TuNgay;';
DECLARE @params nvarchar(100) = N'@KhachHangId int, @TuNgay datetime2(0)';
EXEC sys.sp_executesql @sql, @params, @KhachHangId = 1507, @TuNgay = '20260101';
EXEC sys.sp_executesql @sql, @params, @KhachHangId = 1508, @TuNgay = '20260101';
GO

SELECT cp.objtype, cp.usecounts, LEFT(st.text, 70) AS cau_lenh
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%FROM dbo.DonHang%KhachHangId%'
  AND st.text NOT LIKE N'%sp_executesql%'          -- bỏ batch gọi sp_executesql
  AND st.text NOT LIKE N'%dm_exec_cached_plans%'   -- bỏ chính câu này
ORDER BY cp.objtype, cau_lenh;

Kết quả đúng hình dạng sau. Engine chỉ tham số hóa đơn giản khi kế hoạch không đổi theo giá trị hằng. Ở đây chọn IX_DonHang_KhachHang hay PK_DonHang phụ thuộc khách là ai, nên mỗi khách thêm một mục cache. Câu qua sp_executesql dùng lại một kế hoạch, văn bản trong cache có phần khai báo tham số ở đầu.

Tham số phải khai báo cùng kiểu và cùng độ dài ở mọi lần gọi. Driver gửi nvarchar(4) lần này, nvarchar(5) lần sau thì cache có hai kế hoạch cho một câu.

optimize for ad hoc workloads #

Khi plan cache đầy kế hoạch dùng một lần, tùy chọn này cho lần biên dịch đầu chỉ cất một compiled plan stub nhỏ. Lần thứ hai gặp đúng văn bản đó, engine biên dịch lại và mới cất kế hoạch đầy đủ.

-- Bao nhiêu MB đang dành cho kế hoạch ad hoc chỉ chạy một lần
SELECT objtype, cacheobjtype,
       COUNT(*) AS so_ke_hoach,
       SUM(CAST(size_in_bytes AS bigint)) / 1024 / 1024 AS mb
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;
GO

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;

Tùy chọn có hiệu lực ngay, không cần khởi động lại, và chỉ áp cho kế hoạch mới. Stub hiện trong sys.dm_exec_cached_plans với cacheobjtype = 'Compiled Plan Stub' và không có kế hoạch để xem. Cái giá: câu chạy một lần không còn kế hoạch trong cache để điều tra. Cách sửa gốc vẫn là tham số hóa ở ứng dụng.

2. Kế hoạch ước lượng và kế hoạch thực tế #

Kế hoạch ước lượng (estimated plan) là thứ trình tối ưu sinh ra: hình dạng cây, số dòng ước lượng, chi phí ước lượng. Lấy nó không chạy câu lệnh. Kế hoạch thực tế (actual plan) là cùng kế hoạch đó cộng số liệu của một lần chạy: số dòng thực tế, số lần mỗi toán tử chạy, reads, thời gian, bộ nhớ đã cấp và đã dùng, cảnh báo lúc chạy. Nó chỉ có sau khi câu chạy xong.

Plan cache và Query Store chỉ giữ kế hoạch ước lượng. Query Store giữ thêm số liệu tổng theo khoảng thời gian, không giữ số dòng của từng toán tử.

Ctrl+L trong SSMS cho kế hoạch theo SET option của phiên SSMS và giá trị bạn gõ. Kế hoạch ứng dụng đang dùng có thể khác. Muốn biết ứng dụng đang chạy kế hoạch nào, đọc từ cache hoặc Query Store.

-- Kế hoạch ước lượng. SET SHOWPLAN_XML phải đứng một mình trong batch.
SET SHOWPLAN_XML ON;
GO
SELECT Ten, DonGia FROM dbo.SanPham WHERE SanPhamId = 42;
GO
SET SHOWPLAN_XML OFF;
GO

-- Kế hoạch thực tế, kèm reads và thời gian ở tab Messages
SET STATISTICS XML ON;
SET STATISTICS IO, TIME ON;
SELECT Ten, DonGia FROM dbo.SanPham WHERE SanPhamId = 42;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
GO

Câu tốn nhiều reads nhất đang nằm trong cache, kèm văn bản đúng câu lệnh trong batch và kế hoạch:

SELECT TOP (20)
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS reads_moi_lan,
    qs.total_worker_time / qs.execution_count / 1000.0 AS cpu_ms_moi_lan,
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS ms_moi_lan,
    qs.last_grant_kb,
    qs.last_used_grant_kb,
    qs.last_spills,
    SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
             WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1) AS cau_lenh,
    qp.query_plan
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 qp.dbid = DB_ID(N'BanHang')
ORDER BY qs.total_logical_reads DESC;

Các cột thời gian tính bằng micro giây. sys.dm_exec_query_plan trả kế hoạch của cả batch và trả NULL khi XML lồng từ 128 cấp trở lên. Khi đó dùng sys.dm_exec_text_query_plan với statement_start_offset và statement_end_offset để lấy riêng một câu dưới dạng văn bản.

Kế hoạch thực tế của lần chạy gần nhất, từ SQL Server 2019. Tính năng mặc định tắt và bật theo database:

USE BanHang;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
GO

SELECT cp.objtype, cp.usecounts, LEFT(st.text, 100) AS cau_lenh, qps.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(cp.plan_handle) AS qps
WHERE st.dbid = DB_ID(N'BanHang')
  AND st.text LIKE N'%KhachHangId = 1507%'
  AND st.text NOT LIKE N'%dm_exec_query_plan_stats%';

Kết quả có số dòng thực tế từng toán tử, tổng CPU và thời gian, DOP thực, bộ nhớ đã cấp và đã dùng, cảnh báo spill. Với câu OLTP đơn giản, hàm chỉ trả nút gốc SELECT. Kế hoạch đã bị đẩy khỏi cache thì không còn gì để trả.

Câu đang chạy dở, như một báo cáo đã chạy mười phút, lấy bằng CROSS APPLY sys.dm_exec_query_statistics_xml(r.session_id) từ sys.dm_exec_requests. Hàm này dựa trên lightweight profiling: bật sẵn từ SQL Server 2019; trên 2016 SP1 và 2017 cần trace flag 7412 hoặc một phiên Extended Events phù hợp.

3. Đọc theo chiều nào #

Kế hoạch dạng đồ họa có nút gốc SELECT ở bên trái, các toán tử đọc bảng ở tận cùng bên phải. Dòng dữ liệu đi từ phải sang trái theo mũi tên.

Việc điều khiển đi theo chiều ngược lại. Mỗi toán tử là một iterator có ba thao tác: khởi tạo (Init), lấy dòng kế tiếp (GetNext), đóng (Close). Nút gốc gọi GetNext trên con của nó, con gọi xuống con của mình, cho tới toán tử đọc page. Dòng được kéo từ bên trái và chảy về bên trái. Hai cách nói không mâu thuẫn: lời gọi đi sang phải, dữ liệu đi sang trái.

Mô hình kéo giải thích hai điều hay gặp:

• Toán tử streaming (Nested Loops, Compute Scalar, Filter, Stream Aggregate, seek, scan) trả dòng ngay khi có. TOP (20) phía trên một chuỗi toàn streaming dừng việc đọc sau dòng thứ 20.
• Toán tử blocking (Sort, phía build của Hash Match, Hash Aggregate) phải nhận hết đầu vào rồi mới trả dòng đầu tiên. TOP (20) phía trên một Sort không cứu được việc Sort đọc hết đầu vào.

Kế hoạch trong bài được viết thành cây văn bản. Thụt vào một mức là con của dòng phía trên. Với toán tử có hai đầu vào, con viết trước là đầu vào nằm trên trong bản đồ họa: phía ngoài (outer) của Nested Loops, phía build của Hash Match. Dòng thụt sâu nhất là toán tử ở tận cùng bên phải.

-- minh họa: cách đọc cây trong bài
SELECT
  Nested Loops (Inner Join)                     <- nhận dòng từ hai con
    Index Seek IX_DonHang_KhachHang             <- phía ngoài: chạy 1 lần, trả n dòng
    Key Lookup (Clustered) PK_DonHang           <- phía trong: chạy n lần, mỗi lần 1 dòng

Độ dày mũi tên trong SSMS theo số dòng. Kế hoạch thực tế dùng số dòng thực tế của mọi lần chạy cộng lại. Kế hoạch ước lượng dùng số dòng ước lượng cho một lần chạy. Mũi tên ra khỏi Key Lookup vì vậy mảnh trong kế hoạch ước lượng và dày trong kế hoạch thực tế, dù kế hoạch không đổi. Độ dày cũng không phải số byte: 1 triệu dòng hai cột nhẹ hơn 100.000 dòng có cột nvarchar(max).

4. Thuộc tính cần xem, theo thứ tự #

Bấm vào một toán tử rồi mở cửa sổ Properties (F4) cho đủ thuộc tính; tooltip chỉ hiện một phần. Thứ tự dưới đây đi từ thứ hay chỉ ra nguyên nhân nhất.

4.1 Số dòng ước lượng và thực tế #

Từ SSMS 18.5, tên thuộc tính ghi rõ phạm vi:

Toán tử ở phía trong Nested Loops chạy nhiều lần. So Actual Number of Rows for All Executions với Estimated Number of Rows Per Execution là so hai thứ khác nhau. Phải so actual với estimated for all executions, hoặc chia actual cho Number of Executions.

Cách đọc: đi từ phải sang trái, tìm toán tử đầu tiên mà thực tế lệch ước lượng từ khoảng 10 lần trở lên. Mọi toán tử bên trái nó thừa hưởng sai số đó, nên chỗ lệch đầu tiên mới là nơi tìm nguyên nhân: thống kê cũ, hàm bọc cột, chuyển kiểu ngầm, biến cục bộ, hai điều kiện tương quan.

Number of Rows Read lớn hơn nhiều so với số dòng trả ra nghĩa là toán tử đọc rồi bỏ. Clustered Index Scan đọc 10.000.000 dòng để trả 373.100 dòng là dấu hiệu điều kiện không dùng được để seek.

4.2 Seek Predicate và Predicate #

Seek Predicate là phần điều kiện dùng để đi xuống cây B-tree và xác định khoảng cần đọc. Predicate là phần điều kiện kiểm tra trên từng dòng đã đọc (residual predicate). Một Index Seek có Seek Predicate là KhachHangId = 1507 và Predicate là TongTien > 1000000 đọc mọi đơn của khách 1507 rồi mới lọc theo tiền.

Seek trên bảng phân vùng có thêm khóa ẩn PtnId1000 đứng đầu Seek Predicate. Giá trị của nó cho biết partition nào được đọc.

4.3 Output List #

Cột mà toán tử trả lên trên. Output List của Key Lookup là các cột chỉ mục không clustered thiếu. Output List rộng ở phía dưới một Sort hay Hash Match làm memory grant lớn theo, vì bộ nhớ được tính theo số dòng nhân độ rộng dòng.

4.4 Logical reads #

SET STATISTICS IO ON in số page 8 KB đọc từ buffer pool theo từng bảng. Kế hoạch thực tế có ActualLogicalReads theo từng toán tử và từng thread. Logical reads không phụ thuộc cache nóng hay lạnh, nên dùng nó để so hai phiên bản của một câu. Physical reads cho biết lần chạy đó phải lên đĩa bao nhiêu, và đổi theo trạng thái cache.

Quy về bảng: một page lá của DonHang chứa 218 dòng. Đọc 45.873 page là đọc cả bảng 10 triệu đơn. Đọc 69 page là vài chục đơn.

4.5 Thời gian của từng toán tử #

Kế hoạch thực tế có ActualElapsedms và ActualCPUms cho từng toán tử, theo từng thread; SSMS gom dưới mục "Actual Time Statistics". Ở row mode, con số là cộng dồn của toán tử đó và mọi toán tử con. Ở batch mode, con số chỉ của riêng toán tử. Muốn biết một toán tử row mode tự tốn bao nhiêu, lấy số của nó trừ số của con trực tiếp. Nút gốc có QueryTimeStats (CPU và thời gian của cả câu) và WaitStats (các wait lớn nhất của lần chạy).

4.6 Memory grant #

Sort, Hash Match và một số exchange cần bộ nhớ riêng. Engine tính lượng xin trước khi chạy, theo số dòng ước lượng và độ rộng dòng, rồi giữ phần được cấp đến hết câu. Nút gốc có MemoryGrantInfo:

<MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="34816"
                 RequiredMemory="6144" DesiredMemory="38912" RequestedMemory="38912"
                 GrantWaitTime="0" GrantedMemory="38912" MaxUsedMemory="27648"
                 MaxQueryMemory="11010048" />

Các số tính bằng KB (minh họa, khớp với kế hoạch sau khi sửa ở ví dụ A). Đọc ba cặp:

• GrantedMemory so với MaxUsedMemory. Dùng dưới một nửa trên câu chạy thường xuyên là xin thừa: bộ nhớ đó không cho câu khác dùng, và khi tổng grant chạm trần, câu sau phải chờ với wait RESOURCE_SEMAPHORE.
• GrantWaitTime khác 0: câu đã phải chờ bộ nhớ trước khi chạy.
• Grant thiếu: Sort hoặc Hash Match tràn xuống tempdb và có cảnh báo spill ở toán tử đó.

Từ SQL Server 2019 (compatibility level 150, Enterprise), row mode memory grant feedback thêm LastRequestedMemory và IsMemoryGrantFeedbackAdjusted. Giá trị No: First Execution, No: Accurate Grant, Yes: Adjusting, Yes: Stable, No: Feedback Disabled cho biết engine đã chỉnh grant theo các lần chạy trước hay chưa. Câu có nhu cầu bộ nhớ dao động mạnh giữa các lần chạy sẽ thấy No: Feedback Disabled: engine tự tắt cơ chế này cho câu đó.

4.7 Cảnh báo #

Cảnh báo hiện thành dấu chấm than vàng trên toán tử hoặc trên nút gốc. Có loại sinh lúc biên dịch, nên nằm cả trong kế hoạch của cache. Có loại chỉ sinh lúc chạy, nên chỉ có trong kế hoạch thực tế.

Gợi ý chỉ mục (green text "Missing Index", phần tử MissingIndexes) không phải cảnh báo. Nó là gợi ý cho đúng một câu, không xét chỉ mục đã có và không xét chi phí ghi. Đối chiếu với Chỉ mục và thống kê trước khi tạo.

Với PlanAffectingConvert, collation của BanHang là collation Windows (Vietnamese_100_CI_AS). So cột varchar với hằng hoặc tham số nvarchar, ví dụ WHERE MaSanPham = N'SP-00042', làm cột bị đổi sang nvarchar. Engine vẫn seek được nhờ tính khoảng qua hàm GetRangeThroughConvert, nhưng ước lượng kém đi và kế hoạch có cảnh báo Cardinality Estimate. Với collation SQL kiểu SQL_Latin1_General_CP1_CI_AS, cùng câu đó thành scan.

Liệt kê câu trong cache có cảnh báo lúc biên dịch:

WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS reads_moi_lan,
    qs.last_spills,
    qp.query_plan.exist(N'//Warnings/PlanAffectingConvert') AS co_convert_implicit,
    qp.query_plan.exist(N'//Warnings/@NoJoinPredicate') AS thieu_dieu_kien_noi,
    qp.query_plan.exist(N'//Warnings/@UnmatchedIndexes') AS chi_muc_loc_khong_khop,
    qp.query_plan.exist(N'//MissingIndexes') AS co_goi_y_chi_muc,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.dbid = DB_ID(N'BanHang')
ORDER BY qs.total_logical_reads DESC;

4.8 Danh sách tham số #

Nút gốc của câu có tham số có Parameter List. Mỗi tham số có hai giá trị:

• Parameter Compiled Value: giá trị lúc kế hoạch được biên dịch. Ước lượng của cả kế hoạch dựa vào giá trị này.
• Parameter Runtime Value: giá trị của lần chạy đang xem. Chỉ có trong kế hoạch thực tế.

Kế hoạch lấy từ cache chỉ có giá trị biên dịch. Hai giá trị khác nhau và số dòng lệch nhiều là dấu hiệu đầu tiên của parameter sniffing ở mục 8. Biến cục bộ (DECLARE @x ...) không được sniff: trình tối ưu không biết giá trị lúc biên dịch và ước lượng theo mật độ trung bình của cột, trừ khi câu có OPTION (RECOMPILE).

4.9 Song song #

Nút gốc có Degree of Parallelism. Kế hoạch không song song có thể kèm NonParallelPlanReason giải thích vì sao. Máy minh họa đặt MAXDOP 4, nên kế hoạch song song chạy tối đa 4 thread cho mỗi vùng song song. Trong kế hoạch song song, bấm vào toán tử rồi xem số dòng theo từng thread. Một thread nhận 90% số dòng là lệch tải: câu chạy chậm như kế hoạch một luồng mà vẫn tốn chi phí điều phối. Thread 0 là thread điều phối và thường có 0 dòng ở vùng song song.

5. Các toán tử gặp hằng ngày #

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 (cây cao 3 mức); mục 7 có số cụ thể.

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.

Sort và hai kiểu tổng hợp #

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. Ước lượng 60.000 nhóm mà thực tế 200.000 nhóm thì tràn.

Compute Scalar, Filter, Parallelism, Spool #

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.

Parallelism có ba dạng: Distribute Streams chia một luồng thành nhiều, Repartition Streams chia lại dòng giữa các thread theo hash hoặc round robin, Gather Streams gom về một luồng và có thể giữ thứ tự. Wait CXPACKET, CXCONSUMER đi kèm mọi kế hoạch song song và tự nó không phải lỗi. Lệch tải giữa các thread mới là thứ cần tìm.

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. Table Spool lưu kết quả một nhánh, Row Count Spool phục vụ kiểm tra tồn tại, Index Spool dựng chỉ mục tạm trong lúc chạy. 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.

Columnstore Index Scan ở 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. Segment skipped là rowgroup bị loại nhờ giá trị nhỏ nhất và lớn nhất của cột trong rowgroup đó.

6. Ví dụ A: doanh thu tháng 9/2026 theo sản phẩm #

Báo cáo cuối tháng: doanh thu từng sản phẩm trong tháng 9/2026, không tính đơn đã hủy. Số liệu gốc:

Phiên bản đầu: lọc tháng bằng hàm #

SELECT
    sp.SanPhamId,
    sp.MaSanPham,
    sp.Ten,
    SUM(ct.SoLuong * ct.DonGia) AS DoanhThu
FROM dbo.DonHang AS d
JOIN dbo.ChiTietDonHang AS ct
    ON ct.NgayTao = d.NgayTao
   AND ct.DonHangId = d.DonHangId
JOIN dbo.SanPham AS sp
    ON sp.SanPhamId = ct.SanPhamId
WHERE YEAR(d.NgayTao) = 2026
  AND MONTH(d.NgayTao) = 9
  AND d.TrangThai <> 5
GROUP BY sp.SanPhamId, sp.MaSanPham, sp.Ten;

Kế hoạch thực tế (minh họa, một số toán tử Parallelism ở giữa được lược bớt):

-- minh họa: YEAR()/MONTH() trên NgayTao, DOP 4
SELECT                                          Optimization Level FULL   DOP 4
  Parallelism (Gather Streams)
    Hash Match (Inner Join)                     ct.SanPhamId = sp.SanPhamId      est 4.400    act 4.986
      Clustered Index Scan PK_SanPham                                            est 5.000    act 5.000
      Hash Match (Aggregate)                    GROUP BY ct.SanPhamId            est 4.400    act 4.986
        Nested Loops (Inner Join)                                                est 63.000   act 1.119.300
          Clustered Index Scan PK_DonHang       est 21.000   act 373.100   Rows Read 10.000.000
            Predicate: YEAR(NgayTao) = 2026 AND MONTH(NgayTao) = 9 AND TrangThai <> 5
            Actual Partition Count 4            Actual Partitions Accessed 1..4
          Clustered Index Seek PK_ChiTietDonHang
            Seek Predicate: NgayTao = d.NgayTao AND DonHangId = d.DonHangId
            est 3 mỗi lần   Estimated Number of Executions 21.000
            act 1.119.300   Number of Executions 373.100

-- minh họa, rút gọn các cột bằng 0
Table 'ChiTietDonHang'. Scan count 373100, logical reads 1123186, physical reads 0, read-ahead reads 0.
Table 'DonHang'. Scan count 5, logical reads 45891, physical reads 0, read-ahead reads 0.
Table 'SanPham'. Scan count 1, logical reads 64, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 3891 ms,  elapsed time = 1204 ms.

Đọc theo thứ tự ở mục 4:

1. Ước lượng lệch. Đi từ phải sang trái, chỗ lệch đầu tiên là Clustered Index Scan trên PK_DonHang: ước lượng 21.000, thực tế 373.100, gấp khoảng 18 lần. YEAR() và MONTH() bọc cột nên histogram của NgayTao không dùng được; 21.000 là con số đoán, không lấy từ thống kê.
2. Predicate. Không có Seek Predicate. Cả ba điều kiện nằm ở Predicate, kiểm tra trên 10.000.000 dòng đã đọc.
3. Partition. Actual Partition Count = 4: đọc cả năm 2024, 2025 và partition rỗng từ 2027. Khoảng 45.873 page lá cho một tháng dữ liệu.
4. Nested Loops. Vì tưởng phía ngoài chỉ 21.000 dòng, trình tối ưu chọn Nested Loops với seek vào ChiTietDonHang. Thực tế phía trong chạy 373.100 lần, mỗi lần khoảng 3 page. 1.123.186 trong tổng 1.169.141 logical reads nằm ở đây.
5. Phần trăm chi phí vẫn tính theo 21.000 lần chạy, nên Clustered Index Seek trên ChiTietDonHang không trông như chỗ tốn nhất. ActualElapsedms và logical reads cho thấy điều ngược lại.

Sau khi sửa: điều kiện khoảng trên cả hai bảng #

SELECT
    sp.SanPhamId,
    sp.MaSanPham,
    sp.Ten,
    SUM(ct.SoLuong * ct.DonGia) AS DoanhThu
FROM dbo.DonHang AS d
JOIN dbo.ChiTietDonHang AS ct
    ON ct.NgayTao = d.NgayTao
   AND ct.DonHangId = d.DonHangId
JOIN dbo.SanPham AS sp
    ON sp.SanPhamId = ct.SanPhamId
WHERE d.NgayTao >= CAST('20260901' AS datetime2(0))
  AND d.NgayTao <  CAST('20261001' AS datetime2(0))
  AND ct.NgayTao >= CAST('20260901' AS datetime2(0))
  AND ct.NgayTao <  CAST('20261001' AS datetime2(0))
  AND d.TrangThai <> 5
GROUP BY sp.SanPhamId, sp.MaSanPham, sp.Ten;

Điều kiện ngày được ghi trên cả hai bảng để partition elimination chạy ở cả hai phía, không phụ thuộc trình tối ưu có tự suy điều kiện qua phép nối hay không. Vì sao điều kiện dạng khoảng dùng được chỉ mục còn YEAR(NgayTao) thì không, xem phần sargability ở Chỉ mục và thống kê.

-- minh họa: điều kiện khoảng, DOP 4
SELECT                                          DOP 4   Granted 38.912 KB   MaxUsed 27.648 KB
  Parallelism (Gather Streams)
    Hash Match (Inner Join)                     ct.SanPhamId = sp.SanPhamId      est 5.000      act 4.986
      Clustered Index Scan PK_SanPham                                            est 5.000      act 5.000
      Hash Match (Aggregate)                    GROUP BY ct.SanPhamId            est 5.000      act 4.986
        Hash Match (Inner Join)                 ct.NgayTao = d.NgayTao AND ct.DonHangId = d.DonHangId
                                                                                 est 1.118.000  act 1.119.300
          Clustered Index Seek PK_DonHang       est 372.600   act 373.100   Rows Read 392.700
            Seek Keys: Prefix PtnId1000 = 3, Start NgayTao >= '2026-09-01', End NgayTao < '2026-10-01'
            Predicate: TrangThai <> 5
            Actual Partition Count 1            Actual Partitions Accessed 3
          Clustered Index Seek PK_ChiTietDonHang                     est 1.177.000  act 1.178.100
            Seek Keys: Prefix PtnId1000 = 3, Start NgayTao >= '2026-09-01', End NgayTao < '2026-10-01'
            Actual Partition Count 1            Actual Partitions Accessed 3

-- minh họa, rút gọn các cột bằng 0
Table 'ChiTietDonHang'. Scan count 5, logical reads 6151, physical reads 0, read-ahead reads 0.
Table 'DonHang'. Scan count 5, logical reads 1818, physical reads 0, read-ahead reads 0.
Table 'SanPham'. Scan count 1, logical reads 64, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 641 ms,  elapsed time = 236 ms.

Ước lượng đúng kéo theo kiểu nối đúng. Hai bảng đọc theo khóa đã sắp nên Merge Join cũng là ứng viên; với kế hoạch song song trên máy minh họa, trình tối ưu chọn Hash Match. Đọc chi phí trong kế hoạch của máy mình thay vì đoán.

Phía build của Hash Match là 373.100 dòng DonHang, nên grant tính theo con số đó. Nếu ước lượng phía build thấp hơn thực tế nhiều lần, grant thiếu và kế hoạch thực tế có cảnh báo dạng "Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s)" kèm số page ghi và đọc lại từ tempdb. Mục 8 có một trường hợp như vậy.

Khi bảng có columnstore #

Nếu đã tạo NCCI_DonHang ở chương 2, trình tối ưu xét batch mode cho câu này, kể cả trên SQL Server 2017 hay bản Standard: chỉ cần một bảng trong câu có chỉ mụ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.

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

Logical reads giữ nguyên khoảng 8.000 page. CPU giảm, ví dụ từ 641 ms xuống khoảng 230 ms trên cùng máy (minh họa): batch mode giảm CPU, không giảm số page phải đọc. 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: 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. 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

-- minh họa
Table 'DonHang'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 0.

Đọ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 và thống kê, không còn Key Lookup. Khách 42: 108.000 / 238 ≈ 454 page lá cộng vài page trên lá. Cách chọn cột cho INCLUDE và cái giá khi ghi nằm ở Chỉ mục và thống kê. 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.

8. Parameter sniffing: cơ chế #

Lần đầu thủ tục chạy, trình tối ưu đọc giá trị tham số (sniff), ước lượng theo histogram cho đúng giá trị đó, và cất kế hoạch. Các lần sau dùng lại kế hoạch ấy cho mọi giá trị, đến khi kế hoạch bị đẩy khỏi cache hoặc bị biên dịch lại. Câu gửi qua sp_executesql và prepared statement từ driver đi theo cùng cơ chế. Sniffing là cơ chế có ích: không có nó, mọi ước lượng chỉ là trung bình. Nó thành vấn đề khi dữ liệu lệch, và TrangThai của BanHang lệch: 1% đơn ở trạng thái 1 (Mới), 90% ở trạng thái 4 (Hoàn tất).

Thủ tục cho trang quản trị: 20 khách có tổng giá trị đơn lớn nhất theo một trạng thái.

CREATE OR ALTER PROCEDURE dbo.usp_KhachHang_TheoTrangThai
    @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;
GO

DonHang không có chỉ mục bắt đầu bằng TrangThai, nên cả hai giá trị đều scan PK_DonHang. Hình dạng kế hoạch gần giống nhau. Thứ khác nhau là số dòng ước lượng sau scan, và vì vậy là số nhóm ước lượng, memory grant của Hash Aggregate, có thể cả việc gộp sơ bộ trong từng thread trước khi chia lại dòng giữa các thread. Số liệu dưới đây giả định bảng chưa có NCCI_DonHang; nếu có, câu tổng hợp này đọc columnstore và kế hoạch khác hẳn.

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

Số trong bảng là minh họa. Scan 45.873 page chiếm phần lớn thời gian ở cả bốn dòng, nên chênh lệch nằm ở phần tổng hợp. Kế hoạch biên dịch cho giá trị 1 chạy với giá trị 4:

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

Phần XML cho thấy nguyên nhân mà không cần đoán:

<ParameterList>
  <ColumnReference Column="@TrangThai" ParameterDataType="tinyint"
                   ParameterCompiledValue="(1)" ParameterRuntimeValue="(4)" />
</ParameterList>
...
<Warnings>
  <SpillToTempDb SpillLevel="1" SpilledThreadCount="4" />
  <HashSpillDetails GrantedMemoryKb="5632" UsedMemoryKb="5632"
                    WritesToTempDb="1280" ReadsFromTempDb="1280" />
</Warnings>

Ba điểm phải thấy: giá trị biên dịch là 1 còn giá trị chạy là 4; ước lượng 100.000 dòng so với 9.000.000 thực tế; Hash Aggregate tràn vì grant tính cho khoảng 61.800 nhóm mà thực tế gần 200.000 nhóm. Chiều ngược lại không tràn, nhưng mỗi lần gọi với giá trị 1 giữ 24.576 KB mà chỉ dùng 4.800 KB. Trang quản trị gọi thủ tục vài chục lần mỗi giây thì phần xin thừa đó cộng dồn thành chờ RESOURCE_SEMAPHORE.

Trên SQL Server 2019 Enterprise, compatibility level 150, memory grant feedback chỉnh grant theo lần chạy trước. Với thủ tục bị gọi xen kẽ 1 và 4, nhu cầu bộ nhớ dao động, và kế hoạch dần ghi IsMemoryGrantFeedbackAdjusted = No: Feedback Disabled.

Bảng DonHang có sẵn chỉ mục lọc IX_DonHang_DangMo ... WHERE TrangThai = 1 từ chương 2. Nó phủ đủ câu này cho giá trị 1 nhưng kế hoạch trong cache không dùng được: kế hoạch phải đúng cho mọi giá trị của @TrangThai, kể cả 4. Kế hoạch có thể kèm cảnh báo UnmatchedIndexes. Với OPTION (RECOMPILE), tham số được thay bằng hằng lúc biên dịch và chỉ mục lọc khớp:

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;

Với giá trị 1, kế hoạch đọc IX_DonHang_DangMo, khoảng 470 logical reads thay vì 45.891 (minh họa). Với giá trị 4, kế hoạch scan PK_DonHang và grant tính cho 9.000.000 dòng.

Các cách xử lý #

Query Store hint trên SQL Server 2022, áp RECOMPILE cho câu trong thủ tục mà không sửa thủ tục. 1873 là query_id tìm theo cách ở mục 9:

EXEC sys.sp_query_store_set_hints @query_id = 1873, @query_hints = N'OPTION (RECOMPILE)';

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

EXEC sys.sp_query_store_clear_hints @query_id = 1873;   -- gỡ khi không cần nữa

Parameter Sensitive Plan optimization #

Từ SQL Server 2022, compatibility level 160, có trên mọi edition. Lần biên dịch đầu dùng histogram để tìm điều kiện bằng có dữ liệu lệch, tối đa ba điều kiện. Câu đủ điều kiện được cất thành một dispatcher plan. Dispatcher chia số dòng ước lượng thành ba khoảng (thấp, vừa, cao) và với mỗi khoảng biên dịch một query variant riêng, mỗi variant có kế hoạch riêng trong cache và trong Query Store.

Trong XML của dispatcher plan có phần tử Dispatcher với ParameterSensitivePredicate LowBoundary="..." HighBoundary="...". Văn bản của variant được nối thêm option (PLAN PER VALUE(ObjectID = ..., QueryVariantID = ..., predicate_range(...))); gợi ý này do engine sinh ra và không gõ tay được. Ví dụ trong tài liệu Microsoft có ranh giới 100 và 1.000.000 dòng. Nếu ranh giới cho TrangThai tương tự, 100.000 đơn của giá trị 1 và 9.000.000 đơn của giá trị 4 rơi vào hai khoảng khác nhau và có hai kế hoạch. Ranh giới thật do engine tính từ thống kê; đọc nó trong XML thay vì giả định.

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

Tắt cho cả database bằng ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF, cho một câu bằng USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'). PSP không hoạt động với câu có RECOMPILE, và tắt theo khi parameter sniffing bị tắt.

9. So sánh kế hoạch theo thời gian trong Query Store #

Plan cache chỉ có kế hoạch hiện tại. Query Store giữ mọi kế hoạch của một câu (query_id), mỗi kế hoạch một plan_id, cùng số liệu chạy theo khoảng thời gian, mặc định mỗi khoảng một giờ. Với QUERY_CAPTURE_MODE = AUTO như chương 2, câu chạy ít và rẻ có thể không được ghi.

Các kế hoạch của câu trong thủ tục ở mục 8, trong 7 ngày qua, với số lần chạy và trung bình có trọng số. sys.query_store_runtime_stats có thể có nhiều dòng cho cùng một kế hoạch trong cùng khoảng, nên luôn gộp theo plan_id:

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

avg_duration tính bằng micro giây, avg_logical_io_reads bằng page 8 KB, max_query_max_used_memory bằng page 8 KB nên nhân 8 ra KB. Kết quả minh họa:

Một query_id, hai plan_id. Hai kế hoạch đọc cùng số page, khác nhau ở thời gian và bộ nhớ, đúng như mục 8. Thấy được kế hoạch nào chạy lúc nào bằng cách xem theo từng khoảng, đổi sang giờ Việt Nam:

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;

Ép một kế hoạch, kiểm tra kết quả ép, rồi gỡ. 1873 và 2231 là số minh họa, thay bằng số của máy mình. Cột query_plan là nvarchar(max); TRY_CONVERT sang xml để bấm mở trong SSMS, và trả NULL thay vì báo lỗi khi kế hoạch lồng quá sâu:

EXEC sys.sp_query_store_force_plan @query_id = 1873, @plan_id = 2231;

SELECT plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc,
       TRY_CONVERT(xml, query_plan) AS ke_hoach
FROM sys.query_store_plan
WHERE query_id = 1873;

EXEC sys.sp_query_store_unforce_plan @query_id = 1873, @plan_id = 2231;

Ép kế hoạch làm câu được biên dịch lại theo kế hoạch đã chọn. Kết quả thường giống hệt hoặc gần giống, không bảo đảm y hệt. Ép thất bại, ví dụ vì chỉ mục trong kế hoạch đã bị xóa, thì câu tối ưu như bình thường, force_failure_count tăng, và last_force_failure_reason_desc ghi lý do như NO_INDEX. Báo cáo SSMS "Regressed Queries" và "Queries With Forced Plans" cho cùng thông tin ở dạng đồ họa.

10. Đọc một kế hoạch trong năm phút #

1. Lấy đúng kế hoạch. Kế hoạch thực tế của đúng câu, đúng giá trị tham số, đúng SET option ứng dụng dùng. Không có thì lấy kế hoạch thực tế gần nhất (sys.dm_exec_query_plan_stats), rồi kế hoạch trong cache hoặc Query Store.
2. Nút gốc SELECT. QueryTimeStats, Degree of Parallelism, MemoryGrantInfo, Optimization Level và lý do dừng tối ưu, Parameter List, cảnh báo, WaitStats.
3. Chỗ tốn thật. Tìm toán tử có ActualElapsedms riêng lớn nhất (row mode thì trừ phần của con) và có logical reads lớn nhất. Bỏ qua phần trăm chi phí.
4. Chỗ lệch đầu tiên. Đi từ phải sang trái, tìm toán tử đầu tiên mà actual lệch estimated for all executions từ 10 lần trở lên.
5. Tại chỗ lệch. Seek Predicate hay Predicate, Number of Rows Read, hàm bọc cột, CONVERT_IMPLICIT, biến cục bộ, thống kê của cột đó.
6. Nested Loops. Number of Executions của phía trong. Key Lookup chạy hàng nghìn lần trở lên là ứng viên cho chỉ mục phủ.
7. Sort và Hash. Granted so với MaxUsed, cảnh báo spill.
8. Song song. Số dòng theo từng thread, lệch tải.
9. Lịch sử. Câu có nhiều plan_id trong Query Store không, kế hoạch đổi lúc nào, ParameterCompiledValue của kế hoạch hiện tại là gì.
10. Đổi một thứ. Sửa đúng một chỗ, chạy lại với SET STATISTICS IO, TIME ON, so logical reads và CPU với bản trước.

Những chỗ hay hiểu sai #

• "Toán tử 60% là chỗ chậm nhất." Phần trăm là chi phí ước lượng, kể cả trong kế hoạch thực tế. Chỗ chậm đọc ở ActualElapsedms và logical reads.
• "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ử.
• "Kế hoạch lấy từ cache là kế hoạch thực tế." Cache và Query Store giữ kế hoạch ước lượng. Số dòng thực tế chỉ có trong kế hoạch thực 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.
• "Gợi ý Missing Index là chỉ mục nên tạo." Đó là gợi ý cho một câu, không xét chỉ mục đã có và chi phí ghi.
• "ActualElapsedms là thời gian riêng của toán tử." Ở row mode con số đó cộng cả các toán tử con. Ở batch mode mới là thời gian riêng.
• "Chạy thử trong SSMS nhanh nên câu không có vấn đề." SSMS có SET option khác ứng dụng (ARITHABORT), nên dùng mục cache khác và sniff giá trị bạn gõ. Thay tham số bằng biến cục bộ còn đổi luôn cách ước lượng.
• "Biến cục bộ được ước lượng như tham số." Biến cục bộ không được sniff. Với TrangThai, ước lượng là 2.000.000 dòng cho mọi giá trị.
• "OPTION (RECOMPILE) là cách sửa an toàn mọi lúc." Nó tốn CPU biên dịch ở mỗi lần chạy, và kế hoạch không ở lại cache để memory grant feedback hay PSP làm việc.
• "Ép kế hoạch trong Query Store là xong." Đó là biện pháp giữ tạm. Việc ép có thể thất bại, và kế hoạch bị ép có thể tệ đi khi dữ liệu đổi; kiểm tra force_failure_count và gỡ khi đã sửa gốc.

Đọc tiếp #

• Histogram, mật độ, cardinality estimator, sargability và chỉ mục phủ, những thứ quyết định số ước lượng trong mọi kế hoạch ở bài này: Chỉ mục và thống kê.
• Vì sao tham số nvarchar so với cột varchar sinh CONVERT_IMPLICIT: Kiểu dữ liệu, collation và khóa chính.
• Kế hoạch đọc nhiều page hơn cũng lấy nhiều khóa chung hơn khi READ COMMITTED chạy bằng khóa, nên dễ đụng phiên đang ghi: Transaction, khóa và mức isolation.
• 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.
• Cấu hình Query Store, columnstore và chỉ mục lọc dùng trong bài: Kỹ thuật thường dùng. Trên Azure SQL Database, Query Store bật sẵn: Azure SQL.
• Cách PostgreSQL tổ chức page và tuple, nền để đọc EXPLAIN bên đó: Kiến trúc lưu trữ PostgreSQL.

Nguồn #

• Execution plan overview
• Query processing architecture guide
• Display an actual execution plan
• Query profiling infrastructure
• sys.dm_exec_query_plan_stats
• sys.dm_exec_query_stats
• SET STATISTICS IO
• Server configuration: optimize for ad hoc workloads
• Joins, gồm Adaptive Joins
• Intelligent query processing details
• Memory grant feedback
• Parameter Sensitive Plan optimization
• Query Store hints
• sp_query_store_force_plan
• sys.query_store_plan
• sys.query_store_runtime_stats
• sys.query_store_query_variant
• Editions and supported features of SQL Server 2019
• Editions and supported features of SQL Server 2022
• Showplan XML schema, SQL Server 2019
• New Showplan XML properties in SSMS October Release (SQL Server Team, lưu trữ)