مشکل Parameter Sniffing در SQL Server 2022

مشکل Parameter Sniffing در SQL Server 2022

Parameter Sniffing یکی از مباحث کلیدی در بهینه‌سازی performance در SQL Server است.
این پدیده زمانی رخ می‌دهد که SQL Server از مقدار اولین پارامتری که یک stored procedure یا query parameterized اجرا می‌شود، برای ساختن یک plan execution سراسری استفاده کند.
این plan ممکن است برای برخی مقادیر پارامتر عالی، اما برای برخی دیگر فاجعه‌بار باشد.

در SQL Server 2022، با معرفی ویژگی‌هایی مانند Parameter Sensitive Plan (PSP) Optimization و Intelligent Query Processing (IQP)، رفتار Parameter Sniffing بهبود اساسی یافته، اما همچنان آگاهی از این مشکل ضروری است.

Parameter Sniffing چگونه کار می‌کند؟

وقتی یک پروسیجر با پارامتر فراخوانی می‌شود:

• SQL Server مقدار پارامتر را "بو می‌کشد" (sniff).
• به کمک این مقدار، تخمین cardinality می‌زند.
• یک plan execution بر اساس آن تخمین ساخته می‌شود.

این plan در plan cache ذخیره و برای فراخوانی‌های بعدی (حتی با مقادیر دیگر) استفاده می‌شود.

مثال ساده:

CREATE PROC GetOrders @CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID;

• فراخوانی اول با @CustomerID = 10 (مشتری کم‌تعداد) باعث ایجاد plan با index seek می‌شود.
• فراخوانی دوم با @CustomerID = 5 (مشتری بسیار پُرتعداد) ممکن است نیاز به scan داشته باشد، اما همان plan seek قبلی استفاده می‌شود که بسیار کند است.

علائم و تشخیص مشکل Parameter Sniffing در SQL Server 2022

علائم رفتاری Parameter Sniffing :

• Query کندتر از حد انتظار اجرا می‌شود.
• Performance متغیر بسته به مقدار ورودی.
• بازسازی (recompile) پروسیجر مشکل را موقتاً حل می‌کند.

روش‌های تشخیص:

الف) مشاهده plan cache:

SELECT qs.execution_count,
       qs.total_worker_time/qs.execution_count AS avg_cpu,
       qs.total_elapsed_time/qs.execution_count AS avg_duration,
       qt.text,
       qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE '%YourProc%'
ORDER BY avg_duration DESC;

ب) پیدا کردن queries با multiple plans:

SELECT query_hash, COUNT(DISTINCT plan_handle) AS plan_count
FROM sys.dm_exec_query_stats
GROUP BY query_hash
HAVING COUNT(DISTINCT plan_handle) > 1;

ج) استفاده از Query Store (توصیه قوی در SQL Server 2022):

SELECT query_id, plan_id, avg_duration, query_sql_text
FROM sys.query_store_plan
JOIN sys.query_store_query ON ... 
WHERE is_parameter_sniffing_issue = 1;  -- ستون کمکی

در SQL Server 2022، Query Store به طور خودکار می‌تواند plan با performance بهتر را پیشنهاد دهد.

سناریوی کلاسیک مشکل

جدول Sales با 10 میلیون رکورد، ستون Status با داده‌های skewed:

• مقدار 'Pending' → 1% داده‌ها
• مقدار 'Completed' → 99% داده‌ها

پروسیجر:

CREATE PROC GetSales @Status VARCHAR(20)
AS
SELECT * FROM Sales WHERE Status = @Status;

اگر اول با 'Pending' اجرا شود: plan با Index Seek (عالی برای 1%).

اگر بعداً با 'Completed' اجرا شود: همان Seek استفاده می‌شود که برای 99% داده ضعیف است (باید Scan کند).

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

راهکارهای رفع Parameter Sniffing در SQL

راهکار 1: استفاده از RECOMPILE

CREATE PROC GetSales @Status VARCHAR(20)
WITH RECOMPILE
AS ...

مزیت: هر بار plan تازه ساخته می‌شود.
عیب: overhead کامپایل دوباره.

راهکار 2: OPTION (OPTIMIZE FOR UNKNOWN)

SELECT * FROM Sales WHERE Status = @Status
OPTION (OPTIMIZE FOR UNKNOWN);

