[SQLSERVER] SQL Server Parameter Sniffing trong Store Procedure

Bài viết hôm nay, mình sẽ nói về vấn đề Sql Parametter Sniffing trong sql server khi sử dụng Store Procedure.

Vậy Sql Parametter Sniffing là gì?

Khi một Stored Procedure được chạy lần đầu tiên, hoặc sau khi giải phóng SP cache, v.v… Bộ xử lý Optimizer trên SQL Server bắt buộc phải tạo ra một execution plan tương ứng cho Stored Procedure. Nó sẽ sử dụng các giá trị của tham số truyền vào, sử dụng các dữ liệu thống kê sẵn có để sinh ra một execution plan. Execution Plan này sẽ được lưu trong sp cache và được sử dụng cho các lần gọi Stored Procedure sau BẤT KỂ tham số truyền vào như thế nào.
Thông thường dữ liệu để thống kê trên Stored Procedure có sự khác biệt không đáng kê, do đó khi một Stored Procedure (SP) chạy tốt với tập dữ liệu này cũng sẽ đáp ứng được khi chạy với tập dữ liệu khác. Tuy nhiên một số trường hợp đặc biệt mà bản thân dữ liệu có sự phân bố không đều, dẫn đến 1 tập dữ liệu tương đối khác biệt với phần còn lại.
Parameter Sniffing rất tai hại nếu lần tạo execution plan đầu tiên mà các tham số truyền vào tương ứng với tập dữ liệu bất thường đó. Lúc này execution plan chỉ được coi là tối ưu với tập dữ liệu này mà hoàn toàn không tối ưu với phần lớn dữ liệu còn lại, dẫn đến hiệu năng thực hiện bị giảm sút nghiêm trọng.

Tình huống:

Một ngày nọ, một thành viên trong nhóm Database của tôi gặp một hiện tượng kì lạ. Stored Procedure mà cậu ấy viết khi chạy trên môi trường test rất tốt, thời gian xấp xỉ 1 giây. Tuy nhiên khi đưa lên hệ thống thật, thì lại chạy ì ạch mất khoảng 30 giây.
Cậu ấy thay hết giá trị vào stored, chạy trên SSMS dưới dạng câu SQL bình thường thì lại chạy tốt. Vò đầu bứt tai một hồi cậu ấy đâm ra nghi ngờ khi sử dụng Stored Procedure và có kế hoạch thay hết Stored đã viết bằng dynamic sql để tăng hiệu năng.

Tình huống giả định

Giả sử trong CSDL ta có 2 table là Article và Category như sau:

Ngoài ra trên table Article ta cũng đánh index cho cột CateID để khi cần tìm kiếm theo điều kiện này sẽ được thực thi nhanh hơn.

Dữ liệu của table Category thì chỉ có 2 dòng dữ liệu với ID là 1 và 2. Tuy nhiên dữ liệu của table Article thì có đến 1000 dòng, và ta giả sử có 999 article thuộc category 1và chỉ có 1 article thuộc category 2.

Kế hoạch thực thi Select Query

Điều gì sẽ xảy ra nếu ta so sánh kế hoạch thực thi của 2 câu query sau:

Kết quả:

=> Như bạn có thể thấy, 2 câu truy vấn này giống hệt nhau, chỉ khác mỗi giá trị của C.ID nhưng có kế hoạch thực thi rất khác nhau. Là do dữ liệu article của chúng ta tập trung hầu hết vào category 1, nên với câu truy vấn thứ nhất thì index ở field CateID hầu như không có ý nghĩa nữa. Tuy nhiên với câu truy vấn thứ hai thì khác, chỉ có mỗi một article thuộc category 2, nên việc dùng index ở field CateID là rất hữu ích. Chính vì vậy SQL Server đã sử dụng kế hoạch thực thi khác nhau cho hai trường hợp này.

Kế hoạch thực thi Stored Procedure

Tương tự như vậy, giả sử ta có stored procedure sau đây:

