راهنمای جامع Parameter Sensitive Plan در SQL Server

در SQL Server یک‌سری مشکلات وجود دارد که اگر تجربه‌شان نکرده باشید، شاید اصلاً متوجه نشوید، چرا سیستم توی ساعات خاصی کند می‌شود، ولی وقتی یک‌بار سراغش بیاید، دقیقاً همان کابوس همیشگی را می‌بینید:
یک SELECT ساده بعضی وقت‌ها در چند میلی‌ثانیه جواب می‌دهد و چند دقیقه بعد ناگهان ۱۲ ثانیه طول می‌کشد.

اگر کمی جستجو کرده باشید، اسمش را حتماً شنیده‌اید: Parameter Sniffing و نسخه‌ جدیدتر و مهار شد‌ه‌تر در SQL Server 2022 یعنی Parameter Sensitive Plan (PSP). وقتی یک کوئری دارید که روی مجموعه‌های داده‌ کوچک و بزرگ رفتار متفاوت دارد، SQL Server ممکن است برای یک حالت، پلن خوب بسازد اما همان پلن برای حالت دیگر فاجعه شود. این همان چیزی است که باعث می‌شود گاهی سیستم “حالت خوب” داشته باشد و گاهی “حالت افتضاح”.

نسخه ۲۰۲۲ با PSP آمده است، که این مشکل را «قابل‌مدیریت» و «قابل‌پیش‌بینی» کند. اما واقعیت این است که شناخت دقیقش و استفاده‌ی درست از آن، تفاوت بین یک سیستم پایدار و یک سیستم غیرقابل‌ اعتماد را تعیین می‌کند.
در این مقاله از دید کاملاً عملی جلو می‌رویم. نه توضیحات تکراری، نه شعار.
هدف این است که بعد از خواندن متن بدانید:

• PSP دقیقاً چه مشکلی را حل می‌کند.
• چه زمانی به‌جای کمک، دردسر می‌شود.
• چطور تشخیص دهی کوئری‌ات PSP لازم دارد.
• چطور با Plan Guides، Hints و Indexing درست، وضعیت را بهینه کنید.
• چطور رفتار Query Store را برای PSP کنترل کنید.
• و در نهایت چطور یک چک‌لیست واقعی برای پروژه‌ها داشته باشید.

پس شروع کنیم.

PSP در عمل چه دردی را دوا می‌کند؟

فرض کند یک جدول Orders دارید، ۹۵٪ سفارش‌ها مربوط به یک کشور هستند. ۵٪ باقی‌مانده پخش‌اند.
اگر کوئری تو به این شکل باشد:

SELECT *
FROM Orders
WHERE Country = @country;

برای مقدار Country=’US’ یک پلن لازم داری (مثلاً Scan چون ردیف‌ها زیاد است)
و برای Country=’BE’ یک پلن کاملاً متفاوت (مثلاً Seek چون تعداد کم است).

اما SQL Server فقط یک پلن می‌سازد و همان یک پلن را Cache می‌کند. اگر اولین بار مقدار «کوچک» وارد شود، Seek ساخته می‌شود؛ ولی وقتی چند میلیون ردیف US را بخواهی، اجرای Seek روی حجم عظیم رکورد، سیستم را له می‌کند.
یا برعکس.

PSP با یک ایده‌ی ساده مشکل را کاهش داده:
به‌جای یک Execution Plan، سه گروه پلن می‌سازد که بسته به توزیع داده انتخاب می‌شود. اما این پایان ماجرا نیست. چون هنوز هم:

• توزیع داده باید واقعاً skewed باشد.
• آمارها به‌روز باشند.
• Index درست وجود داشته باشد.
• و الگوریتم دسته‌بندی PSP برای دیتای تو مناسب باشد.

در غیر این صورت، PSP «به ظاهر» روشن است ولی کمک زیادی نمی‌کند.

 PSP دقیقاً چطور کار می‌کند؟

SQL Server 2022 سه bucket می‌سازد:

1. Small
2. Medium
3. Large

هر bucket می‌تواند پلن مخصوص خودش را داشته باشد. SQL Server از روی Histogram و توزیع داده، تشخیص می‌دهد که مقدار پارامتر داخل کدام گروه قرار می‌گیرد.

