راهنمای جامع Plan Guideها و کنترل برنامه اجرایی در SQL Server

دامنه این راهنمای جامع

این مقاله نقشه کامل خانواده Plan Guides در SQL Server است. هدف آن حل مسئله «کنترل Optimization Hint و رفتار کامپایل بدون دست‌کاری مستقیم متن برنامه یا کد فروشنده» و ایجاد ارتباط روشن میان گزینه‌های پیکربندی، اشیای مدیریتی و Queryهای تشخیصی است.

مخاطبان اصلی DBAهای Performance، پشتیبانان نرم‌افزارهای بسته و متخصصان Query Tuning هستند. پیش‌نیاز، شناخت T-SQL، دسترسی آزمایشگاهی و توانایی ثبت Baseline است. در پایان می‌توانید اعضای این خانواده را دسته‌بندی کنید، تفاوت کاربرد آن‌ها را تشخیص دهید و برای هر مورد به آموزش مستقل بروید.

این خانواده دقیقاً 6 موضوع فرزند دارد و لینک هر موضوع فقط در همین مجموعه ارائه شده است. شماره مجموعه بخشی از عنوان یا Slug نیست و ترتیب معرفی مطابق داده ورودی حفظ شده است.

تعریف مجموعه و جایگاه آن

Plan Guides مجموعه‌ای از قابلیت‌های SQL Server است که برای کنترل Optimization Hint و رفتار کامپایل بدون دست‌کاری مستقیم متن برنامه یا کد فروشنده به‌کار می‌رود.

هسته فنی این مجموعه بر مفاهیم OBJECT Guide، SQL Guide، TEMPLATE Guide، Plan Cache، Query Hint، sys.plan_guides استوار است. انتخاب یک عضو بدون شناخت رابطه آن با دیگر اجزا می‌تواند به تنظیم ناقص، مشاهده اشتباه یا نتیجه‌ای کوتاه‌مدت منجر شود.

نقشه مجموعه نشان می‌دهد اعضای Plan Guides چگونه از تعریف و پیکربندی به مشاهده Runtime، تحلیل هزینه و تصمیم اصلاحی متصل می‌شوند.

دسته‌بندی اجزا و مسیر مطالعه

1. آموزش sp_create_plan_guide برای اعمال Hint بدون تغییر کد

sp_create_plan_guide متن Query یا Object را با Hint مشخص پیوند می‌دهد تا Optimizer هنگام تطبیق دقیق، راهنمای تعریف‌شده را اعمال کند.

آموزش کامل و مثال‌های عملی sp_create_plan_guide

2. آموزش sp_create_plan_guide_from_handle از روی Plan Cache

sp_create_plan_guide_from_handle یک Plan Guide را از روی Plan Cache و Handle مشخص می‌سازد تا برنامه اجرایی یا Hint مشاهده‌شده حفظ شود.

آموزش کامل و مثال‌های عملی sp_create_plan_guide_from_handle

3. آموزش sp_control_plan_guide برای فعال‌سازی، غیرفعال‌سازی و حذف

sp_control_plan_guide چرخه عمر Plan Guide را با عملیات ENABLE، DISABLE، DROP و DROP ALL کنترل می‌کند.

آموزش کامل و مثال‌های عملی sp_control_plan_guide

4. آموزش sys.plan_guides و ممیزی Plan Guideهای SQL Server

sys.plan_guides کاتالوگ Plan Guideهای پایگاه داده را با نوع، Scope، متن تطبیق، Hint و وضعیت فعال نمایش می‌دهد.

آموزش کامل و مثال‌های عملی sys.plan_guides

5. مقایسه Plan Guideهای OBJECT، SQL و TEMPLATE در SQL Server

سه نوع OBJECT، SQL و TEMPLATE محدوده و روش تطبیق متفاوتی دارند؛ انتخاب نادرست سبب عدم Match یا اثرگذاری گسترده‌تر از انتظار می‌شود.

آموزش کامل و مثال‌های عملی Plan Guide Types: OBJECT and SQL and TEMPLATE

6. مدیریت Plan Guide با ENABLE، DISABLE، DROP و DROP ALL

عملیات ENABLE، DISABLE، DROP و DROP ALL برای کنترل وضعیت و حذف Plan Guideها استفاده می‌شوند و هرکدام اثر عملیاتی متفاوتی بر کامپایل‌های بعدی دارند.

آموزش کامل و مثال‌های عملی Actions: ENABLE and DISABLE DROP and DROP ALL

