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

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

منبع اصلی داده در این موضوع «موتور بهینه‌ساز و داده‌های Runtime اپراتورها» است و خروجی‌های شاخص آن شامل Actual Rows، Actual Executions، هشدارها، زمان و مصرف اپراتورها می‌شود. بنابراین پیش از فعال‌سازی ابزار باید پرسش عملکردی مشخص باشد؛ برای نمونه، آیا تیم در پی مقایسه Actual Rows با Estimated Rows است یا می‌خواهد کشف Spill در Sort یا Hash را ارزیابی کند؟

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

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

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

در یک فرایند حرفه‌ای، ابتدا بازه زمانی و معیار موفقیت ثبت می‌شود. سپس داده از موتور بهینه‌ساز و داده‌های Runtime اپراتورها جمع‌آوری می‌گردد و شاخص‌هایی مانند Actual Rows، Actual Executions، هشدارها، زمان و مصرف اپراتورها کنار هم قرار می‌گیرند. اگر نتیجه با تجربه کاربران سازگار نبود، باید از ابزار مکمل در لایه سیستم‌عامل، موتور داده یا برنامه کاربردی کمک گرفت.

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

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

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

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

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

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

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

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

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

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

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

-- نمونه 2 اختصاصی Actual Execution Plan
        SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT COUNT_BIG(*) FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b; SET STATISTICS TIME OFF; SET STATISTICS IO OFF;

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

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

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

-- نمونه 3 اختصاصی Actual Execution Plan
        SELECT TOP (20) qs.last_rows,qs.max_rows,qp.query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp ORDER BY qs.last_execution_time DESC;

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

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

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

-- نمونه 4 اختصاصی Actual Execution Plan
        SELECT o.name,i.name AS index_name FROM sys.objects AS o LEFT JOIN sys.indexes AS i ON i.object_id=o.object_id WHERE o.type='U' ORDER BY o.name;

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

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

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

-- نمونه 5 اختصاصی Actual Execution Plan
        SELECT TOP (100) p.object_id,p.index_id,p.rows FROM sys.partitions AS p WHERE p.index_id IN (0,1) ORDER BY p.rows DESC;

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

تصویر دوم، جریان اجرای Actual Execution Plan را از دریافت ورودی تا تولید مشاهده قابل مقایسه برای بررسی Key Lookup پرهزینه ترسیم می‌کند.

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

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

-- نمونه 6 اختصاصی Actual Execution Plan
        SELECT TOP (50) name,object_id FROM sys.objects WHERE name LIKE N'%a%' ORDER BY object_id OPTION (RECOMPILE);

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

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

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

-- نمونه 7 اختصاصی Actual Execution Plan
        DECLARE @n sysname=N'sysobjects'; SELECT name,object_id FROM sys.objects WHERE name=@n;

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

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

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

-- نمونه 8 اختصاصی Actual Execution Plan
        SELECT DB_NAME(database_id) AS db_name,OBJECT_NAME(object_id,database_id) AS object_name,equality_columns,included_columns FROM sys.dm_db_missing_index_details WHERE database_id=DB_ID();

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

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

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

-- نمونه 9 اختصاصی Actual Execution Plan
        SELECT TOP (10) qs.total_spills,qs.last_spills,st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_spills DESC;

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

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

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

-- نمونه 10 اختصاصی Actual Execution Plan
        SELECT TOP (10) qs.total_dop,qs.max_dop,qs.total_grant_kb,qs.max_used_grant_kb,st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_worker_time DESC;

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

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

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

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

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

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

تصویر سوم، تصمیم فنی میان Baseline، مشاهده، هشدار Performance و Best Practice را برای Actual Execution Plan و سناریوی یافتن Implicit Conversion خلاصه می‌کند.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• در Actual Execution Plan پرسش پایش برای مقایسه Actual Rows با Estimated Rows نوشته شده است.
• برای Actual Execution Plan مجوز دسترسی به موتور بهینه‌ساز و داده‌های Runtime اپراتورها بررسی شده است.
• بازه زمانی و حجم خروجی Actual Execution Plan محدود شده است.
• برای Actual Execution Plan حداقل دو معیار از Actual Rows، Actual Executions، هشدارها، زمان و مصرف اپراتورها ثبت شده است.
• تأثیر Actual Execution Plan بر CPU، I/O و فضای دیسک سنجیده شده است.
• اقدام اصلاحی مبتنی بر Actual Execution Plan و برنامه بازگشت مستند شده است.

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

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

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