IT Blog #7 | Kế hoạch thực thi trong MS SQL Server

IT_Blog 7_Thumbnail

Trước khi đi vào nội dung chính của hôm nay, hãy thử dự đoán đáp án cho một câu hỏi nhỏ: trong hai câu lệnh SQL dưới đây, câu lệnh nào chạy nhanh hơn?

IT_Blog 7_1

Hình 1 – SQL Query 1 and SQL Query 2
 

Ghi chú:

  • payment_done, send_mrc_status, contract_number: là các cột trong bảng Customer_contracts.
  • Giá trị của payment_done: NULL, '0', '1'.
  • Giá trị của send_mrc_status: NULL, '-4', '-3', '-2', '1', '3', '4'.

Đáp án: Cả hai câu lệnh SQL đều trả về gần 1.500.000 bản ghi, nhưng SQL Query 2 (~30s) chạy nhanh hơn SQL Query 1 (~90s) khoảng 3 lần.

Làm sao để biết sự khác biệt giữa hai câu lệnh SQL này?

Đó chính là chủ đề hôm nay: Execution Plan trong MS SQL Server.

1. Execution Plan là gì?

IT_Blog 7_2

Hình 2 – Ví dụ Execution Plan
 

Hãy tưởng tượng một bản đồ hướng dẫn từng bước mà xe ô tô cần để đến đích. Execution Plan cũng tương tự như vậy – nhưng thay vì chỉ đường cho xe, nó chỉ đường cho câu truy vấn đi qua dữ liệu.

Execution Plan mô tả cách SQL Server xử lý câu truy vấn, cung cấp thông tin như:

  • Các thao tác cụ thể: table scan, join, filter, aggregation…
  • Chi phí dự kiến của từng bước → giúp xác định bottleneck.
  • Luồng xử lý dữ liệu → thấy được dữ liệu được đọc, xử lý và xuất ra như thế nào.

Execution Plan gồm 2 loại:


  • Estimated Execution Plan – Như tên gọi, loại Execution Plan này là dự đoán các bước mà database engine sẽ thực hiện khi chạy một câu truy vấn cụ thể. Nó cung cấp thông tin hữu ích về mức độ hiệu quả của query và giúp xác định những điểm nghẽn hiệu năng tiềm ẩn.
  • Actual Execution Plan – Actual Execution Plan được tạo ra sau khi truy vấn đã được thực thi thực tế. Nó hiển thị các bước và thao tác thực sự diễn ra trong quá trình chạy query. Kết quả này có thể giống hoặc khác so với Estimated Execution Plan.

2. Các thành phần chính của Execution Plan

IT_Blog 7_3

Hình 3 – Các thành phần chính của Execution Plan
 

  • Physical Operation: Đây là các operator thực thi thao tác theo logic được chỉ định bởi logical operators. Tất cả physical operators thường là các đối tượng thực hiện một thao tác. Ví dụ: Clustered Index Scan, Index Seek, v.v.
  • Logical Operation: Đây là các operator mô tả phép toán đại số dùng để xử lý truy vấn. Ví dụ: Right Anti Semi Join, Hash Join, v.v.
  • Actual Execution Mode: Đây là mode thực thi thực tế mà processing engine sử dụng khi chạy truy vấn. Ví dụ – Row và Batch.
  • Estimated Execution Mode: Tương tự Actual Execution Mode nhưng hiển thị giá trị ước tính.
  • Storage: Thể hiện cách query optimizer sẽ lưu kết quả được truy xuất từ truy vấn.
  • Number of Rows Read: Tổng số bản ghi được operator đọc từ table index.
  • Actual Number of Rows: Tổng số bản ghi được trả về sau khi áp dụng điều kiện trong WHERE.
  • Actual Number of Batches: Nếu truy vấn chạy ở chế độ batch, giá trị này thể hiện số batch được thực thi để lấy kết quả.
  • Estimated I/O Cost: Chi phí của thao tác đọc/ghi dữ liệu trên result set.
  • Estimated Operator Cost: Không phải chi phí thực tế mà là giá trị tương quan so với các operator khác trong Execution Plan.
  • Estimated CPU Cost: Chi phí CPU mà hệ thống dự tính cần để xử lý thao tác.
  • Estimated Subtree Cost: Chi phí của cây thực thi hiện tại, được đọc từ phải sang trái và từ trên xuống dưới.
  • Number of Executions: Số lần thực thi mà optimizer xử lý trong một batch.
  • Estimated Number of Executions: Tương tự Number of Executions nhưng là giá trị ước tính.
  • Estimated Number of Rows: Số dòng mà optimizer dự đoán sẽ được trả về bởi operator.
  • Estimated Number of Rows to be Read: Số dòng mà optimizer dự đoán sẽ cần được đọc bởi operator.
  • Estimated Row Size: Dung lượng lưu trữ của từng dòng dữ liệu trong operator.
  • Node ID: Số thứ tự được gán tự động dựa trên trình tự operator được gọi trong Execution Plan — đọc từ phải sang trái và từ trên xuống dưới.

Ngoài ra, như bên dưới, có ba thuộc tính cung cấp thêm thông tin về câu truy vấn và đối tượng được tạo Execution Plan:

  • Predicate: Giá trị lấy từ điều kiện WHERE trong câu lệnh SQL.
  • Object: Bảng mà thao tác đang được thực hiện.
  • Output: Các cột được chọn và hiển thị trong kết quả truy vấn.

Nguồn tham khảo: https://www.sqlshack.com/execution-plans-in-sql-server/

3. Các chiến lược cải thiện hiệu năng

Để tăng tốc Execution Plan và nâng cao hiệu suất database, bạn có thể xem xét các hướng tối ưu sau:

  • Indexing Mastery: Đảm bảo bảng có index phù hợp nhằm tăng tốc độ truy xuất dữ liệu.
  • Statistics Updates: Luôn cập nhật statistics để bộ tối ưu hóa truy vấn có thể đưa ra ước lượng chính xác và chọn Execution Plan tối ưu.
  • Query Optimization: Rà soát và tối ưu câu truy vấn để SQL Engine có thể tạo ra Execution Plan hiệu quả hơn.
  • Regular Analysis: Thường xuyên theo dõi và phân tích Execution Plan để phát hiện cơ hội tối ưu.

4. Giải mã kết quả:
IT_Blog 7_4

Hình 4 - Execution Plan of SQL Query 1

IT_Blog 7_5

Hình 5 – Execution Plan of SQL Query 2
 

Về câu hỏi nhỏ ở phần đầu bài, dưới đây là sự khác biệt giữa hai SQL Query:

  • SQL Query 1: Execution Plan chỉ chạy một node với cost 99%.
  • SQL Query 2: Execution Plan chạy nhiều node và nhiều tiến trình song song. Query này có Execution Plan tối ưu hơn Query 1, nên thời gian thực thi nhanh hơn dù cùng điều kiện dữ liệu.

Tổng kết

  • Execution Plan trong SQL Server là “kiến trúc sư thầm lặng” đứng sau hiệu năng của các truy vấn.
  • Khi hiểu rõ cách chúng hoạt động, phân tích các thành phần và áp dụng chiến lược tối ưu, bạn có thể nâng hiệu suất database lên một tầm cao mới.
  • Phân tích Execution Plan thường xuyên là chìa khóa giúp hệ thống phản hồi nhanh và hoạt động hiệu quả.
  • Khi bạn bắt đầu hành trình này, việc nắm vững Execution Plan chính là chìa khóa mở ra toàn bộ sức mạnh xử lý của SQL Server.

Bài viết được thực hiện bởi Software Developer – Phan Quốc Việt