بریم سراغ یکی از چالش‌های خیلی مهم SQL Server به اسم Parameter Sniffing.

---

🔹 Parameter Sniffing یعنی چی؟

وقتی یک Stored Procedure برای اولین بار اجرا می‌شه، SQL Server میاد و بر اساس مقدار پارامتر ورودی همون اجرای اول یک Execution Plan می‌سازه و اون Plan رو Cache می‌کنه.

از اون به بعد هر بار که SP با پارامترهای مختلف اجرا بشه، همون Plan کش‌شده استفاده می‌شه—even اگر اون Plan برای داده‌های جدید بهینه نباشه.

---

🔹 یک مثال واقعی

فرض کن جدول Orders داری:

CREATE TABLE Orders (
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    Amount DECIMAL(10,2)
);

-- ایندکس روی CustomerID
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

و Stored Procedure زیر:

CREATE PROCEDURE dbo.GetOrdersByCustomer
  @CustomerID INT
AS
BEGIN
  SELECT OrderID, OrderDate, Amount
  FROM Orders
  WHERE CustomerID = @CustomerID;
END;

---

📌 سناریو:

• مشتری A فقط 5 سفارش داره.
• مشتری B یک میلیون سفارش داره.

وقتی اولین بار SP رو اجرا کنی مثلا با @CustomerID = A، SQL Server می‌بینه که فقط 5 ردیف برمی‌گرده → Plan می‌سازه که از Index Seek استفاده کنه (خیلی سریع برای داده کم).
حالا دفعه بعد اجرا کنی با @CustomerID = B، SQL Server همون Plan (Index Seek) رو استفاده می‌کنه ولی چون خروجی خیلی زیاده (۱ میلیون ردیف)، این Plan بد عمل می‌کنه → کارایی خیلی پایین میاد.

برعکس هم ممکنه: اگر بار اول روی مشتری بزرگ اجرا بشه، Plan با Index Scan یا Table Scan ساخته می‌شه و برای مشتری کوچک غیر بهینه می‌شه.

این می‌شه مشکل Parameter Sniffing.

---

🔹 راه‌حل‌های حرفه‌ای

✅ 1. استفاده از OPTION (RECOMPILE)

می‌گی SQL Server هر بار Plan رو دوباره بسازه:

CREATE PROCEDURE dbo.GetOrdersByCustomer
  @CustomerID INT
AS
BEGIN
  SELECT OrderID, OrderDate, Amount
  FROM Orders
  WHERE CustomerID = @CustomerID
  OPTION (RECOMPILE);
END;

🔸 مزیت: همیشه بهترین Plan ساخته می‌شه.
🔸 عیب: هزینه CPU بالاتر چون Plan کش نمی‌شه.

---

✅ 2. استفاده از WITH RECOMPILE هنگام اجرا

به جای تغییر SP می‌تونی فقط موقع اجرا بزنی:

EXEC dbo.GetOrdersByCustomer @CustomerID = 42 WITH RECOMPILE;

اینطوری فقط همون بار Plan جدید ساخته می‌شه.

---

✅ 3. استفاده از OPTIMIZE FOR

می‌گی Plan همیشه بر اساس یک مقدار خاص ساخته بشه:

CREATE PROCEDURE dbo.GetOrdersByCustomer
  @CustomerID INT
AS
BEGIN
  SELECT OrderID, OrderDate, Amount
  FROM Orders
  WHERE CustomerID = @CustomerID
  OPTION (OPTIMIZE FOR (@CustomerID = 1));
END;

🔸 برای مواردی خوبه که یک مقدار خاص بیشترین استفاده رو داره (مثلا مشتری خاصی).

---

✅ 4. استفاده از OPTIMIZE FOR UNKNOWN

می‌گی SQL Server Plan رو بسازه طوری که انگار مقدار پارامتر معلوم نیست → می‌ره سراغ آمار کلی (Histogram):

CREATE PROCEDURE dbo.GetOrdersByCustomer
  @CustomerID INT
AS
BEGIN
  SELECT OrderID, OrderDate, Amount
  FROM Orders
  WHERE CustomerID = @CustomerID
  OPTION (OPTIMIZE FOR UNKNOWN);
END;

🔸 خروجی Plan معمولاً میانه و متعادل هست (نه خیلی خوب برای همه، ولی قابل‌قبول برای اکثر حالات).

---

✅ 5. شکستن SP به چند نسخه (Dynamic SQL یا Branching)

گاهی بهترین کار اینه که شرط بذاری:

CREATE PROCEDURE dbo.GetOrdersByCustomer
  @CustomerID INT
AS
BEGIN
  IF @CustomerID < 1000
  BEGIN
      -- برای مشتری‌های کوچک
      SELECT OrderID, OrderDate, Amount
      FROM Orders
      WHERE CustomerID = @CustomerID
      OPTION (RECOMPILE);
  END
  ELSE
  BEGIN
      -- برای مشتری‌های بزرگ
      SELECT OrderID, OrderDate, Amount
      FROM Orders
      WHERE CustomerID = @CustomerID
      OPTION (OPTIMIZE FOR UNKNOWN);
  END
END;

یا حتی از Dynamic SQL استفاده کنی.

---

🔹 خلاصه

• Parameter Sniffing وقتی پیش میاد که Plan ساخته‌شده برای بعضی پارامترها خوب باشه، ولی برای بقیه افتضاح.
• راه‌حل‌ها بسته به نیاز فرق می‌کنن:
  • OPTION (RECOMPILE) → بهترین Plan برای هر اجرا (ولی CPU بیشتر).
  • OPTIMIZE FOR UNKNOWN → Plan متعادل برای همه.
  • OPTIMIZE FOR (value) → برای یک مقدار خاص بهینه‌سازی.
  • یا شکستن SP / Dynamic SQL برای کنترل بیشتر.

---

🔥 توی مصاحبه‌ها معمولا می‌پرسن:
«اگر SP داری که روی دیتابیس خوب جواب می‌ده، ولی روی محیط Production خیلی کند می‌شه و دلیلش هم Execution Plan هست، چه می‌کنی؟»
جواب درست همینه: بررسی Parameter Sniffing و استفاده از یکی از راه‌حل‌های بالا.