و سپس «افزودن به صفحه اصلی» ضربه بزنید
و سپس «افزودن به صفحه اصلی» ضربه بزنید
چرا Query Optimizer همیشه بهترین تصمیم را نمی‌گیرد؟
در SQL Server، قلب هر عملیات اجرایی، Query Optimizer است؛ مغزی که از بین هزاران مسیر ممکن برای اجرای یک Query، یکی را انتخاب می‌کند تا سریع‌ترین نتیجه حاصل شود. اما همیشه بهترین تصمیم گرفته نمی‌شود.
شرایطی مانند تغییر آمار (Statistics)، تفاوت در حجم داده یا استفاده از پارامترهای مختلف باعث می‌شود Query Optimizer در اجرای مجدد همان Query، طرح اجرایی متفاوتی (Execution Plan) تولید کند. این تغییر ناگهانی گاهی منجر به افت شدید عملکرد، افزایش CPU و حتی قفل شدن سیستم می‌شود.
در اینجا مفاهیمی مانند Plan Forcing و Plan Freezing وارد می‌شوند. ابزارهایی قدرتمند برای DBAها که اجازه می‌دهند رفتار Query Optimizer را تا حدی “ثابت” یا “هدایت” کنند.
در این مقاله، با نگاهی فنی و کاربردی، از SQL Server 2016 تا نسخه ۲۰۲۵ بررسی می‌کنیم که چگونه می‌توان با استفاده از Query Store ،Hints و تکنیک‌های پیشرفته Plan Control، عملکرد پایدار و قابل‌پیش‌بینی به‌دست آورد.
مفهوم Execution Plan و نحوه انتخاب آن
Execution Plan چیست؟
Execution Plan نقشه‌ای است که SQL Server برای اجرای Query می‌سازد. این نقشه تعیین می‌کند:
- از چه ایندکسی استفاده شود.
- داده‌ها به چه ترتیبی Join شوند.
- و چه الگوریتمی (Nested Loop ،Merge Join ،Hash Join و …) اجرا گردد.
دو نوع Plan اصلی داریم:
- Estimated Execution Plan: قبل از اجرا، بر اساس تخمین آمار
- Actual Execution Plan: پس از اجرا، با داده‌های واقعی
Query Optimizer چگونه Plan را انتخاب می‌کند؟
Optimizer از مدل Cost-Based Optimization استفاده می‌کند؛ یعنی برای هر Query چند طرح ممکن را می‌سازد، هزینه تقریبی هر کدام را محاسبه کرده و کم هزینه‌ترین را انتخاب می‌کند. اما این مدل همیشه دقیق نیست. تغییرات کوچک در داده یا پارامتر می‌تواند باعث شود Optimizer Plan اشتباهی انتخاب کند.
چرا Plan تغییر می‌کند؟ (Plan Regression Problem)
یکی از مشکلات رایج در SQL Server این است که همان Query در زمان‌های مختلف با Plan متفاوتی اجرا می‌شود. این پدیده را Plan Regression می‌نامند.
دلایل متداول آن عبارتند از:
- تغییر در Statistics (مثلاً Auto Update Statistics)
- تغییر در Schema یا ایندکس‌ها
- Parameter Sniffing (بهینه‌سازی بر اساس اولین پارامتر)
- تغییر در Server Memory Grant یا Cardinality Estimator
- ارتقاء نسخه SQL Server
به همین دلیل، مایکروسافت از SQL Server 2016 به بعد ابزارهایی را معرفی کرد تا DBA بتواند کنترل بیشتری روی انتخاب Plan داشته باشد:
- Plan Freezing (USE PLAN Hint)
- Plan Forcing (Query Store)
Plan Forcing چیست؟
Plan Forcing قابلیتی است که به شما اجازه می‌دهد SQL Server را مجبور کنید برای اجرای Query خاصی از Plan مشخصی استفاده کند. حتی اگر Optimizer پیشنهاد دیگری داشته باشد.
مبنای کار
از SQL Server 2016 به بعد، قابلیت Query Store معرفی شد که اطلاعات کامل Queryها، Planها، و کارایی هر Plan را ذخیره می‌کند. DBA می‌تواند با استفاده از Query Store، Plan مورد نظر را “Force” کند تا همان Plan در آینده هم استفاده شود.
ساختار Query Store
Query Store سه بخش اصلی دارد:
- Plans: طرح‌های مختلف ثبت‌شده برای یک Query
- Runtime Stats: زمان اجرا، I/O ،CPU
- Forced Plans: Planهایی که DBA عمداً قفل کرده است
مثال کاربردی
فرض کنید Query زیر دو Plan مختلف تولید کرده است:
SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID;
Plan اول سریع‌تر است و می‌خواهیم همیشه از آن استفاده شود.
می‌توانیم از Query Store استفاده کنیم:
EXEC sp_query_store_force_plan @query_id = 52, @plan_id = 78;
از این لحظه به بعد، SQL Server همان Plan را اجبارا اجرا می‌کند.
Plan Freezing چیست؟
Plan Freezing در واقع “قفل کردن” Plan در سطح Session یا Query است. برخلاف Plan Forcing که از Query Store استفاده می‌کند، در Plan Freezing معمولاً از Query Hintها یا USE PLAN استفاده می‌شود.
نمونه کد
فرض کنید Execution Plan فعلی را استخراج کرده‌ایم و می‌خواهیم همیشه همین Plan استفاده شود:
SELECT *
FROM Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (USE PLAN N'<ShowPlanXML>');
SQL Server از Plan مشخص‌شده در XML استفاده می‌کند و Optimizer را از ساخت Plan جدید باز می‌دارد.
پیشنهاد مطالعه: SQL Hints در SQL Server ابزار کنترل هوشمند رفتار Query Optimizer برای عملکرد بهینه
تفاوت کلیدی با Plan Forcing
| ویژگی | Plan Forcing | Plan Freezing |
|---|---|---|
| ابزار پیاده‌سازی | Query Store | Query Hint (USE PLAN) |
| محدوده تأثیر | سراسری برای Query | فقط همان دستور |
| قابلیت مانیتورینگ | بله، در Query Store | خیر |
| پایداری پس از Restart | بله | خیر |
| سطح کنترل | منطقی | جزئی و سخت‌گیرانه |
مزایا و معایب
مزایای Plan Forcing
ثبات در عملکرد Query
بازگرداندن سریع Plan قبلی در صورت Regression
قابل مانیتور و مدیریت از طریق Query Store UI
مناسب برای Production
معایب Plan Forcing
- در صورت تغییر ساختار جدول یا ایندکس، Plan ممکن است نامعتبر شود.
- نیاز به نظارت مداوم بر Query Store دارد.
مزایای Plan Freezing
- کنترل دقیق بر Plan خاص
- مفید برای تست و دیباگ
- مستقل از Query Store
معایب Plan Freezing
- نگهداری سخت‌تر (در صورت تغییر ساختار)
- عدم سازگاری با تغییرات دیتابیس
- نیاز به دانش عمیق از XML Plan Structure
کاربردهای عملی
۱. رفع Regression پس از ارتقاء SQL Server
پس از ارتقاء از SQL Server 2019 به ۲۰۲۵، ممکن است Optimizer جدید Plan متفاوتی انتخاب کند.
می‌توانید Plan قبلی را با Query Store “Force” کنید تا پایداری حفظ شود.
EXEC sp_query_store_force_plan @query_id = 150, @plan_id = 302;
۲. کنترل Parameter Sniffing
در Queryهایی که رفتارشان با پارامتر متفاوت است، می‌توانید بهترین Plan را انتخاب و “Freeze” کنید تا از نوسان عملکرد جلوگیری شود.
۳. تست Performance در محیط UAT
Plan Freezing برای آزمایش دقیق تفاوت بین دو Execution Plan در محیط آزمایشی بسیار مفید است.
ابزارهای مدیریتی در SQL Server 2025
Query Store UI
در SSMS 2025، بخش Query Store → Regressed Queries مستقیماً Planهای کندتر را شناسایی کرده و Plan قدیمی را پیشنهاد می‌دهد.
می‌توانید با یک کلیک گزینه “Force Plan” را فعال کنید.
DMVهای جدید
در نسخه ۲۰۲۵، DMV جدیدی برای مانیتور Plan Forcing اضافه شده است:
SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1;
همچنین می‌توان با DMV زیر، وضعیت فعلی را بررسی کرد:
SELECT query_id, plan_id, force_failure_count
FROM sys.query_store_plan
WHERE is_forced_plan = 1;
مقایسه Plan Forcing با سایر روش‌های کنترل Optimizer
| روش | توضیح | مناسب برای |
|---|---|---|
| Plan Forcing | کنترل از طریق Query Store | تولید |
| Plan Freezing (USE PLAN) | قفل Plan خاص | تست |
| Query Hint (OPTIMIZE FOR) | کنترل رفتار پارامتر | Dev/Test |
| Plan Guides | اعمال Hint بدون تغییر Query | ERPها یا نرم‌افزارهای خارجی |
| Optimizer Fix Level (Trace Flag) | کنترل نسخه CE | تنظیمات کل‌سیستم |
Best Practices برای Plan Stability
- همیشه Query Store را فعال کنید (Read-Write Mode)
- Planهای مهم را مستند و مانیتور کنید.
- Plan Forcing را موقت در نظر بگیرید تا زمانی که Root Cause مشخص شود.
- از Plan Freezing فقط در محیط Test استفاده کنید.
- بعد از هر Update یا Schema Change ،Planهای Forced را بررسی کنید.
- از DMV و Alert برای نظارت خودکار روی Plan Failures بهره ببرید.
سناریو عملی: نجات Query کند با Plan Forcing
فرض کنید Query زیر پس از Update آمار کند شده است:
SELECT ProductID, SUM(Quantity)
FROM Sales.Orders
WHERE Region = @Region
GROUP BY ProductID;
قبلاً در ۵ ثانیه اجرا می‌شد، اما اکنون ۱۲۰ ثانیه طول می‌کشد.
در Query Store، دو Plan برای این Query داریم:
- Plan 1: Hash Aggregate (بهینه)
- Plan 2: Nested Loop (کند)
DBA Plan 1 را “Force” می‌کند:
EXEC sp_query_store_force_plan @query_id = 82, @plan_id = 217;
اکنون Query مجدداً سریع و پایدار اجرا می‌شود.
مزایا و معایب کلی
| ویژگی | Plan Forcing | Plan Freezing |
|---|---|---|
| مناسب برای محیط | Production | Dev/Test |
| پایداری پس از ریست | ✅ بله | ❌ خیر |
| نیاز به Query Store | ✅ بله | ❌ خیر |
| سطح کنترل | متوسط | کامل |
| پیچیدگی اجرا | پایین | بالا |
| انعطاف | بالا | پایین |
| مانیتورینگ | دارد | ندارد |
نتیجه‌گیری
در دنیای SQL Server 2025 ،Query Optimizer هوشمندتر از همیشه است — اما هنوز DBA باید بتواند در مواقع خاص، رفتار آن را هدایت کند.
با Plan Forcing از طریق Query Store می‌توانید پایداری و ثبات را تضمین کنید.
با Plan Freezing می‌توانید کنترل آزمایشی دقیق بر رفتار Optimizer داشته باشید.
بهترین رویکرد:
در محیط تولید از Plan Forcing استفاده کنید.
در محیط آزمایشی و آموزشی از Plan Freezing برای بررسی رفتار Optimizer بهره ببرید.
سوالات متداول (FAQ)
۱. آیا Plan Forcing در همه نسخه‌ها وجود دارد؟
خیر، از SQL Server 2016 به بعد (Query Store).
۲. اگر Plan اجباری حذف شود چه می‌شود؟
SQL Server به‌صورت خودکار Plan جدیدی می‌سازد و Forcing را غیرفعال می‌کند.
۳. آیا می‌توان چند Plan را هم‌زمان Force کرد؟
برای هر Query فقط یک Plan قابل Force است.
۴. آیا Plan Forcing با Recompile تداخل دارد؟
بله، اگر Query با OPTION (RECOMPILE) اجرا شود، از Plan Store استفاده نمی‌کند.
پیشنهاد مطالعه:
Forced Parameterization در SQL Server مزایا، چالش‌ها و سناریوهای استفاده
تماس و مشاوره در مورد خدمات Performance & DBA با لاندا
تیم DBA و مانیتورینگ لاندا با تجربه در SQL Server Query Tuning ،Plan Analysis و Query Store Optimization، به سازمان‌ها کمک می‌کند تا کارایی پایگاه داده خود را تثبیت و بهینه کنند. اگر با مشکلات نوسان عملکرد Query یا Regression پس از ارتقاء مواجه هستید،
همین حالا با تیم ما تماس ✆ بگیرید تا Planهایتان همیشه در بهترین حالت اجرا شوند.
نظری داده نشده