جدول مقایسه موضوعات

موضوع یا تابع	نوع	کاربرد اصلی / نکته مهم	لینک آموزش کامل
sp_create_plan_guide	STORED_PROCEDURE	sp_create_plan_guide متن Query یا Object را با Hint مشخص پیوند می‌دهد تا Optimizer هنگام تطبیق دقیق، راهنمای تعریف‌شده را اعمال کند.…	مطالعه آموزش کامل
sp_create_plan_guide_from_handle	STORED_PROCEDURE	sp_create_plan_guide_from_handle یک Plan Guide را از روی Plan Cache و Handle مشخص می‌سازد تا برنامه اجرایی یا Hint مشاهده‌شده حفظ شود.…	مطالعه آموزش کامل
sp_control_plan_guide	STORED_PROCEDURE	sp_control_plan_guide چرخه عمر Plan Guide را با عملیات ENABLE، DISABLE، DROP و DROP ALL کنترل می‌کند.…	مطالعه آموزش کامل
sys.plan_guides	DMV_OR_VIEW	sys.plan_guides کاتالوگ Plan Guideهای پایگاه داده را با نوع، Scope، متن تطبیق، Hint و وضعیت فعال نمایش می‌دهد.…	مطالعه آموزش کامل
Plan Guide Types: OBJECT and SQL and TEMPLATE	GENERAL_TOPIC	سه نوع OBJECT، SQL و TEMPLATE محدوده و روش تطبیق متفاوتی دارند؛ انتخاب نادرست سبب عدم Match یا اثرگذاری گسترده‌تر از انتظار می‌شود.…	مطالعه آموزش کامل
Actions: ENABLE and DISABLE DROP and DROP ALL	GENERAL_TOPIC	عملیات ENABLE، DISABLE، DROP و DROP ALL برای کنترل وضعیت و حذف Plan Guideها استفاده می‌شوند و هرکدام اثر عملیاتی متفاوتی بر کامپایل‌های بعدی دارند.…	مطالعه آموزش کامل

این جریان نشان می‌دهد برای Plan Guides ابتدا مسئله و Scope تعیین می‌شود، سپس عضو مناسب انتخاب، تغییر اعمال و خروجی با View یا DMV مرتبط کنترل می‌شود.

شش مثال ترکیبی و قابل اجرا

مثال ترکیبی 1: کنترل مرحله 1 در Plan Guides

این مثال بخشی از زنجیره تصمیم Plan Guides را پوشش می‌دهد و برای ساخت Baseline یا تأیید وضعیت استفاده می‌شود. آن را با نام اشیا و Permission محیط مقصد هماهنگ کنید.

SELECT name, scope_type_desc, is_disabled FROM sys.plan_guides;

شاخص	خروجی نمونه
Metric_1415_1	وضعیت نمونه برای مرحله 1
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 1: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

مثال ترکیبی 2: کنترل مرحله 2 در Plan Guides

SELECT name, query_text, hints FROM sys.plan_guides WHERE is_disabled = 0;

شاخص	خروجی نمونه
Metric_1415_2	وضعیت نمونه برای مرحله 2
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 2: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

مثال ترکیبی 3: کنترل مرحله 3 در Plan Guides

EXEC sys.sp_control_plan_guide N'DISABLE', N'Guide_Name';

شاخص	خروجی نمونه
Metric_1415_3	وضعیت نمونه برای مرحله 3
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 3: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

مثال ترکیبی 4: کنترل مرحله 4 در Plan Guides

EXEC sys.sp_control_plan_guide N'ENABLE', N'Guide_Name';

شاخص	خروجی نمونه
Metric_1415_4	وضعیت نمونه برای مرحله 4
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 4: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

مثال ترکیبی 5: کنترل مرحله 5 در Plan Guides

SELECT plan_handle, execution_count FROM sys.dm_exec_query_stats ORDER BY execution_count DESC;

شاخص	خروجی نمونه
Metric_1415_5	وضعیت نمونه برای مرحله 5
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 5: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

مثال ترکیبی 6: کنترل مرحله 6 در Plan Guides

-- پس از تأیید Change: EXEC sys.sp_control_plan_guide N'DROP', N'Guide_Name';

شاخص	خروجی نمونه
Metric_1415_6	وضعیت نمونه برای مرحله 6
Decision	ادامه، اصلاح یا Rollback بر اساس Baseline

نکته فنی مثال 6: نتیجه را در کنار تغییرات همزمان Instance تحلیل کنید و از یک مشاهده منفرد نتیجه‌گیری قطعی نکنید.

