Tối ưu truy vấn SQL Server: 7 bước đo và xử lý đúng nguyên nhân

Một câu lệnh chạy chậm có thể xuất phát từ đọc quá nhiều trang dữ liệu, ước lượng số dòng sai, kiểu dữ liệu không tương thích, blocking, thiếu bộ nhớ cấp cho truy vấn hoặc một execution plan vừa bị thay đổi. Vì vậy, thêm index theo cảm tính đôi khi chỉ chuyển chi phí từ lúc đọc sang lúc ghi — và tạo thêm việc bảo trì.

Bài viết này trình bày quy trình tối ưu truy vấn SQL Server theo hướng có thể đo, kiểm chứng và đưa vào vận hành. Ví dụ dùng T-SQL, nhưng tư duy “đo trước, sửa đúng nút thắt, đo lại” cũng áp dụng tốt cho nhiều hệ quản trị cơ sở dữ liệu quan hệ.

1. Chốt đường cơ sở trước khi chỉnh sửa

Đừng bắt đầu bằng việc viết lại câu SQL. Trước hết hãy ghi nhận thời gian phản hồi, CPU, số lần đọc logic, số dòng trả về và tần suất thực thi trong điều kiện đủ gần với thực tế. Một truy vấn 50 ms nhưng chạy hàng triệu lần có thể đáng ưu tiên hơn truy vấn 5 giây chỉ chạy cuối tháng.

SET STATISTICS IO, TIME ON;

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
  AND OrderDate >= @FromDate
  AND OrderDate <  @ToDate;

SET STATISTICS IO, TIME OFF;

Trong môi trường kiểm thử, hãy bật Actual Execution Plan. Khác với estimated plan, actual plan có thông tin khi truy vấn đã chạy như số dòng thực tế, mức sử dụng tài nguyên và cảnh báo runtime. Không nên chạy thử truy vấn nặng trên production nếu chưa đánh giá tác động.

Nguyên tắc: cùng tham số, cùng tập dữ liệu đại diện và cùng chỉ số đo trước–sau. Nếu không, “nhanh hơn” có thể chỉ là hiệu ứng cache hoặc khác biệt tải hệ thống.

2. Viết điều kiện SARGable để SQL Server tìm kiếm bằng index

SARGable nghĩa là điều kiện có thể được bộ tối ưu biến thành thao tác tìm kiếm hiệu quả trên cấu trúc truy cập. Đặt hàm lên cột thường khiến SQL Server phải tính giá trị cho nhiều dòng và có thể chuyển từ Index Seek sang Scan.

Không nên:

WHERE YEAR(OrderDate) = 2026

Nên dùng khoảng nửa mở:

WHERE OrderDate >= '20260101'
  AND OrderDate <  '20270101'

Cách viết này vừa thân thiện với index vừa tránh lỗi ở cột có phần thời gian. Cũng cần kiểm tra implicit conversion: so sánh hai cột hoặc tham số khác kiểu dữ liệu có thể làm mất khả năng seek và khiến ước lượng cardinality kém chính xác.

3. Chỉ lấy những cột và dòng thực sự cần

SELECT * làm tăng I/O, băng thông, bộ nhớ cấp phát và khiến ứng dụng phụ thuộc không cần thiết vào cấu trúc bảng. Nó còn có thể biến một index vốn “cover” truy vấn thành không đủ, buộc SQL Server quay lại bảng hoặc clustered index để lấy thêm cột.

-- Thay vì SELECT *
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID;

Hãy phân trang ở phía cơ sở dữ liệu, lọc càng sớm càng tốt và tránh trả hàng trăm nghìn dòng cho giao diện chỉ hiển thị vài chục dòng. Tuy nhiên, không nên dùng TOP để che một điều kiện nghiệp vụ thiếu chính xác.

4. Thiết kế index theo workload, không theo mẹo truyền miệng

