آموزش جامع پلن اجرایی تخمینی با مثال‌های عملی SQL Server

Estimated Execution Plan یکی از ابزارها یا قابلیت‌های مهم اکوسیستم Microsoft SQL Server برای بررسی برنامه احتمالی اجرا بدون اجرای خود Query است. این مقاله از تعریف پایه آغاز می‌کند و سپس به ساخت خط مبنا، خواندن خروجی، کنترل سربار و تبدیل مشاهده فنی به اقدام قابل سنجش می‌رسد. تمام مثال‌ها برای همین موضوع طراحی شده‌اند تا متن با مقاله‌های دیگر مجموعه هم‌پوشانی محتوایی نداشته باشد.

منبع اصلی داده در این موضوع «برآوردهای Cardinality Estimator و متادیتای آماری» است و خروجی‌های شاخص آن شامل Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی می‌شود. بنابراین پیش از فعال‌سازی ابزار باید پرسش عملکردی مشخص باشد؛ برای نمونه، آیا تیم در پی بازبینی Query خطرناک پیش از اجرا است یا می‌خواهد مقایسه اثر ایندکس پیشنهادی را ارزیابی کند؟

مسیر دسترسی سریع مقاله Estimated Execution Plan: بازگشت به راهنمای ابزارهای گرافیکی و خارجی پایش عملکرد SQL Server.

تعریف و جایگاه Estimated Execution Plan

جایگاه Estimated Execution Plan در چرخه عیب‌یابی بین مشاهده، تفسیر و اقدام قرار می‌گیرد. مشاهده خام تنها می‌گوید چه چیزی رخ داده است؛ تفسیر باید آن رخداد را با ظرفیت سرور، الگوی مصرف و تغییرات نرم‌افزار مرتبط کند؛ اقدام نیز باید فرضیه‌ای قابل بازگشت داشته باشد. این ابزار برای بررسی برنامه احتمالی اجرا بدون اجرای خود Query مناسب است، اما به‌تنهایی علت ریشه‌ای را تضمین نمی‌کند.

در یک فرایند حرفه‌ای، ابتدا بازه زمانی و معیار موفقیت ثبت می‌شود. سپس داده از برآوردهای Cardinality Estimator و متادیتای آماری جمع‌آوری می‌گردد و شاخص‌هایی مانند Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی کنار هم قرار می‌گیرند. اگر نتیجه با تجربه کاربران سازگار نبود، باید از ابزار مکمل در لایه سیستم‌عامل، موتور داده یا برنامه کاربردی کمک گرفت.

تصویر نخست، معماری اختصاصی Estimated Execution Plan را از منبع داده تا خروجی‌های قابل استفاده برای بازبینی Query خطرناک پیش از اجرا نشان می‌دهد.

نحو و الگوی خواندن داده در Estimated Execution Plan

همه قابلیت‌های Estimated Execution Plan یک دستور واحد ندارند، اما Query زیر نقطه شروع عملی برای مشاهده بخشی از داده مرتبط است. آن را ابتدا در محیط آزمایش اجرا کنید و نام پایگاه، مجوزها و نسخه موتور را با شرایط واقعی تطبیق دهید.

-- نمونه 1 اختصاصی Estimated Execution Plan
        SET SHOWPLAN_XML ON; SELECT TOP (100) object_id,name FROM sys.objects ORDER BY name; SET SHOWPLAN_XML OFF;

ده مثال عملی و غیرتکراری برای Estimated Execution Plan

مثال 1: ساخت خط مبنای اولیه با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 1 روی موضوع «بازبینی Query خطرناک پیش از اجرا» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 1 اختصاصی Estimated Execution Plan
        SET SHOWPLAN_XML ON; SELECT TOP (100) object_id,name FROM sys.objects ORDER BY name; SET SHOWPLAN_XML OFF;

نکته فنی نمونه 1 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره تحلیل ترتیب Joinها تصمیم بگیرید.

مثال 2: تحلیل داده نمونه در محیط آزمایش با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 2 روی موضوع «مقایسه اثر ایندکس پیشنهادی» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 2 اختصاصی Estimated Execution Plan
        SET SHOWPLAN_ALL ON; SELECT COUNT(*) FROM sys.objects WHERE type='U'; SET SHOWPLAN_ALL OFF;

نکته فنی نمونه 2 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بررسی Predicateهای غیرقابل جست‌وجو تصمیم بگیرید.

