Trong quá trình tối ưu query với Microsoft SQL Server, rất nhiều developer mắc một sai lầm phổ biến: tối ưu dựa trên cảm giác. Thêm index theo suy đoán, rewrite query theo kinh nghiệm cá nhân, nhưng không kiểm chứng SQL Server thực sự đang làm gì phía sau.

Execution Plan (kế hoạch thực thi) chính là “bản đồ” cho bạn biết database engine đang truy cập dữ liệu ra sao, join kiểu gì, sort như thế nào và tốn tài nguyên ở đâu. Nếu không đọc Execution Plan, việc tối ưu gần như là mò mẫm trong bóng tối.

Bài viết này sẽ giúp bạn hiểu đầy đủ cách đọc, phân tích và tối ưu dựa trên Execution Plan – theo hướng thực chiến.

1. Execution Plan là gì?

Execution Plan là mô tả chi tiết cách SQL Server thực thi một câu lệnh T-SQL. Nó cho bạn biết:

SQL dùng Index Seek hay Table Scan?
Join bằng Nested Loop hay Hash Match?
Có Sort không?
Có Key Lookup không?
Estimated rows có khớp với Actual rows không?

Có hai loại:

Estimated Execution Plan – kế hoạch ước lượng trước khi chạy.
Actual Execution Plan – kế hoạch thực tế sau khi chạy (quan trọng hơn).

Trong SQL Server Management Studio, bạn bật Actual Execution Plan bằng Ctrl + M.

2. Cách đọc Execution Plan đúng cách

Nguyên tắc 1: Đọc từ phải sang trái

Execution Plan chạy theo chiều từ phải qua trái. Operator ngoài cùng bên phải là nơi dữ liệu được truy xuất đầu tiên.

Nguyên tắc 2: Tìm operator có % cost cao nhất

Operator nào chiếm tỷ lệ cost cao thường là điểm nghẽn chính.

Nguyên tắc 3: So sánh Estimated vs Actual Rows

Nếu ước lượng sai nghiêm trọng → SQL có thể chọn sai chiến lược join → query chậm.

3. Các Operator Quan Trọng Nhất

3.1 Table Scan (Nguy hiểm với bảng lớn)

SQL quét toàn bộ bảng.

Xảy ra khi:

Không có index phù hợp
WHERE không SARGable
Implicit conversion

Với bảng vài triệu record → rất tốn I/O.

3.2 Index Seek (Tối ưu)

SQL tìm đúng vị trí dữ liệu trong index.

Luôn muốn thấy Seek thay vì Scan.

3.3 Index Scan

Quét toàn bộ index. Tốt hơn Table Scan nhưng vẫn là quét toàn bộ.

3.4 Key Lookup (I/O killer)

Xảy ra khi:

SQL dùng index để tìm key
Nhưng cần thêm cột không có trong index
Phải quay lại clustered index để lấy dữ liệu

Nếu xảy ra hàng chục nghìn lần → rất chậm.

Giải pháp: tạo Covering Index.

3.5 Nested Loop

Tốt khi:

Một bảng nhỏ
Bảng còn lại có index

3.6 Hash Match

Thường dùng khi:

Join bảng lớn
Không có index tốt

Tốn RAM. Có thể spill ra tempdb.

3.7 Sort

Sort tốn CPU và memory. Nếu không có index theo ORDER BY, SQL buộc phải sort.

4. Ví dụ Thực Tế Phân Tích Execution Plan

Giả sử bạn có query:

SELECT o.Id, o.Amount, o.CreatedDate, u.FullName
FROM Orders o
JOIN Users u ON o.UserId = u.Id
WHERE o.Status = 1
ORDER BY o.CreatedDate DESC

Dữ liệu:

Orders: 5 triệu rows
Users: 500k rows

4.1 Execution Plan ban đầu

Plan có dạng:

Clustered Index Scan (Orders)
Clustered Index Scan (Users)
Hash Match (Join)
Sort

Estimated Rows: 10
Actual Rows: 120,000

Query chạy: 3.2 giây.

4.2 Phân tích từng vấn đề

Vấn đề 1: Scan bảng Orders