وقتی کوئری اجرا شود:

• اگر مقدار پارامتر داخل Small باشد، پلن Small اجرا می‌شود.
• اگر Medium باشد، پلن Medium
• و اگر Large باشد، پلن Large

این یعنی دیگر مجبور نیستیم یک پلن بد برای دو حالت بسیار متفاوت قبول کنیم. اما توجه کن که PSP Plan Explosion ایجاد نمی‌کند. حداکثر سه پلن داریم و این باعث می‌شود، هم کارایی حفظ شود، هم حافظه‌ Plan Cache کنترل‌ شده باشد.

چه زمانی PSP فعال می‌شود و چه زمانی نه؟

PSP فقط روی کوئری‌هایی فعال می‌شود که:

1. پارامتر داشته باشند.
2. توزیع داده نامتوازن باشد.
3. براساس Threshold داخلی SQL Server «گران» باشند.
4. امکان ساخت پلن‌های متفاوت برایشان وجود داشته باشد.

اگر یکی از این شرایط برقرار نباشد، PSP بی‌اثر است.

مثلاً:

• توزیع داده خیلی یکنواخت باشد.
• Index مناسب وجود نداشته باشد.
• Predicate تو SARGable نباشد.
• از توابع روی ستون استفاده کرده باشید.
• Cross Apply دارید.
• یا کوئری خیلی سبک باشد.

در چنین حالتی، همچنان همان رفتار سنتی Parameter Sniffing را می‌بینید.

چطور تشخیص دهیم مشکل ما PSPیی است یا چیز دیگر؟

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

مرحله ۱. Compare Slow Plan vs Fast Plan

اگر Execution Plan به‌وضوح «دو رفتار» دارد (مثلاً یک‌بار Seek و یک‌بار Scan)، احتمال PSP بالاست.

مرحله ۲. درصد Cardinality تفاوت شدید دارد؟

مثلاً:

• برای مقدار A تعداد ردیف ۱۰۰ تا است.
• برای مقدار B تعداد ردیف ۵ میلیون است.

این یعنی Data Skew شدید.

مرحله ۳. Query Store چه می‌گوید؟

به‌سادگی می‌توانی ببینی که Plan Variance شدید است یا خیر.

مرحله ۴. تست ساده با RECOMPILE

اگر با RECOMPILE کوئری همیشه سریع شود، مشکل ۹۰٪ Parameter Sniffing/PSP است.

چطور PSP را تنظیم و بهینه کنیم؟

PSP خودش بد نیست.
بیشتر مشکل از این است که:

• Index بد است.
• آمارها قدیمی‌اند.
• Predicate غیربهینه است.

راهکارهای مستقیم مدیریت PSP

۱. تنظیم Index بر اساس توزیع واقعی

اگر ۶۰٪ کوئری‌ها برای مقدار «کوچک» و ۴۰٪ برای مقدار «بزرگ» است، باید Index کمک‌کننده برای هر دو حالت ساخته شود.
گاهی یک Index Covering ساده کل مشکل را حل می‌کند.

۲. استفاده کنترل‌شده از OPTION (RECOMPILE)

نه همیشه، نه همه‌جا. فقط روی کوئری‌هایی که:

• به‌ندرت اجرا می‌شوند.
• هزینه‌ی Generate Plan برایشان زیاد نیست.
• رفتارشان با مقادیر مختلف کاملاً متفاوت است.

۳. Query Hints برای Override رفتار PSP

مثلاً:

OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))

یا حالت برعکس:

OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

۴. استفاده از OPTIMIZE FOR UNKNOWN در سناریوهای خاص

اگر مقدار پارامتر پیش‌بینی‌پذیر نیست و PSP هم جواب نمی‌دهد:

OPTION (OPTIMIZE FOR UNKNOWN)

۵. Plan Forcing با Query Store

به‌ویژه زمانی که می‌خواهی پلن موقتاً ثابت بماند تا بتوانی علت اصلی را پیدا کنی.

PSP چه زمانی به دردسر تبدیل می‌شود؟

بعضی وقت‌ها دوگانه بودن پلن‌ها به خودی خود خوب نیست.
چند سناریو که مشکل ایجاد می‌کند:

