Trong SQL Server, hiện tượng Parameter Sniffing là gì và lập trình viên cần xử lý như thế nào khi gặp sự cố hiệu năng do Parameter Sniffing gây ra trong Stored Procedure?

Question

Accepted Answer

Khái niệm Parameter Sniffing
Parameter Sniffing là cơ chế mà SQL Server sử dụng khi biên dịch (compile) một Stored Procedure lần đầu tiên (hoặc khi kế hoạch thực thi bị xóa khỏi plan cache). Lúc này, SQL Server sẽ "nhìn" vào giá trị của các tham số được truyền vào trong lần thực thi đầu tiên đó để xây dựng Execution Plan tối ưu nhất cho giá trị cụ thể đó.
Vấn đề gặp phải
Nếu lần đầu tiên Stored Procedure được gọi với một tham số có lượng dữ liệu rất lớn (hoặc rất ít), Execution Plan được tạo ra sẽ được lưu vào bộ nhớ đệm (Plan Cache). Các lần gọi tiếp theo với tham số có phân phối dữ liệu hoàn toàn khác sẽ tiếp tục sử dụng lại Execution Plan cũ này, dẫn đến hiện tượng quét bảng toàn bộ (Table Scan) không cần thiết hoặc chọn sai loại Index, làm sụt giảm hiệu năng trầm trọng.
Cách khắc phục phổ biến

Sử dụng từ khóa WITH RECOMPILE:
Buộc SQL Server biên dịch lại Stored Procedure cho mỗi lần chạy dựa trên tham số mới.
CREATE PROCEDURE sp_LayDanhSachNhanVien @PhongBanID INT
WITH RECOMPILE
AS
BEGIN
 SELECT * FROM NhanVien WHERE PhongBanID = @PhongBanID;
END
Lưu ý: Không nên lạm dụng cho các Stored Procedure chạy tần suất cao vì tốn tài nguyên CPU cho việc biên dịch liên tục.

Sử dụng biến cục bộ (Local Variables):
Sao chép tham số vào biến cục bộ. SQL Server sẽ không "sniff" giá trị của biến cục bộ mà dựa vào ước tính mật độ thống kê chung (Density Vector).
CREATE PROCEDURE sp_LayDanhSachNhanVien @PhongBanID INT
AS
BEGIN
 DECLARE @ID INT = @PhongBanID;
 SELECT * FROM NhanVien WHERE PhongBanID = @ID;
END

Sử dụng Option (Optimization Hints):
Sử dụng OPTION (RECOMPILE) cho từng câu lệnh cụ thể hoặc OPTION (OPTIMIZE FOR (...)).

Kinh nghiệm thực chiến cho Junior
Khi hệ thống chạy chậm đột ngột ở một Stored Procedure vốn dĩ rất nhanh, hãy nghĩ ngay đến Parameter Sniffing. Kiểm tra Plan Cache và thử xóa cache bằng lệnh DBCC FREEPROCCACHE (môi trường test) để xác định nguyên nhân.

Trong SQL Server, hiện tượng Parameter Sniffing là gì và lập trình viên cần xử lý như thế nào khi gặp sự cố hiệu năng do Parameter Sniffing gây ra trong Stored Procedure?

Khái niệm Parameter Sniffing

Làm Chủ Phỏng Vấn & Nâng Cao Trình Độ Chuyên Nghiệp

Tải Tuyển Tập 100+ Câu Hỏi Phỏng Vấn Chuyên Sâu (PDF)

Cần Mentor Phỏng Vấn Thử 1-1 & Tối Ưu CV?

Kiểm Tra CV Chuẩn ATS Miễn Phí

Câu hỏi sql server liên quan cùng chủ đề