Index phù hợp thường đặt các cột dùng cho điều kiện bằng và join ở phần key; cột phục vụ range và sắp xếp được cân nhắc theo thứ tự; các cột chỉ cần trả về có thể nằm trong INCLUDE. Ví dụ dưới đây có thể phù hợp với truy vấn lọc theo khách hàng và khoảng ngày:

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount);

Đây không phải công thức áp dụng máy móc. Mỗi index tiêu tốn dung lượng, bộ nhớ đệm và chi phí cho INSERT, UPDATE, DELETE. Hãy đối chiếu execution plan, tần suất truy vấn và workload ghi; kiểm tra index trùng lặp trước khi tạo mới. Gợi ý “missing index” trong plan là đầu mối để điều tra, không phải lệnh triển khai tự động.

5. Đọc execution plan theo nguyên nhân, không săn biểu tượng đắt nhất

Chi phí phần trăm trong graphical plan là ước lượng tương đối, không phải thời gian đo thực tế. Khi phân tích, nên ưu tiên:

• Actual Rows và Estimated Rows: chênh lệch lớn thường gợi ý vấn đề statistics, phân phối dữ liệu hoặc tham số.
• Scan và Seek: scan không mặc định là xấu; đọc phần lớn bảng nhỏ đôi khi là lựa chọn hợp lý.
• Key Lookup: có thể rẻ khi chỉ vài dòng nhưng rất tốn khi lặp hàng trăm nghìn lần.
• Sort/Hash spill: dữ liệu tràn xuống tempdb thường liên quan memory grant hoặc ước lượng số dòng.
• Warnings: implicit conversion, spill, missing statistics và các cảnh báo runtime cần được xem trong ngữ cảnh.

6. JOIN không chậm; JOIN sai grain mới nguy hiểm

Không nên mặc định thay JOIN bằng subquery. Bộ tối ưu có thể biến nhiều cách viết logic thành plan tương đương. Điều cần kiểm tra là khóa join có cùng kiểu dữ liệu, các cột được hỗ trợ bởi index phù hợp và quan hệ một–một, một–nhiều hay nhiều–nhiều có đúng với grain của kết quả.

SELECT o.OrderID, c.CustomerName, o.TotalAmount
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
  ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= @FromDate
  AND o.OrderDate <  @ToDate;

Nếu join làm số dòng tăng bất thường, hãy kiểm tra khóa không duy nhất hoặc điều kiện join thiếu — đừng dùng DISTINCT như một miếng vá. DISTINCT có thể thêm Sort/Hash mà vẫn che khuất lỗi mô hình dữ liệu.

7. Theo dõi hồi quy bằng Query Store

Plan cache chỉ giữ trạng thái hiện tại và có thể bị loại bỏ. Query Store lưu lịch sử câu truy vấn, execution plan và thống kê runtime theo khoảng thời gian, nhờ đó DBA có thể nhận ra truy vấn vừa hồi quy sau thay đổi plan, statistics hoặc index.

Với SQL Server 2022, Query Store được bật mặc định cho cơ sở dữ liệu mới; các phiên bản cũ cần kiểm tra cấu hình. Việc force một plan cũ có thể là biện pháp khôi phục nhanh, nhưng vẫn phải tìm nguyên nhân gốc và theo dõi sau thay đổi. Tương tự, OPTION (RECOMPILE) hay hint không nên được rải đại trà: chúng có đánh đổi về CPU biên dịch, khả năng tái sử dụng plan và vận hành lâu dài.

Checklist triển khai an toàn

Kết luận

Tối ưu SQL Server không phải cuộc thi viết câu lệnh ngắn nhất. Quy trình đáng tin cậy là: xác định truy vấn quan trọng, đo đúng chỉ số, đọc actual execution plan, sửa nguyên nhân nhỏ nhất rồi kiểm thử lại với workload đại diện. Index, SARGable predicate và Query Store đều là công cụ; chất lượng quyết định nằm ở cách chúng ta dùng bằng chứng để lựa chọn.

Tài liệu tham khảo

• Microsoft Learn — Analyze an actual execution plan
• Microsoft Learn — Indexes in SQL Server
• Microsoft Learn — Monitor performance by using Query Store