مثال 3: استفاده در گزارش روزانه با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 3 روی موضوع «تحلیل ترتیب Joinها» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 3 اختصاصی Estimated Execution Plan
        SET SHOWPLAN_TEXT ON; SELECT o.name,s.name AS schema_name FROM sys.objects AS o JOIN sys.schemas AS s ON s.schema_id=o.schema_id; SET SHOWPLAN_TEXT OFF;

نکته فنی نمونه 3 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مطالعه هزینه نسبی اپراتورها تصمیم بگیرید.

مثال 4: اعمال فیلتر هدفمند با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 4 روی موضوع «بررسی Predicateهای غیرقابل جست‌وجو» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 4 اختصاصی Estimated Execution Plan
        SELECT TOP (20) name,object_id FROM sys.objects WHERE object_id>100 ORDER BY name OPTION (RECOMPILE);

نکته فنی نمونه 4 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بازبینی Query خطرناک پیش از اجرا تصمیم بگیرید.

مثال 5: ترکیب با نمای سیستمی مکمل با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 5 روی موضوع «مطالعه هزینه نسبی اپراتورها» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 5 اختصاصی Estimated Execution Plan
        SELECT i.object_id,i.index_id,i.name FROM sys.indexes AS i WHERE i.is_hypothetical=0 ORDER BY i.object_id,i.index_id;

نکته فنی نمونه 5 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مقایسه اثر ایندکس پیشنهادی تصمیم بگیرید.

تصویر دوم، جریان اجرای Estimated Execution Plan را از دریافت ورودی تا تولید مشاهده قابل مقایسه برای تحلیل ترتیب Joinها ترسیم می‌کند.

مثال 6: بررسی نبود داده یا مقدار NULL با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 6 روی موضوع «بازبینی Query خطرناک پیش از اجرا» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 6 اختصاصی Estimated Execution Plan
        SELECT s.name,o.name FROM sys.schemas AS s LEFT JOIN sys.objects AS o ON o.schema_id=s.schema_id WHERE o.type='U';

نکته فنی نمونه 6 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره تحلیل ترتیب Joinها تصمیم بگیرید.

مثال 7: آزمون وضعیت مرزی و بار غیرمعمول با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 7 روی موضوع «مقایسه اثر ایندکس پیشنهادی» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 7 اختصاصی Estimated Execution Plan
        DECLARE @type char(2)='U'; SELECT name,object_id FROM sys.objects WHERE type=@type;

نکته فنی نمونه 7 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بررسی Predicateهای غیرقابل جست‌وجو تصمیم بگیرید.

مثال 8: سناریوی عملیاتی سازمانی با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 8 روی موضوع «تحلیل ترتیب Joinها» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 8 اختصاصی Estimated Execution Plan
        SELECT COUNT_BIG(*) FROM sys.all_columns AS c JOIN sys.types AS t ON t.user_type_id=c.user_type_id WHERE t.name=N'nvarchar';

نکته فنی نمونه 8 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مطالعه هزینه نسبی اپراتورها تصمیم بگیرید.

مثال 9: نمایش روش اشتباه و نسخه اصلاح‌شده با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 9 روی موضوع «بررسی Predicateهای غیرقابل جست‌وجو» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود. روش اشتباه، اجرای پایش بدون فیلتر و بدون تعیین مدت است؛ نسخه اصلاح‌شده Query را محدود می‌کند تا داده مرتبط با Estimated Execution Plan استخراج شود.

-- نمونه 9 اختصاصی Estimated Execution Plan
        SELECT TOP (100) name FROM sys.objects WHERE LEFT(name,1)=N's' ORDER BY name;

نکته فنی نمونه 9 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بازبینی Query خطرناک پیش از اجرا تصمیم بگیرید.

مثال 10: کنترل کارایی و هزینه پایش با Estimated Execution Plan

در این سناریوی اختصاصی، هدف آن است که بررسی برنامه احتمالی اجرا بدون اجرای خود Query به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 10 روی موضوع «مطالعه هزینه نسبی اپراتورها» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 10 اختصاصی Estimated Execution Plan
        SELECT TOP (10) d.object_id,d.equality_columns,d.inequality_columns FROM sys.dm_db_missing_index_details AS d WHERE d.database_id=DB_ID();

نکته فنی نمونه 10 در Estimated Execution Plan آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مقایسه اثر ایندکس پیشنهادی تصمیم بگیرید. در محیط پرترافیک، ابتدا هزینه خود ابزار را بسنجید؛ زیرا نبود داده واقعی اجرا باعث می‌شود برخی مشکلات فقط در پلن Actual دیده شوند.

