Trong bài đăng này, tôi đang chia sẻ về Tham số Sniffing của SQL Server. Tôi sẽ thảo luận, tại sao đôi khi trình tối ưu hóa truy vấn không chọn được một kế hoạch truy vấn thích hợp.
Một ngày nọ, tôi nhận thấy rằng một trong những quy trình được lưu trữ trong sản xuất của tôi không trả lại hiệu suất như mong đợi và trước hai ngày, quy trình được lưu trữ tương tự vẫn hoạt động tốt.
Vào thời điểm đó, tôi không biết về Parameter Sniffing và bắt đầu xem xét vấn đề này.
Tôi thấy rằng trước hai ngày có tổng số 25000 bản ghi và sau đó là tổng số 10,00,00 bản ghi.
Tôi kết luận rằng các bản ghi tăng nhanh, nhưng không tăng lên mười lần, và nó chỉ tăng ba lần, và thậm chí tôi đã thử nghiệm quy trình lưu trữ này cho 10,00,00,00 bản ghi.
Bây giờ là lúc để biết về Parameter Sniffing:
Nhiều khi trình tối ưu hóa truy vấn chọn kế hoạch truy vấn cũ để thực thi vì nó lưu trữ kế hoạch này vào bộ nhớ đệm cho các truy vấn thường xuyên chạy.
Bây giờ, điều gì đã xảy ra khi truy vấn cũ đã tạo bảng quét cho tập hợp bản ghi đầu tiên và sau đó, cùng một tham số được yêu cầu thay đổi để quét chỉ mục vì số lượng bản ghi tăng lên.
Tôi kiểm tra kế hoạch thực thi truy vấn và nhận thấy rằng trong tình huống của tôi, trình tối ưu hóa truy vấn sử dụng kế hoạch truy vấn cũ thay vì tạo kế hoạch truy vấn mới.
Trình tối ưu hóa truy vấn đang sử dụng một kế hoạch truy vấn cũ từ bộ đệm truy vấn.
Bây giờ, đây được gọi là sự cố Đánh hơi tham số.
Đối với các bạn, điều này rất quan trọng để biết tại sao điều này lại xảy ra.
Trình tối ưu hóa truy vấn không phải lúc nào cũng chọn kế hoạch truy vấn cũ, nhưng nhiều lần do lưu lượng truy cập hoặc tải, trình tối ưu hóa truy vấn chọn một kế hoạch từ bộ nhớ cache truy vấn.
Là một DBA, bạn nên theo dõi tất cả các chỉ mục và cách sử dụng của nó để xác định loại vấn đề này.
Giải pháp SQL Server cho việc xem xét thông số :
Ba lựa chọn cho giải pháp này:
- lựa chọn 1: PHỤC HỒI
- Lựa chọn 2: TỐI ƯU CHO GIÁ TRỊ
- Lựa chọn 3: OPTIMIZE FOR UNKNOWN
lựa chọn 1 : PHỤC HỒI
PHỤC HỒI, means forcing SQL Server to recompile stored procedure or statement at every execution. Bạn có thể đặt gợi ý biên dịch lại trên quy trình hoặc câu lệnh được lưu trữ.
Mã mẫu cho thủ tục và câu lệnh được lưu trữ.
| 1 2 3 4 5 6 7 8 | /*Stored Procedure with Recomplie option*/ CREATE PROCEDURE dbo.usp_gettestdata @testid INT WITH RECOMPILE AS /*Select statement with Recomplie option*/ SELECT * FROM dbo.tbl_testdata OPTION (RECOMPILE); |
Lựa chọn 2 : TỐI ƯU CHO GIÁ TRỊ
As a DBA, you can find a particular set of values which require executing under the best execution plan so you can set OPTIMIZE for a hint for this value. Nhưng các giá trị và dữ liệu thay đổi nhanh chóng trong hệ thống cơ sở dữ liệu, vì vậy điều này chỉ dành cho những giá trị hoặc dữ liệu không thường xuyên thay đổi.
Câu lệnh mẫu với OPTIMIZE cho Gợi ý:
| 1 2 3 4 5 6 7 | CREATE PROCEDURE dbo.usp_EmployeeData @EmpID INT AS SELECT EmployeName, EmployeeDOB, EmployeeClass FROM dbo.tbl_EmployeeDetails WHERE EmpID= @EmpID OPTION (OPTIMIZE FOR (@EmpID=26)); |
Lựa chọn 3 : TỐI ƯU CHO GIÁ TRỊ
Về cơ bản, bạn có thể đặt gợi ý UNKNOWN cho trình tối ưu hóa truy vấn để tạo một kế hoạch thực thi chung dựa trên dữ liệu thống kê. Điều này có thể lại được tạo ra bởi sự cố dò tìm tham số, nhưng bạn vẫn có thể sử dụng điều này cho các truy vấn không thường xuyên chạy.
| 1 2 3 4 5 6 7 | CREATE PROCEDURE dbo.usp_EmployeeData @EmpID INT AS SELECT EmployeName, EmployeeDOB, EmployeeClass FROM dbo.tbl_EmployeeDetails WHERE EmpID= @EmpID OPTION (OPTIMIZE FOR UNKNOWN); |