Thiết kế Stored Procedure chống Parameter Sniffing cho hệ thống ERP dài hạn

 

Abstract

Trong các hệ thống ERP vận hành dài hạn, vấn đề hiệu năng không nằm ở một truy vấn đơn lẻ mà ở cách Stored Procedure được thiết kế để thích nghi với sự thay đổi về khối lượng và phân bố dữ liệu theo thời gian. Parameter Sniffing, nếu không được kiểm soát từ giai đoạn thiết kế, sẽ trở thành nguyên nhân gây suy giảm hiệu năng không ổn định và khó chẩn đoán. Bài viết này trình bày các nguyên tắc kiến trúc, mô hình thiết kế Stored Procedure, và chiến lược quản lý Execution Plan nhằm giảm thiểu rủi ro Parameter Sniffing trong hệ thống ERP dữ liệu lớn, hoạt động lâu dài.

---

1. Introduction

Khi hệ thống ERP mới triển khai:

• Dữ liệu ít
• Execution Plan gần như luôn “đúng”
• Parameter Sniffing hiếm khi gây vấn đề

Sau 3–5 năm:

• Bảng hàng chục triệu dòng
• Dữ liệu phân bố lệch nghiêm trọng
• Stored Procedure cũ trở thành “điểm nghẽn”

👉 Vấn đề không phải SQL Server chậm, mà là Stored Procedure không được thiết kế để già đi cùng dữ liệu.

---

2. Nguyên tắc kiến trúc cốt lõi

Nguyên tắc 1: Không tồn tại “Execution Plan tối ưu cho mọi thời điểm”

• Plan đúng hôm nay ≠ đúng năm sau
• Stored Procedure phải chịu được sự thay đổi của dữ liệu

👉 Thiết kế cho độ ổn định, không phải case nhanh nhất.

---

Nguyên tắc 2: Phân loại Stored Procedure ngay từ đầu

Mỗi SP nên được gắn nhãn logic:

Loại SP	Đặc điểm
Online	Chạy liên tục, dữ liệu nhỏ
Batch	Chạy theo đợt, dữ liệu lớn
Report	Truy vấn đọc nhiều
Compute	Tính toán phức tạp

👉 Mỗi loại cần chiến lược chống sniffing khác nhau.

---

3. Mô hình thiết kế 1: Stored Procedure theo “kịch bản dữ liệu”

3.1 Ý tưởng

Không để 1 SP xử lý mọi trường hợp:

IF @RowCount < 100000
    EXEC usp_Process_SmallData ...
ELSE
    EXEC usp_Process_LargeData ...

3.2 Lợi ích dài hạn

• Mỗi SP có Execution Plan riêng
• Dễ tune index
• Ít phụ thuộc Parameter Sniffing

👉 Đây là mô hình được nhiều ERP lớn sử dụng.

---

4. Mô hình thiết kế 2: “Staging + Temp Table”

4.1 Kiến trúc

• Lấy dữ liệu theo điều kiện
• Đổ vào #Temp
• Đánh index theo kịch bản
• Xử lý tiếp

SELECT ...
INTO #Data
FROM LargeTable
WHERE Thang = @Thang;

CREATE INDEX IX_Data ON #Data(Col1);

---

4.2 Vì sao chống Parameter Sniffing tốt?

• SQL Server có statistics thực tế
• Cardinality Estimation chính xác
• Execution Plan ổn định

👉 Đây là vũ khí mạnh nhất cho ERP dữ liệu lớn.

---

5. Mô hình thiết kế 3: SP Orchestrator (Điều phối)

5.1 Tư duy

• 1 SP chính
• Nhiều SP con xử lý chuyên biệt

CREATE PROC usp_Main
AS
BEGIN
    EXEC usp_CollectData
    EXEC usp_Process
    EXEC usp_Aggregate
END

---

5.2 Ưu điểm

• Mỗi SP nhỏ, plan đơn giản
• Dễ kiểm soát sniffing
• Dễ bảo trì

👉 Chia nhỏ logic = chia nhỏ rủi ro sniffing.

---

6. Chiến lược index gắn với thiết kế SP

6.1 Index theo “hành vi truy vấn”, không theo bảng

Sai lầm phổ biến:

“Bảng này cần index gì?”

Đúng phải hỏi:

“SP này truy vấn bảng này như thế nào?”

---

6.2 Index giúp giảm sniffing như thế nào?

• Index tốt → SQL Server ít phải chọn chiến lược “liều”
• Giảm rủi ro plan lệch

👉 Index không loại bỏ sniffing, nhưng giảm hậu quả.

---

7. Quản lý Execution Plan theo vòng đời hệ thống

7.1 Khi nào nên chấp nhận RECOMPILE?

• SP batch
• SP report
• SP chạy hiếm

👉 Không dùng cho SP online lõi.

---

7.2 Query Store & Plan Forcing (định hướng)

• Theo dõi plan theo thời gian
• Phát hiện plan xấu
• Ép plan ổn định khi cần

👉 Phù hợp hệ thống ERP trưởng thành.

---

8. Checklist thiết kế Stored Procedure chống Parameter Sniffing

Trước khi deploy SP:

• SP này thuộc loại nào?
• Dữ liệu có phân bố lệch không?
• Execution Plan có ổn định không?
• Estimated vs Actual Rows có lệch không?
• Có cần tách SP hay staging data không?

👉 Checklist này giúp tránh 80% vấn đề sniffing.

---

9. Case thực tế: ERP 10 năm dữ liệu

Trước:

• 1 SP
• 1 Execution Plan
• Thỉnh thoảng chậm 1–2 phút

Sau:

• Tách SP
• Temp table + index
• Execution Plan ổn định

👉 Thời gian chạy ổn định < 5s trong nhiều năm.

---

10. Kết luận

Parameter Sniffing không phải là lỗi cần “chữa cháy”, mà là một yếu tố cần được đưa vào tư duy thiết kế Stored Procedure ngay từ đầu. Với hệ thống ERP dữ liệu lớn và vận hành dài hạn, việc áp dụng các mô hình thiết kế phù hợp giúp đảm bảo hiệu năng ổn định, dễ mở rộng và giảm đáng kể chi phí bảo trì trong tương lai.

Tổng kết series

Bài	Nội dung
Bài 1	Execution Plan căn bản
Bài 2	Parameter Sniffing
Bài 3	Chiến lược kiểm soát
Bài 4	Thiết kế kiến trúc