خطاهای رایج در کار با Estimated Execution Plan

• در Estimated Execution Plan، جمع‌آوری داده بدون سؤال مشخص خروجی حجیمی می‌سازد که هیچ تصمیمی را پشتیبانی نمی‌کند.
• نادیده گرفتن هشدار اصلی این ابزار: نبود داده واقعی اجرا باعث می‌شود برخی مشکلات فقط در پلن Actual دیده شوند.
• در تحلیل Estimated Execution Plan، مقایسه دو بازه با حجم Workload متفاوت نتیجه بهبود یا افت را مخدوش می‌کند.
• پس از مشاهده Estimated Execution Plan، اعمال تغییر مستقیم در تولید بدون Baseline و معیار Rollback خطرناک است.
• تفسیر Estimated Execution Plan با تمرکز روی یک عدد و حذف زمینه‌هایی مانند Wait، I/O، Lock و رفتار برنامه ناقص می‌ماند.

ملاحظات کارایی و بهترین روش‌ها در Estimated Execution Plan

هزینه پایش باید بخشی از طراحی باشد. برای Estimated Execution Plan دامنه داده را به رویداد، Database، Session یا بازه لازم محدود کنید. نرخ تولید داده را در پنج دقیقه نخست اندازه بگیرید؛ اگر رشد خروجی از انتظار بیشتر بود، فیلتر یا فاصله نمونه‌برداری را اصلاح کنید. نتیجه معتبر زمانی ایجاد می‌شود که خود ابزار باعث تغییر محسوس در رفتار Workload نشود.

بهترین روش، ایجاد دفترچه تصمیم است: پرسش اولیه، زمان جمع‌آوری، نسخه SQL Server، تغییرات اخیر، معیارهای Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی، تفسیر، اقدام و نتیجه پس از اقدام ثبت شوند. برای سناریوی بررسی Predicateهای غیرقابل جست‌وجو بهتر است داده قبل و بعد از تغییر با یک بار کاری مشابه مقایسه شود.

تصویر سوم، تصمیم فنی میان Baseline، مشاهده، هشدار Performance و Best Practice را برای Estimated Execution Plan و سناریوی مطالعه هزینه نسبی اپراتورها خلاصه می‌کند.

سؤالات متداول اختصاصی Estimated Execution Plan

پرسش 1: Estimated Execution Plan دقیقاً چه مسئله‌ای را حل می‌کند؟

در زمینه Estimated Execution Plan، این ابزار برای بررسی برنامه احتمالی اجرا بدون اجرای خود Query طراحی شده است و داده را از برآوردهای Cardinality Estimator و متادیتای آماری می‌گیرد. ارزش اصلی آن زمانی آشکار می‌شود که نتیجه با خط مبنا و هدف کسب‌وکار مقایسه شود.

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

در زمینه Estimated Execution Plan، دسترسی مشاهده DMVها یا مجوز مناسب ابزار، تعیین بازه پایش و شناخت بار عادی سامانه لازم است. پیش از اجرا نیز باید مشخص شود کدام‌یک از خروجی‌های «Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی» واقعاً اهمیت دارد.

پرسش 3: آیا Estimated Execution Plan برای پروژه تجاری کوچک هم ارزش دارد؟

در زمینه Estimated Execution Plan، بله، اما دامنه جمع‌آوری باید متناسب با اندازه سامانه باشد. در پروژه کوچک می‌توان از یک سناریوی محدود مانند بازبینی Query خطرناک پیش از اجرا شروع کرد و تنها در صورت نیاز جزئیات بیشتری ثبت کرد.

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

در زمینه Estimated Execution Plan، با شناسایی دقیق منبع مصرف یا رگرسیون، تیم از تغییرات حدسی دور می‌شود. این موضوع زمان عیب‌یابی، ریسک توقف سرویس و هزینه تهیه سخت‌افزار بدون ضرورت را کاهش می‌دهد.

پرسش 5: Estimated Execution Plan با ابزارهای نزدیک چه تفاوتی دارد؟

در زمینه Estimated Execution Plan، تمایز اصلی در منبع داده و عمق تحلیل است؛ این موضوع بر برآوردهای Cardinality Estimator و متادیتای آماری تکیه دارد، در حالی که ابزار دیگر ممکن است فقط نمای لحظه‌ای یا گزارش سطح سیستم‌عامل ارائه کند.

