Execution Plan trong SQL Server là gì và tại sao bạn cần biết (phần 1)
Mục lục
Bắt đầu bằng một tình huống thực tế
Bạn viết một câu query, nhấn F5, rồi ngồi chờ. 5 giây. 10 giây. Màn hình vẫn xoay. Bạn thử thêm điều kiện, bỏ bớt cột, copy lên google tìm kiếm “SQL query chạy chậm” — nhưng vẫn không biết vấn đề nằm ở đâu.
Nguyên nhân thường gặp nhất là bạn đang cố sửa một thứ mà bạn chưa nhìn thấy.
SQL Server mỗi khi chạy một câu query, nó ghi lại toàn bộ quá trình xử lý — đọc bảng nào, dùng index nào, ghép dữ liệu kiểu gì, tốn bao nhiêu tài nguyên. Thứ đó gọi là Execution Plan.
Vấn đề là hầu hết mọi người không biết thứ đó tồn tại, hoặc biết nhưng chưa bao giờ mở ra xem.
SQL Server không chỉ “chạy” câu lệnh của bạn
Trước khi nói đến Execution Plan, cần hiểu một chút về những gì xảy ra bên trong SQL Server khi bạn nhấn F5.
Nhiều người hình dung SQL Server như một cái máy: nhét câu SQL vào, dữ liệu trả ra. Thực ra nó phức tạp hơn nhiều. Giữa lúc bạn gửi câu query và lúc dữ liệu trả về, SQL Server trải qua 4 bước:
Bước 1 — Parser
Bước này chỉ làm một việc: kiểm tra xem câu SQL của bạn có đúng cú pháp không. Bạn viết SELCT thay vì SELECT? Thiếu dấu ngoặc? Quên dấu phẩy giữa các cột? Parser phát hiện ngay và báo lỗi, query không chạy tiếp.
Bước 2 — Algebrizer
Sau khi cú pháp ổn, SQL Server kiểm tra xem các đối tượng bạn gọi có thực sự tồn tại không. Bảng DonHang có trong database không? Cột TongTien có thuộc bảng đó không? Bạn đang JOIN với một bảng mà bạn không có quyền truy cập? Nếu có vấn đề, lỗi sẽ xuất hiện ở đây.
Bước 3 — Query Optimizer
Đây là bước quan trọng nhất — và cũng là bước mà đại đa số developer không biết tới.
Query Optimizer là “bộ não” của SQL Server. Khi nhận được câu query đã qua kiểm tra, nó không chạy ngay mà dừng lại để suy nghĩ: có bao nhiêu cách để lấy được dữ liệu này? Cách nào nhanh nhất?
Ví dụ, để lấy danh sách đơn hàng của một khách hàng cụ thể, SQL Server có thể:
- Đọc toàn bộ bảng từ đầu đến cuối, lọc ra những dòng khớp
- Dùng index trên cột KhachHang để nhảy thẳng đến đúng dòng cần tìm
- Kết hợp nhiều index theo nhiều cách khác nhau
Mỗi cách có chi phí khác nhau. Query Optimizer ước tính chi phí của từng phương án, rồi chọn phương án tốt nhất. Kết quả của quá trình lựa chọn này chính là Execution Plan.
Bước 4 — Thực thi
SQL Server làm đúng theo Execution Plan. Nếu plan nói “đọc toàn bộ bảng”, nó đọc toàn bộ. Nếu plan nói “dùng index IX_NgayDat”, nó dùng đúng cái index đó.
Execution Plan là gì
Hãy lấy một ví dụ ngoài đời thực.
Bạn cần đi từ Hà Nội vào TP.HCM. Bạn có thể đi máy bay, tàu hỏa, xe khách, hoặc tự lái xe. Mỗi cách có thời gian và chi phí khác nhau. Trước khi lên đường, bạn so sánh rồi chọn một cách.
SQL Server cũng vậy. Để lấy dữ liệu bạn yêu cầu, nó có nhiều “con đường” khác nhau. Execution Plan là bản ghi lại con đường SQL Server đã chọn — gồm toàn bộ các bước thực hiện, theo đúng thứ tự, kèm theo chi phí ước tính của từng bước.
Khi query chậm, thay vì đoán mò, bạn mở Execution Plan ra và nhìn thẳng vào: SQL Server đang đi qua các bước nào, và bước nào đang tốn chi phí nhiều nhất.
Mở Execution Plan ở đâu?
Trong SQL Server Management Studio (SSMS) có 2 cách:
Estimated Execution Plan — Ctrl + L
SQL Server sẽ tạo plan mà không cần chạy query. Nhanh, an toàn, dùng được ngay cả khi bạn chưa muốn thực thi truy vấn — ví dụ với các câu INSERT, UPDATE, DELETE mà bạn đang viết thử.
Actual Execution Plan — Ctrl + M rồi F5 (sử dụng F5 để chạy câu lệnh hoặc click button Execute)
Ctrl + M bật chế độ thu thập plan. Sau đó bạn chạy query bình thường bằng F5. Khi query xong, tab Execution Plan xuất hiện ở phía dưới cùng với tab Results và Messages.
Cái này chính xác hơn vì nó cho bạn thấy số dòng thực tế SQL Server đã xử lý, không chỉ là ước tính.
Nguyên tắc đơn giản: dùng Estimated khi bạn muốn xem nhanh, dùng Actual khi bạn đang debug một query có vấn đề thật sự.
Thử ngay — tạo dữ liệu và xem plan đầu tiên
Lưu ý: Để thực hiện được ví dụ dưới đây thì bạn nên cài 2 tool trước là SQL Server Express và SQL Sever Management Studio (https://datapot.vn/huong-dan-cai-dat-sql-server-2022-moi-nhat/ ). Thực hiện tạo database và schema để thực hiện các bước tiếp theo
Chạy đoạn script này để có bảng mẫu:
CREATE TABLE DonHang (
MaDon INT PRIMARY KEY,
KhachHang NVARCHAR(100),
NgayDat DATE,
TongTien DECIMAL(18,2),
TrangThai NVARCHAR(20)
);
Thực hiện insert 1000 record random vào bảng dbo.DonHang
WITH So AS (
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM sys.objects a CROSS JOIN sys.objects b
)
INSERT INTO DonHang (MaDon, KhachHang, NgayDat, TongTien, TrangThai)
SELECT
N,
N'Khách hàng ' + CAST(N % 10 + 1 AS NVARCHAR),
DATEADD(DAY, -(N % 365), '2024-12-31'),
CAST((N % 50 + 1) * 100000 AS DECIMAL(18,2)),
CASE N % 3
WHEN 0 THEN N'Hoàn thành'
WHEN 1 THEN N'Đang xử lý'
ELSE N'Đã hủy'
END
FROM So;
Bật Actual Plan (Ctrl + M) rồi chạy, câu lệnh có ý nghĩa là thực hiện tính tổng tiền mua và tổng số đơn hàng của mỗi khách hàng, với điều kiện chỉ lấy các đơn mua từ 01-01-2024 và có trạng thái là hoàn thành, sau đó sắp xếp kết quả giảm dần theo TongTien.
SELECT
KhachHang,
COUNT(*) AS SoDon,
SUM(TongTien) AS TongTien
FROM DonHang
WHERE NgayDat >= '2024-01-01'
AND TrangThai = N'Hoàn thành'
GROUP BY KhachHang
ORDER BY TongTien DESC;
Click vào tab Execution Plan. Bạn sẽ thấy một sơ đồ với vài hình nối bằng mũi tên. Đó là Execution Plan đầu tiên của bạn.
Bước 1 — Nhìn vào operator bên phải nhất. Đó là điểm SQL Server bắt đầu — nơi nó đọc dữ liệu từ bảng DonHang.
Bước 2 — Nhìn vào con số % bên dưới. Operator đó chiếm bao nhiêu phần trăm tổng chi phí? con số ở bước đầu tiên đang là 28%, nghĩa là nó đang dành khoảng 1/3 tổng thời gian thực hiện của câu lệnh để dùng đọc dữ liệu từ bảng
Bước 3 — Trỏ chuột vào operator đầu tiên từ bên phải. Trong tooltip, tìm hai dòng: Estimated Number of Rows to be Read và Actual Number of Rows Read. Bảng có 1000 dòng, và SQL Server đã đọc bao nhiêu dòng ? Ngoài ra còn có các thông tin quan trọng như Estimate CPU Cost và Estimate I/O Cost ~ chi phí ước tính tiêu tốn của CPU và của các action đọc ghi dữ liệu, Predicate ~ điều kiện lọc mà operator đang áp dụng, Output List thể hiện các column đầu ra của operator tương ứng.
Ở thời điểm này chưa cần hiểu sâu từng chỉ số. Chỉ cần biết chúng ở đó, và mỗi bài tiếp theo sẽ lần lượt “giải mã” từng cái một khi đến đúng ngữ cảnh.
Chỉ cần làm được 3 bước đó, bạn đã biết cách đọc một Execution Plan cơ bản. Ý nghĩa của từng operator — tại sao nó chậm, cách fix — là chủ đề của các bài tiếp theo.
Đọc Execution Plan như thế nào?
Nhìn vào plan lần đầu, hầu hết mọi người đều thấy rối. Thực ra chỉ cần nhớ 3 thứ:
Quy tắc 1: Đọc từ phải sang trái. Dữ liệu chảy từ phải (nguồn — các bảng) sang trái (kết quả trả về cho bạn). Bước đầu tiên SQL Server làm luôn nằm ở góc phải, bước cuối nằm ở góc trái.
Quy tắc 2: Mỗi hình là một thao tác. Mỗi hình trong sơ đồ gọi là một operator. Mỗi operator làm một việc cụ thể: đọc dữ liệu từ bảng, lọc, sắp xếp, ghép hai tập dữ liệu… Bài sau sẽ đi sâu vào từng loại.
Quy tắc 3: Con số % là chi phí tương đối. Bên dưới mỗi operator có một con số phần trăm — phần trăm tổng chi phí mà operator đó chiếm. Operator nào % cao nhất là chỗ đang ngốn tài nguyên nhiều nhất, và là nơi bạn nên nhìn vào đầu tiên.
Trỏ chuột vào operator để xem thông tin chi tiết
Một tính năng rất hữu ích mà nhiều người bỏ qua: khi hover chuột vào bất kỳ operator nào trong SSMS, một tooltip xuất hiện với rất nhiều thông tin. Những thứ quan trọng cần chú ý:
Estimated Rows vs Actual Rows — đây là cặp số quan trọng nhất. SQL Server ước tính sẽ xử lý bao nhiêu dòng, và thực tế nó xử lý bao nhiêu. Nếu hai con số này chênh lệch lớn — ví dụ ước tính 10 dòng nhưng thực tế 80.000 dòng — thì statistics đang có vấn đề và Query Optimizer đã đưa ra quyết định dựa trên thông tin sai.
Estimated I/O Cost và CPU Cost — cho bạn biết operator đó đang tốn tài nguyên ở đâu. I/O cao thường do đọc quá nhiều dữ liệu từ đĩa. CPU cao thường do sắp xếp, tính toán phức tạp, hoặc join không tối ưu.
Output List — danh sách các cột operator này trả ra cho bước tiếp theo. Nếu thấy quá nhiều cột trong khi query chỉ cần vài cột, đó là dấu hiệu nên tránh SELECT *.
Lưu và so sánh Execution Plan
Một tính năng ít người biết trong SSMS: bạn có thể lưu Execution Plan thành file .sqlplan để so sánh trước và sau khi tối ưu.
Cách làm: click chuột phải vào vùng Execution Plan → chọn Save Execution Plan As → lưu thành file .sqlplan.
Sau này khi đã thực hiện tối ưu mà muốn so sánh lại plan thì có thể click chuột phải chọn “Compare show plan” -> chọn file .sqlplan để so sánh.
Rất hữu ích khi đang tối ưu một query qua nhiều bước — bạn lưu plan ban đầu, rồi so sánh với plan sau khi chỉnh sửa để thấy rõ thay đổi.
Tóm lại
Execution Plan là thứ duy nhất cho bạn biết SQL Server đang thực sự làm gì khi chạy query.
Ba thứ cơ bản cần nhớ khi đọc execution plan: đọc từ phải sang trái, operator có % cao nhất là điểm cần xem trước, dấu chấm than vàng là gợi ý trực tiếp từ SQL Server.
Bài tiếp theo sẽ đi vào phần thực chất: các operator bạn gặp hàng ngày — Table Scan, Index Seek, Key Lookup, và các kiểu Join — chúng khác nhau như thế nào, và nhìn vào plan thì có thể biết ngay nên cải tiến gì.