Không có index cho Status → SQL phải quét toàn bộ 5 triệu dòng.

Vấn đề 2: Hash Join

Do không có index phù hợp cho join → SQL chọn Hash Match → tốn RAM.

Vấn đề 3: Sort

Không có index theo CreatedDate → SQL phải sort 120k rows.

Vấn đề 4: Estimated sai lệch

Estimated 10, Actual 120k → statistics không chính xác.

5. Tối ưu đúng cách

5.1 Tạo index phù hợp

CREATE INDEX IX_Orders_Status_CreatedDate
ON Orders(Status, CreatedDate DESC)
INCLUDE (UserId, Amount);

Index này:

Lọc theo Status
Sắp xếp theo CreatedDate
Cover cột select
Hỗ trợ join

5.2 Cập nhật statistics

UPDATE STATISTICS Orders;

6. Execution Plan sau tối ưu

Plan mới:

Index Seek (Orders)
Nested Loop
Index Seek (Users)

Không còn:

Scan
Hash Match
Sort
Key Lookup

Thời gian giảm còn ~40ms.

Logical Reads giảm mạnh.

7. Lỗi Phổ Biến Khi Đọc Execution Plan

7.1 Chỉ nhìn % Cost

% Cost chỉ là ước lượng tương đối. Quan trọng hơn là:

Logical Reads
CPU time
Actual rows

7.2 Không xem Actual Plan

Estimated Plan có thể khác thực tế.

7.3 Bỏ qua Implicit Conversion

Ví dụ:

WHERE UserId = '100'

UserId là INT → SQL convert → không dùng index.

Execution Plan sẽ hiển thị CONVERT_IMPLICIT.

7.4 WHERE không SARGable

Sai:

WHERE YEAR(CreatedDate) = 2024

Đúng:

WHERE CreatedDate >= '2024-01-01'
AND CreatedDate < '2025-01-01'

8. Checklist Phân Tích Execution Plan Chuẩn Production

Khi gặp query chậm:

Có Scan không?
Có Key Lookup không?
Có Sort cost cao không?
Join kiểu gì?
Estimated vs Actual lệch nhiều không?
Có Warning (tam giác vàng) không?
Logical Reads bao nhiêu?

9. Mối Liên Hệ Giữa Index và Execution Plan

Index tốt phải phục vụ:

WHERE
JOIN
ORDER BY
SELECT (Covering)

Sai lầm thường gặp:

Tạo index chỉ cho WHERE
Không quan tâm ORDER BY
Không cover cột select

10. Khi Nào Không Cần Tối Ưu?

Bảng nhỏ (vài nghìn rows)
Query chạy dưới 50ms
Không nằm trong critical path

Tối ưu quá mức có thể làm:

Tăng chi phí write
Tăng kích thước DB
Làm chậm insert/update

11. Tư Duy Senior Khi Nhìn Execution Plan

Một Senior không hỏi:

“Query có index chưa?”

Mà hỏi:

SQL đang làm gì?
Nó đọc bao nhiêu rows?
Join chiến lược có hợp lý không?
Cardinality estimation có chính xác không?
Có cần thay đổi thiết kế dữ liệu không?

Execution Plan không chỉ là công cụ debug, mà là cách bạn hiểu cách SQL Server suy nghĩ.

Kết Luận

Execution Plan là công cụ quan trọng nhất khi tối ưu query trong SQL Server. Nó giúp bạn:

Nhìn thấy database thực sự làm gì
Xác định chính xác điểm nghẽn
Tránh tối ưu mù
Giảm thời gian xử lý từ giây xuống mili giây
Hiểu sâu cách hoạt động của optimizer

Nếu bạn làm backend (.NET, microservices, high traffic API), kỹ năng đọc Execution Plan không phải là tùy chọn – mà là bắt buộc.

Tối ưu đúng không đến từ việc viết query “đẹp”, mà đến từ việc hiểu cách database thực thi query đó.

Nếu bạn muốn, mình có thể viết tiếp phần nâng cao:

Cardinality Estimation
Parameter Sniffing
Query Store
Covering Index chiến lược cho hệ thống lớn