Kiểm chứng kế hoạch thực thi trong 2 lần thực thi stored procedure với dữ liệu như bên trên. 

DBCC FREEPROCCACHE: dùng để xóa cache kế hoạch thực thi của SQL Server

Rõ ràng ta thấy SQL Server chỉ sử dụng 1 kế hoạch duy nhất cho 2 trường hợp này. Ta thử đổi lại thứ tự 2 lần thực thi stored procedure:

Một lần nữa, ta thấy rằng SQL Server chỉ dùng 1 kế hoạch thực thi cho 2 lần gọi stored procedure này.

Nguyên nhân

Sở dĩ SQL Server chỉ dùng một kế hoạch thực thi cho các lần gọi stored procedure khác nhau là do nó có cơ chế cache lại kế hoạch thực thi. Tức là khi bạn thực thi stored đó lần đầu tiên, nó phải tính toán để đưa ra kế hoạch thực thi tốt nhất đối với tham số vừa truyền vào, và đồng thời ghi cache để lưu giữ lại kế hoạch thực thi này cho các lần gọi stored procedure tiếp theo.

Chính vì vậy, nếu lần đầu tiên bạn thực thi stored procedure với một giá trị tham số hiếm khi xảy ra (trong ví dụ này là tìm các article thuộc category 2) thì kế hoạch thực thi mà nó cache lại sẽ không phải tối ưu cho các lần gọi stored procedure để tìm các article thuộc category 1.

Đến đây thì bạn đã hiểu vì sao stored procedure của mình bị chậm rồi đấy :)

Hướng giải quyết

Có 6 hướng giải quyết sau:

• Sử dụng "WITH RECOMPILE" trong stored procedure
• Sử dụng "OPTION (RECOMPILE)"
• Sử dụng "OPTION (OPTIMIZE FOR)"
• Sử dụng biến thay thế trong stored procedures
• Disable SQL Server Parameter Sniffing at the Instance Level
• Disable Parameter Sniffing for a Specific SQL Server Query

Sử dụng "WITH RECOMPILE" trong stored procedure

Đây không phải là cách tối ưu, đây chỉ là option để yêu cầu SQL Server xây dựng lại kế hoạch thực thi mới mà không sử dụng kế hoạch thực thi từ cache. Điều này sẽ giúp SQL Server tính toán được kế hoạch thực thi tốt nhất ứng với mỗi lần thực thi cụ thể, với từng giá trị tham số khác nhau. Tuy nhiên sẽ làm tăng tải CPU dùng cho việc tính đi tính lại liên tục kế hoạch thực thi mới.

Sử dụng "OPTION (RECOMPILE)"

Tương tự như trên, đây chưa phải là giải pháp tối ưu

Kết quả sau khi đã chỉnh sửa stored procedure bằng một trong 2 cách bên trên

Sử dụng "OPTION (OPTIMIZE FOR)"

Option này cho phép bạn đặt giá trị tham khảo cho parameter để SQL dùng vào việc lập kế hoạch thực thi. Bạn phải chắc chắn rằng giá trị bạn cung cấp là phổ biến để có một kế hoạch thực thi tốt. Tham khảo thêm về cách sự dụng option này tại đây. Nhưng nếu bạn sử dụng SQL Server 2008 hoặc cao hơn, bạn có thể dùng OPTIMIZE FOR UNKNOWN để SQL Server tự quyết định giá trị đó thay bạn.

Kết quả

Sử dụng biến thay thế trong stored procedures

Đây là cách thường được dùng nhất từ trước cho đến năm 2005. Người ta sử dụng một biến cục bộ khác để thay thế parameter truyền vào stored procedure

Kết quả

Disable SQL Server Parameter Sniffing at the Instance Level

Đây không phải là cách tốt, và không được khuyến khích sử dụng, bạn có thể tham khảo thêm ở nguồn bên dưới.

Disable Parameter Sniffing for a Specific SQL Server Query

Sử dụng OPTION (QUERYTRACEON 4136)

Kết quả

Theo http://hoidaplaptrinh.net