SQL Server از توزیع آماری متوسط استفاده می‌کند، نه مقدار خاص.

راهکار 3: OPTION (OPTIMIZE FOR (@Status = 'Pending'))

OPTION (OPTIMIZE FOR (@Status = 'Pending'));

به SQL Server می‌گوییم همیشه فرض کن پارامتر مقدار خاص دارد.

راهکار 4: استفاده از متغیر محلی (Local Variable)

CREATE PROC GetSales @Status VARCHAR(20)
AS
DECLARE @LocalStatus VARCHAR(20) = @Status;
SELECT * FROM Sales WHERE Status = @LocalStatus;

SQL Server مقدار متغیر محلی را sniff نمی‌کند و از cardinality متوسط استفاده می‌کند.
نکته: در SQL Server 2022 این روش کمتر توصیه می‌شود، چون PSP Optimization خودکار این سناریوها را مدیریت می‌کند.

راهکار 5: Disable Parameter Sniffing (در سطح پایگاه داده)

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

 

تأثیر: همه queries از average distribution استفاده می‌کنند. ممکن است برخی queries بدتر شوند.

قابلیت‌های جدید SQL Server 2022 برای حل مشکل

الف) Parameter Sensitive Plan (PSP) Optimization

در SQL Server 2022 (با compatibility level 160)، به طور خودکار برای queries با توزیع نابرابر داده (data skew) چندین plan ذخیره می‌شود.
SQL Server در زمان اجرا تشخیص می‌دهد کدام plan برای مقدار پارامتر مناسب است.

فعال‌سازی: به صورت خودکار فعال است اگر:

Compatibility level = 160

Query Store فعال باشد (حتی در حالت read-only)

مثال: Query Store به صورت خودکار plan‌های جایگزین را ذخیره و بر اساس مقدار پارامتر انتخاب می‌کند.

ب) Automatic Plan Correction

در SQL Server 2022، اگر یک plan regression ناشی از Parameter Sniffing تشخیص داده شود، Query Store می‌تواند به طور خودکار به plan قبلی و بهتر برگردد.

ج) Improved Cardinality Estimation

مدل‌های جدید CE (Cardinality Estimator) در 2022 به سمت multiple independent predicates و better containment assumption حساسیت کمتری به Sniffing دارند.

سناریوی واقعی و حل آن در 2022

سناریو: گزارش فروش ماهانه با تاریخ ورودی.

CREATE PROC MonthlyReport @StartDate DATE, @EndDate DATE
AS
SELECT ProductID, SUM(Amount)
FROM Sales
WHERE OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY ProductID;

دامنه کوچک (1 روز) → Seek/Scan محدود

دامنه بزرگ (1 سال) → Full Scan

راه حل در SQL Server 2022:

کافی است compatibility level 160 و Query Store ON باشد. SQL Server دو plan مجزا ذخیره می‌کند.

برای اطمینان از وجود plan برای دامنه بزرگ، می‌توان query را با یک دامنه بزرگ یک بار اجرا کرد تا plan دوم نیز در cache قرار گیرد.

بهترین روش‌ها برای جلوگیری از Parameter Sniffing

روش	زمان استفاده
SQL Server 2022 به بالا، پیش‌فرض	استفاده از Query Store + Auto Plan Correction
OPTION (RECOMPILE)	Queries بسیار حساس و سریع (کامپایل overhead کم)
OPTIMIZE FOR UNKNOWN	توزیع متوسط قابل قبول باشد
Local Variable	راهکار قدیمی، در 2022 کمتر نیاز است
بازبینی ایندکس‌ها و به‌روزرسانی آمار	پیش‌نیاز هر راهکاری

جمع‌بندی

Parameter Sniffing ذاتاً بد نیست. در بسیاری موارد مفید است. مشکل زمانی ایجاد می‌شود که توزیع داده بسیار نامتعادل باشد.
با SQL Server 2022 و PSP Optimization، بسیاری از این موارد به صورت خودکار مدیریت می‌شوند.
با این حال آگاهی از روش‌های دستی (RECOMPILE، OPTIMIZE FOR، Query Store) همچنان برای معماران دیتابیس ضروری است.

نکته نهایی: همیشه قبل از اعمال هر راهکار، performance فعلی را با Query Store baseline بگیرید و تأثیر تغییرات را در محیط تست با داده‌های واقعی بسنجید.