سناریوهای واقعی

در سناریوی سازمانی، Plan Guides معمولاً برای یک Query منفرد انتخاب نمی‌شود؛ بلکه بخشی از طراحی ظرفیت، SLO و Change Management است. ابتدا Workloadها بر اساس اهمیت و رفتار طبقه‌بندی می‌شوند، سپس ابزار مناسب برای کنترل یا مشاهده هر دسته انتخاب می‌شود.

سناریوی دوم، عیب‌یابی پس از رشد بار است. در این حالت تیم باید میان مشکل طراحی داده، تنظیم Instance، الگوی اتصال و محدودیت منابع تفکیک قائل شود. اعضای Plan Guides شواهد لازم برای این تفکیک را فراهم می‌کنند اما جایگزین تحلیل ریشه‌ای نیستند.

نکته مهم در طراحی خانواده

هیچ عضو Plan Guides را به‌صورت جدا از وابستگی‌ها و بدون Rollback در Production اجرا نکنید. برخی تغییرات فقط پس از Reconfigure، Compile جدید، Restart یا اتصال جدید دیده می‌شوند؛ بنابراین زمان مشاهده باید با مدل اثر همان عضو هماهنگ باشد.

اشتباهات رایج

انتخاب ابزار بر اساس نام و نه Scope واقعی اثر.
نداشتن Baseline و مقایسه با بازه بار نامشابه.
ترکیب همزمان چند تغییر و ناممکن شدن تشخیص علت.
نادیده گرفتن Permission، Edition یا تفاوت نسخه.
حذف یا Disable بدون ثبت وابستگی و مسیر بازگشت.

Performance Considerations

در Plan Guides معیار اصلی فقط زمان پاسخ نیست. CPU، حافظه، Compile، صف درخواست، تعداد Session و هزینه نگهداری باید همزمان دیده شوند. یک بهبود محلی ممکن است فشار را به بخش دیگری منتقل کند.

برای تحلیل معتبر، Query Store یا Snapshotهای DMV را در بازه‌های همسان نگه دارید و تغییرات Deployment را کنار داده Performance ثبت کنید. Polling سنگین خود می‌تواند اندازه‌گیری را منحرف کند.

Best Practices

برای هر تغییر یک فرضیه قابل آزمون بنویسید.
عضو مناسب را از جدول مقایسه انتخاب و آموزش مستقل آن را مطالعه کنید.
تغییر را کوچک، قابل برگشت و زمان‌بندی‌شده نگه دارید.
تعریف کاتالوگی و رفتار Runtime را با هم کنترل کنید.
پس از موفقیت، Runbook و مالک نگهداری را ثبت کنید.

پنل تصمیم نهایی برای Plan Guides خطاهای طراحی را با مسیر اندازه‌گیری، کنترل ریسک و انتخاب Best Path مقایسه می‌کند.

سؤالات متداول

برای شروع کار با Plan Guides چه پیش‌نیازی لازم است؟

پیش از اجرا، نسخه و Edition، سطح دسترسی، وضعیت پایگاه داده و یک محیط آزمایشی همسان با Production را بررسی کنید. برای Plan Guides ثبت Baseline اولیه باعث می‌شود تغییر واقعی از نوسان عادی جدا شود.

چگونه صحت پیکربندی Plan Guides را بررسی کنیم؟

تعریف کاتالوگی را با نمای Runtime مقایسه کنید، سپس یک سناریوی کنترل‌شده اجرا و نتیجه را در بازه زمانی مشخص ثبت کنید. اتکا به یک Snapshot برای قضاوت درباره Plan Guides کافی نیست.

Plan Guides در چه پروژه‌هایی ارزش تجاری بیشتری ایجاد می‌کند؟

در سامانه‌هایی که تأخیر، پایداری و تفکیک بارکاری مستقیماً بر درآمد یا SLA اثر دارد، Plan Guides می‌تواند ارزش بیشتری ایجاد کند؛ البته نتیجه باید با شاخص قابل اندازه‌گیری تأیید شود.

چه زمانی هزینه نگهداری Plan Guides از منفعت آن بیشتر می‌شود؟

وقتی حجم عملیات پایین، تیم فاقد مهارت نگهداری یا مسیر بازگشت نامشخص است، پیچیدگی Plan Guides ممکن است توجیه نداشته باشد. تصمیم باید بر هزینه کل مالکیت استوار باشد.

Plan Guides با روش جایگزین در SQL Server چه تفاوتی دارد؟