• توزیع داده بیش از حد متغیر است و Bucketing همیشه دقیق نیست.
• مقدارهایی داریم که در مرز دامنه‌ها می‌افتند.
• Index چسبیده به ستون درست طراحی نشده است.
• Auto Update Statistics دیر اجرا می‌شود.
• Query Store پلن‌ها را می‌کوبد و وضعیت حدس‌زنی بهم می‌ریزد.

در این لحظه‌ها به‌جای اینکه PSP کمک کند، باعث می‌شود تیم تصور کند “SQL Server رفتار غیرقابل‌ پیش‌بینی دارد”.

بهترین راهکار در چنین شرایطی این است:

• یا عمداً PSP را خاموش کنید.
• یا پلن صحیح را با Query Store قفل کنید.
• یا Index مناسب بسازید تا رفتار پلن‌ها خطی‌تر شود.

مثال واقعی از پروژه‌ای که بدون PSP غیرقابل‌استفاده بود.

برای اینکه موضوع روشن‌تر شود، یک مثال از دنیای واقعی بیاورم. در یک سیستم سفارش‌گیری، جدولی داشتیم با حدود ۹۰۰ میلیون رکورد. ستون CustomerType توزیعِ به‌شدت نامتوازن داشت:

• ۸۷٪ رکوردها: B2C
• ۱۳٪ رکوردها: B2B

اما رفتار کوئری‌ها دقیقاً برعکس بود:

• بیشترین Query روی B2B بود (مصرف گزارش‌ها)
• ولی هنگام شروع سیستم، اولین اجرای Stored Procedure همیشه با مقدار B2C انجام می‌شد.

نتیجه چه می‌شد؟

• SQL Server پلن را بر اساس B2C می‌ساخت.
• و بقیه‌ی روز کوئری‌ها روی B2B مجبور می‌شدند با یک پلن کاملاً اشتباه اجرا شوند.
• تا جایی که زمان اجرای کوئری از ۰.۴ ثانیه می‌رسید به ۱۸ ثانیه
• CPU سیستم نزدیک ۹۰٪ می‌رفت بالا
• کاربران گزارش اعتراض می‌کردند.
• تیم توسعه فکر می‌کرد مشکل از DAX است.

در SQL Server 2019 تنها راه منطقی این بود که:

• یا یک RECOMPILE بگذاریم.
• یا پلن را با Query Store قفل کنیم.
• یا با یک Case روی پارامتر پلن را شکستیم (کاری که برنامه‌نویس‌ها متنفرند)

اما در SQL Server 2022، PSP این رفتار را به حالت پایدار تبدیل کرد.

وقتی PSP را فعال کردیم:

• سه پلن ساخته شد.
• پلن مخصوص B2B به‌طور خودکار توسط engine انتخاب شد.
• نوسان Performance تقریباً صفر شد.
• نیاز به RECOMPILE کامل حذف شد.
• و فشار CPU نصف شد.

این همان نقطه‌ای است که PSP واقعاً ارزش خودش را نشان می‌دهد.

چطور PSP را فعال یا غیرفعال کنیم؟

چک کردن اینکه PSP فعال است یا نه

SELECT name, value, value_for_secondary
FROM sys.configurations
WHERE name = 'parameter_sensitive_plan_optimize_for_secondary';

و همچنین:

SELECT * 
FROM sys.database_scoped_configurations
WHERE name = 'PARAMETER_SENSITIVE_PLAN';

در اغلب موارد، PSP به‌صورت پیش‌فرض فعال است. اما اگر در سازمان تو نسخه‌ی خاصی از SQL Server نصب شده باشد یا تنظیمات قبلی دستکاری شده باشد، ممکن است غیرفعال باشد.

فعال‌سازی دستی PSP در سطح دیتابیس

ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SENSITIVE_PLAN = ON;

غیرفعال‌سازی دستی PSP برای یک کوئری خاص

اگر کوئری رفتار غیرقابل‌پیش‌بینی ایجاد می‌کند:

SELECT ...
FROM ...
WHERE ...
OPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_OPTIMIZATION'));

این قابلیت مهم است. چون بعضی از کوئری‌ها واقعاً با یک پلن ثابت عملکرد بهتری دارند.

بررسی یک پلن PSP در Execution Plan

برای اینکه بفهمید کوئری واقعاً PSP است، باید در Execution Plan خط زیر را ببینی:

Parameter Sensitive Plan (PSP)

همچنین زیر بخش‌هایی مانند:

• PSP Cache Object
• PSP Bucket
• PSP Variation

اگر چنین چیزی وجود نداشت، یعنی PSP هنوز برای کوئری فعال نشده یا لازم نبوده، این مهم است، چون خیلی‌ها فکر می‌کنند صرفاً با فعال بودن PSP، همه کوئری‌ها تحت تأثیر قرار می‌گیرند. اما واقعیت این است که PSP فقط برای کوئری‌هایی فعال می‌شود که engine تشخیص دهد واقعاً ارزشش را دارد.

الگوریتم تصمیم‌گیری PSP

این بخش کمی عمیق‌تر است، اما دانستنش کمک می‌کند بهتر بتوانی رفتار کوئری‌ها را تحلیل کنید.

الگوریتم PSP سه مرحله اصلی دارد:

۱. Bucketing

SQL Server توزیع histogram را بررسی می‌کند و سه دسته می‌سازد.
معمولاً چیزی شبیه این:

• Bucket 1: ۰ تا ۰.۱٪ کاردینالیتی
• Bucket 2: ۰.۱٪ تا ۱۰٪
• Bucket 3: بزرگ‌تر از ۱۰٪

این مقادیر دقیق نیستند، اما تقریباً رفتار موتور به همین شکل است. اگر ستون تو توزیع متعادل داشته باشد، PSP اصلاً فعال نمی‌شود.

۲. Plan Optimization

برای هر bucket یک پلن بهینه می‌سازد. اما اگر دو bucket نتیجه یکسان بدهند، دوباره سه پلن نمی‌سازد؛ فقط یکی را Cache می‌کند.

۳. Runtime Switching

در لحظه اجرا، مقدار پارامتر خوانده می‌شود و موتور انتخاب می‌کند که کوئری طبق پلن bucket کوچک، متوسط یا بزرگ اجرا شود. این قسمت نسبتاً شبیه Adaptive Join است، اما با رفتار کش‌محور.

خطاهای رایج تیم‌ها درباره PSP

متأسفانه خیلی‌ها PSP را اشتباه می‌فهمند.
چند خطای رایج:

۱. PSP را با Parameter Sniffing اشتباه می‌گیرند.

در ۲۰۲۲، PSP قرار نیست Parameter Sniffing را کامل حذف کند. فقط کمک می‌کند رفتار قابل‌پیش‌بینی‌تر شود.

۲. فکر می‌کنند PSP جایگزین Index است.

اگر Index بد باشد، PSP هم معجزه نمی‌کند.
گاهی یک Index ساده مشکل را کاملاً حل می‌کند.

۳. فقط به Execution Plan نگاه می‌کنند.

در حالی‌که PSP بیشتر از اینکه در Plan دیده شود، در Query Store قابل بررسی است.

۴. پلن را با Query Store Force می‌کنند.

اجازه بده موتور PSP کار خودش را انجام دهد. تنها زمانی پلن را قفل کن که مشکل کاملاً غیرعادی باشد.

بهترین روش‌ها که در پروژه‌های مختلف جواب داده‌اند.

۱. Histogram باید دقیق باشد.

وقتی Statistics قدیمی می‌شوند، PSP بی‌اثر می‌شود.
پس:

ALTER DATABASE SCOPED CONFIGURATION 
SET AUTO_UPDATE_STATISTICS = ON;

حتماً روشن باشد.

۲. از Filtered Index برای مقادیر خاص استفاده کن

وقتی تعداد کم مهم است:

CREATE INDEX IX_Orders_Country_BE
ON Orders(Country)
WHERE Country = 'BE';

این کار باعث می‌شود دسته Small یک پلن واقعاً متفاوت بسازد.

۳. اگر داده به‌شدت skewed است، Columnstore همیشه انتخاب خوبی نیست.

چون Columnstore رفتار PSP را خطی می‌کند و گاهی پلن‌های متفاوت معنی ندارد.

۴. Stored Procedure را طوری طراحی کن که SARGable باشد.

تابع روی ستون نگذارید.
Case غیرضروری روی ستون نگذارید.
پارامتر را نگذار تبدیل implicit شود.

۵. رفتار PSP را در Query Store زیر نظر بگیرید.