پرسش 6: برای پیاده‌سازی حرفه‌ای Estimated Execution Plan چه خدماتی مفید است؟

در زمینه Estimated Execution Plan، طراحی Baseline، انتخاب فیلتر، ساخت گزارش، تحلیل خروجی و مستندسازی اقدام اصلاحی از خدمات رایج مشاوره SQL Server هستند. این مراحل باید با نیاز واقعی پروژه هماهنگ شوند.

پرسش 7: رایج‌ترین خطا هنگام استفاده از Estimated Execution Plan چیست؟

در زمینه Estimated Execution Plan، رایج‌ترین خطا تفسیر یک نمونه منفرد بدون زمینه است. همچنین باید به این هشدار توجه شود که نبود داده واقعی اجرا باعث می‌شود برخی مشکلات فقط در پلن Actual دیده شوند.

پرسش 8: اثر Performance خود Estimated Execution Plan چگونه کنترل می‌شود؟

در زمینه Estimated Execution Plan، رویدادها یا Counterها را محدود، مدت جمع‌آوری را مشخص و حجم خروجی را اندازه‌گیری کنید. سپس مصرف CPU، I/O و فضای ذخیره‌سازی ابزار را جدا از Workload اصلی ثبت کنید.

پرسش 9: بهترین روش عملی برای Estimated Execution Plan چیست؟

در زمینه Estimated Execution Plan، با یک پرسش مشخص مانند «مقایسه اثر ایندکس پیشنهادی» شروع کنید، داده حداقلی لازم را جمع‌آوری کنید، نتیجه را با Baseline بسنجید و اقدام اصلاحی را همراه با معیار بازگشت ثبت کنید.

پرسش 10: سازگاری نسخه‌ای Estimated Execution Plan را چگونه بررسی کنیم؟

در زمینه Estimated Execution Plan، قابلیت‌ها و نام DMVها میان نسخه‌های SQL Server، Azure SQL و SSMS می‌توانند تفاوت داشته باشند. قبل از اجرای اسکریپت در تولید، آن را روی همان Edition و Compatibility Level آزمایش کنید.

سؤالات مصاحبه درباره Estimated Execution Plan

1. توضیح دهید چرا Estimated Execution Plan برای بازبینی Query خطرناک پیش از اجرا مناسب است.
2. چگونه سربار Estimated Execution Plan را در یک سرور پرترافیک اندازه‌گیری می‌کنید؟
3. بین خروجی‌های Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی کدام معیار را برای تشخیص اولیه انتخاب می‌کنید و چرا؟
4. اگر داده Estimated Execution Plan با تجربه کاربر ناسازگار باشد، چه منابع مکملی را بررسی می‌کنید؟
5. یک برنامه بازگشت برای تغییری که بر اساس Estimated Execution Plan پیشنهاد شده طراحی کنید.

چک‌لیست نهایی Estimated Execution Plan

• در Estimated Execution Plan پرسش پایش برای بازبینی Query خطرناک پیش از اجرا نوشته شده است.
• برای Estimated Execution Plan مجوز دسترسی به برآوردهای Cardinality Estimator و متادیتای آماری بررسی شده است.
• بازه زمانی و حجم خروجی Estimated Execution Plan محدود شده است.
• برای Estimated Execution Plan حداقل دو معیار از Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی ثبت شده است.
• تأثیر Estimated Execution Plan بر CPU، I/O و فضای دیسک سنجیده شده است.
• اقدام اصلاحی مبتنی بر Estimated Execution Plan و برنامه بازگشت مستند شده است.

جمع‌بندی آموزش Estimated Execution Plan

Estimated Execution Plan زمانی بیشترین ارزش را دارد که برای یک مسئله محدود مانند بازبینی Query خطرناک پیش از اجرا به کار رود و خروجی آن با Baseline سنجیده شود. داده‌های Estimated Rows، Estimated Cost، ترتیب Join و روش دسترسی باید در کنار زمینه Workload تفسیر شوند. با رعایت فیلتر، زمان‌بندی و کنترل سربار می‌توان از این قابلیت برای تصمیم‌های قابل دفاع در SQL Server استفاده کرد.

برای مقایسه Estimated Execution Plan با تمام گزینه‌های مجموعه، راهنمای جامع ابزارهای گرافیکی و خارجی عملکرد SQL Server را مطالعه کنید.