روش جایگزین معمولاً Scope، هزینه اجرا و میزان کنترل متفاوتی دارد. مقایسه درست باید روی Workload واقعی، Plan یا مصرف منابع و نه صرفاً زمان یک Query انجام شود.

برای پیاده‌سازی حرفه‌ای Plan Guides چه خدماتی لازم است؟

تحلیل Workload، طراحی آزمایش، پیاده‌سازی مرحله‌ای، مستندسازی، آموزش تیم و پایش پس از انتشار اجزای اصلی خدمت حرفه‌ای هستند؛ اجرای مستقیم در Production بدون این زنجیره پرریسک است.

رایج‌ترین خطای عملیاتی در Plan Guides چیست؟

خطای رایج، اعمال تنظیم بدون اندازه‌گیری وضعیت پایه و بدون کنترل وابستگی‌هاست. در Plan Guides ابتدا Scope را محدود کنید و هر تغییر را با یک معیار موفقیت و یک شرط توقف همراه سازید.

اثر Plan Guides بر Performance چگونه اندازه‌گیری می‌شود؟

شاخص مناسب به موضوع بستگی دارد، اما زمان پاسخ، CPU، Memory، تعداد اجرای موفق، صف انتظار و تغییر Plan از معیارهای رایج‌اند. مقایسه باید در پنجره بار مشابه انجام شود.

بهترین روش مستندسازی و Rollback برای Plan Guides چیست؟

نام اشیا، دلیل تغییر، مقدار قبل و بعد، مالک تصمیم، زمان اعمال، Query اعتبارسنجی و فرمان بازگشت را در Runbook ثبت کنید تا Plan Guides به تنظیمی ناشناخته تبدیل نشود.

Plan Guides با کدام نسخه‌های SQL Server سازگار است؟

قابلیت دقیق می‌تواند بین نسخه‌ها و محیط‌های Azure تفاوت داشته باشد. مستندات همان نسخه را بررسی و Syntax را روی محیط Test اجرا کنید؛ از تعمیم رفتار نسخه جدید به سرور قدیمی پرهیز شود.

سؤالات مصاحبه

چگونه تشخیص می‌دهید Plan Guides واقعاً روی Workload اثر گذاشته است؟
اگر پس از اعمال Plan Guides وضعیت بدتر شد، ترتیب عیب‌یابی شما چیست؟
چه تفاوتی میان تعریف کاتالوگی و وضعیت Runtime در Plan Guides وجود دارد؟
برای جلوگیری از تغییرات ناخواسته در Plan Guides چه کنترل‌هایی می‌گذارید؟
چه زمانی تصمیم می‌گیرید Plan Guides را حذف یا به روش دیگری مهاجرت دهید؟

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

فهرست اعضای خانواده و نقش هرکدام را مشخص کنید.
Baseline را قبل از تغییر ثبت کنید.
Permission و سازگاری نسخه را بررسی کنید.
لینک آموزش مستقل عضو انتخابی را مطالعه کنید.
Query اعتبارسنجی و Rollback را آماده کنید.
نتیجه و هزینه جانبی را در Runbook بنویسید.

جمع‌بندی و مسیر ادامه

Plan Guides یک مجموعه ابزار مرتبط است، نه یک تنظیم جادویی. مسیر درست از تعریف مسئله، انتخاب عضو مناسب، آزمایش کنترل‌شده و مشاهده Runtime عبور می‌کند. برای ادامه، یکی از آموزش‌های زیر را بر اساس مسئله واقعی خود انتخاب کنید.

خدمات برنامه‌نویسی و پایگاه داده

برنامه‌نویسی در اصفهان؛ قبول سفارش‌های برنامه‌نویسی و پایگاه داده با شماره 09131253620. انجام پروژه، آموزش برنامه‌نویسی و آموزش SQL Server با رویکرد حرفه‌ای و قابل پشتیبانی.

مجموعه‌ای معتبر با سابقه فعالیت حرفه‌ای از سال ۱۳۷۵ شمسی تاکنون در طراحی سامانه‌های تحت وب، وب‌سایت، پایگاه داده و راهکارهای نرم‌افزاری فعالیت می‌کند.

برای سفارش پروژه‌های جدید با ایتا، واتساپ و تماس مستقیم: +989131253620 ارتباط بگیرید یا صفحه تماس با ما را ببینید.

راهنمای جامع Plan Guideها و کنترل برنامه اجرایی در SQL Server | آموزش تخصصی SQL Server