به‌خصوص:

sys.query_store_plan
sys.query_store_runtime_stats
sys.query_store_query

وقتی سه پلن مختلف داری، نتیجه معمولاً بهتر است. اگر فقط یک پلن دارید، احتمالاً PSP غیر فعال است.

چک‌لیست عملی که در تمام پروژه‌ها برای PSP استفاده می‌کنیم.

این چک‌لیست بسیار مهم است. اگر فقط همین را اجرا کنی، ۸۰٪ نتیجه را می‌گیرید.

چک‌لیست تشخیص

• آیا رفتار کوئری بین مقادیر مختلف ۵ تا ۱۰ برابر متفاوت است؟
• آیا ستون ورودی واقعاً skewed است؟
• آیا Index مخصوص هر حالت داریم؟
• آیا Statistics به‌روز است؟
• آیا Query Store چند پلن برای یک QueryID ساخته؟
• آیا RECOMPILE مشکل را حل می‌کند؟

چک‌لیست راهکار

• PSP در سطح دیتابیس روشن باشد
• Predicate SARGable باشد.
• فیلتر غیرلازم حذف شود.
• یک Filtered Index برای حالت Small ساخته شود.
• اگر لازم است پلن را Force کنید.
• اگر رفتار غیرقابل‌پیش‌بینی است PSP را غیرفعال کنید و پلن ثابت بدهید.

جمع‌بندی

PSP در SQL Server 2022 ابزار بسیار قدرتمندی است، اما فقط زمانی نتیجه می‌دهد که:

• دیتای تو واقعاً نامتوازن باشد.
• Indexها درست طراحی شده باشند.
• Query Store فعال و سالم باشد.
• Statistics همیشه آپدیت باشد.

اگر این شرایط برقرار باشد، PSP می‌تواند سیستم را از یک رفتار «پرنوسان» به یک رفتار «قابل‌اعتماد» تبدیل کند.

نتیجه‌گیری

PSP شاید کوچک به‌نظر برسد، اما اثر بزرگی در سیستم دارد.

اگر بخواهم همه این مسیر را در چند جمله خلاصه کنم، حرف اصلی این است:
وقتی حجم داده بالا می‌رود و توزیع آن نامتوازن می‌شود، رفتار کوئری‌ها دیگر قابل پیش‌بینی نیست. SQL Server 2022 با Parameter Sensitive Plan این مشکل قدیمی را هدف گرفته و در عمل آن را تا حد زیادی برطرف می‌کند.

اما PSP تنها زمانی اثر واقعی دارد که:

• Indexها درست انتخاب شده باشند.
• Statistics همیشه تازه بماند.
• Query Store فعال باشد.
• کوئری‌ها SARGable طراحی شده باشند.
• و تیم توسعه بداند با پلن‌های مختلف چه رفتاری باید انتظار داشته باشد.

اگر این اصول رعایت شود، PSP می‌تواند یک سیستم ناپایدار، کند و پرنوسان را تبدیل کند به یک سرویس قابل‌اعتماد که رفتار کوئری‌هایش قابل پیش‌بینی است.

این چیزی است که در پروژه‌های واقعی بارها تکرار شده:
کاهش فشار CPU، ثبات بیشتر، سرعت بهتر و حذف RECOMPILEهای غیرضروری.

تماس و مشاوره با لاندا

اگر در سرور شما هم رفتارهای عجیب رخ می‌دهد؛ گاهی یک پارامتر ساده باعث می‌شود کوئری سریع باشد و پنج دقیقه بعد کاملاً کند شود؛ یا اگر Execution Plan مدام عوض می‌شود و نمی‌دانید کدام رفتار طبیعی است و کدام خطرناک …

این دقیقاً همان نقطه‌ای است که ما در لاندا آماده کمک هستیم.

تحلیل کامل رفتار Query Store، تشخیص حالات PSP، طراحی Index درست و در نهایت ساخت یک پلن پایدار برای هر پارامتر.

اگر می‌خواهید یک‌بار برای همیشه خیال خودتان و تیم را از این نوسان‌ها راحت کنید:
برای مشاوره PSP و Plan Forcing با مشاوران لاندا تماس ✆ بگیرید تا وضعیت واقعی سرور بررسی شود.