راهنمای جامع دستورات و مفاهیم Execution Plan در SQL Server
این مقاله مادر یک مسیر منظم برای شناخت دستورات SHOWPLAN، انواع Execution Plan، شاخصهای Estimated و Actual، Cost، Memory Grant، Parallelism، Wait Statistics، Warningها و Missing Index Recommendation فراهم میکند. برای هر موضوع یک مقاله مستقل با مثالهای قابل اجرا و خروجی نمونه نیز لینک شده است.
فهرست دسترسی سریع
مبانی تحلیل Execution Plan
Execution Plan نقشه تصمیمگیری Query Optimizer برای اجرای یک دستور SQL است. تحلیل حرفهای پلن فقط پیدا کردن اپراتور با درصد Cost بالا نیست؛ باید مسیر دسترسی به داده، تعداد ردیفهای تخمینی و واقعی، ترتیب Joinها، نیاز به Sort یا Hash، Memory Grant، Parallelism و الگوی Waitها را در کنار هدف تجاری Query بررسی کرد. یک پلن که برای حجم کم مناسب است ممکن است با رشد داده یا تغییر توزیع مقادیر رفتار دیگری نشان دهد.
در تحلیل Performance باید بین نشانه و علت تفاوت گذاشت. Index Scan همیشه بد نیست و Index Seek همیشه خوب نیست. اگر Query بخش بزرگی از جدول را نیاز داشته باشد Scan میتواند منطقیتر باشد. در مقابل، Seek کوچکی که هزاران بار در Nested Loops تکرار شود ممکن است Logical Reads زیادی ایجاد کند. بنابراین هر تصمیم باید با اندازهگیری و مقایسه قبل و بعد همراه باشد.
Cardinality Estimation یکی از پایههای تصمیم Optimizer است. وقتی تعداد ردیف اشتباه تخمین زده شود، Join Algorithm، Memory Grant، روش دسترسی و Parallelism نیز ممکن است نامناسب انتخاب شوند. اختلاف زیاد Estimated Rows و Actual Rows ارزش بررسی Statistics، دادههای Skewed، Parameter Sensitivity، Predicateهای پیچیده، تبدیل ضمنی و ساختار Query را دارد.
برای عیبیابی قابل اعتماد، Query، پارامترها، پلن، زمان اجرا، Logical Reads، CPU و شرایط بار سیستم را بهعنوان Baseline ثبت کنید. تغییرات را یکییکی اعمال و نتیجه را دوباره اندازهگیری کنید. بهینهسازی بدون Baseline ممکن است یک Query را در تست سریعتر کند اما بهدلیل Write Overhead، Blocking، Memory Pressure یا افزایش اندازه Index عملکرد کل سامانه را بدتر کند.
نسخه SQL Server و Compatibility Level میتوانند شکل پلن را تغییر دهند. Cardinality Estimatorهای جدید، Adaptive Query Processing، Memory Grant Feedback و قابلیتهای Intelligent Query Processing رفتار Optimizer را توسعه دادهاند. هنگام مقایسه پلنها باید محیط، نسخه، Statistics و تنظیمات مهم ثبت شوند تا نتیجهگیری بر اساس Context واقعی انجام شود.
در Production نباید Queryهای آزمایشی سنگین را بدون کنترل اجرا کرد. برای برخی بررسیها Estimated Plan یا SHOWPLAN مناسبتر است و برای برخی دیگر Actual Plan ضروری است. Query Store، Extended Events و مانیتورینگ کنترلشده میتوانند شواهد مکمل فراهم کنند. هدف این است که خود فرآیند عیبیابی باعث اختلال جدید، Blocking یا مصرف ناخواسته منابع نشود.
قاعده حرفهای: هیچ تصمیم Performance را فقط با یک درصد Cost یا یک Missing Index Recommendation نگیرید؛ ابتدا مسئله را اندازهگیری، علت را اثبات و تغییر را Benchmark کنید.
دستورات SHOWPLAN
موضوعات این بخش مکمل یکدیگرند و باید با توجه به ریسک اجرای Query، نیاز به Runtime Metrics و هدف عیبیابی انتخاب شوند. در محیط واقعی، نتیجه هر ابزار به Statistics، Indexها، پارامترها، حجم داده و بار همزمان وابسته است.
SET SHOWPLAN_XML ON
فعالکردن نمایش پلن تخمینی در قالب XML بدون اجرای واقعی Query؛ Query اجرا نمیشود و ShowPlanXML برگردانده میشود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_XML ON با مثالهای عملی
SET SHOWPLAN_XML OFF
خاموشکردن SHOWPLAN_XML و بازگرداندن Session به اجرای عادی؛ دستورات بعدی دوباره بهصورت معمول اجرا میشوند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_XML OFF با مثالهای عملی
SET SHOWPLAN_TEXT ON
دریافت پلن تخمینی متنی و سلسلهمراتبی بدون اجرای Query؛ خروجی متنی ساختار اپراتورها را نشان میدهد. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_TEXT ON با مثالهای عملی
SET SHOWPLAN_TEXT OFF
پایاندادن به حالت SHOWPLAN_TEXT؛ Session از حالت فقط-پلن خارج میشود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_TEXT OFF با مثالهای عملی
SET SHOWPLAN_ALL ON
دریافت پلن تخمینی متنی با ستونهای جزئیتر درباره اپراتورها؛ جزئیات بیشتری از برآورد Optimizer نمایش داده میشود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_ALL ON با مثالهای عملی
SET SHOWPLAN_ALL OFF
خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها؛ اجرای عادی Session ادامه پیدا میکند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی SET SHOWPLAN_ALL OFF با مثالهای عملی
انواع پلن و مشاهده اجرا
موضوعات این بخش مکمل یکدیگرند و باید با توجه به ریسک اجرای Query، نیاز به Runtime Metrics و هدف عیبیابی انتخاب شوند. در محیط واقعی، نتیجه هر ابزار به Statistics، Indexها، پارامترها، حجم داده و بار همزمان وابسته است.
Estimated Execution Plan
بررسی برنامه انتخابی Optimizer پیش از اجرای Query؛ پلن تخمینی بدون Runtime Metrics نمایش داده میشود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Estimated Execution Plan با مثالهای عملی
Actual Execution Plan
تحلیل پلن واقعی همراه با اطلاعات زمان اجرا و Actual Rows؛ پلن واقعی اطلاعات Runtime را به تصمیم Optimizer اضافه میکند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Actual Execution Plan با مثالهای عملی
Graphical Execution Plan
خواندن نمای گرافیکی اپراتورها، فلشها، هزینهها و جریان داده؛ اپراتورها در نمای گرافیکی SSMS قابل بررسی هستند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Graphical Execution Plan با مثالهای عملی
Live Query Statistics
مشاهده پیشرفت زنده اپراتورها هنگام اجرای Queryهای طولانی؛ پیشرفت اجرای اپراتورها در زمان اجرا قابل مشاهده است. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Live Query Statistics با مثالهای عملی
شاخصها، هزینهها و هشدارها
موضوعات این بخش مکمل یکدیگرند و باید با توجه به ریسک اجرای Query، نیاز به Runtime Metrics و هدف عیبیابی انتخاب شوند. در محیط واقعی، نتیجه هر ابزار به Statistics، Indexها، پارامترها، حجم داده و بار همزمان وابسته است.
Actual Number of Rows
تعداد واقعی ردیفهایی که در Runtime از اپراتور عبور کردهاند؛ Actual Rows مقدار واقعی مشاهدهشده هنگام اجرا است. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Actual Number of Rows با مثالهای عملی
Estimated Number of Rows
تعداد ردیف برآوردشده توسط Cardinality Estimator؛ Estimated Rows برآورد Optimizer پیش از اجرا است. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Estimated Number of Rows با مثالهای عملی
Estimated Subtree Cost
هزینه نسبی تخمینی کل زیرشاخه یک اپراتور در مدل هزینه SQL Server؛ عدد Cost برای مقایسه گزینههای پلن است، نه زمان واقعی. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Estimated Subtree Cost با مثالهای عملی
Operator Cost
سهم نسبی هزینه یک اپراتور و شیوه تفسیر صحیح آن؛ Cost اپراتور باید همراه با هزینه کل و Runtime دیده شود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Operator Cost با مثالهای عملی
Memory Grant
حافظه رزروشده برای Sort و Hash و اثر کمبود یا مازاد آن؛ Requested و Granted Memory برای Queryهای فعال قابل مشاهده است. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Memory Grant با مثالهای عملی
Degree of Parallelism
تعداد Threadهای موازی Query و ارتباط با MAXDOP و Cost Threshold؛ پلن موازی میتواند اپراتورهای Parallelism و DOP بزرگتر از 1 داشته باشد. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Degree of Parallelism با مثالهای عملی
Wait Statistics
تحلیل انتظارهای Query برای تشخیص CPU، I/O، Lock، Memory و همزمانی؛ Wait Typeهای غالب سرنخ فشار منابع هستند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Wait Statistics با مثالهای عملی
Warnings
هشدارهای پلن مانند Spill، تبدیل ضمنی، آمار و مشکلات Cardinality؛ Warningها باید در Context پلن و Runtime بررسی شوند. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Warnings با مثالهای عملی
Missing Index Recommendation
پیشنهادهای Missing Index در پلن و روش ارزیابی سود و هزینه آنها؛ پیشنهاد Missing Index فقط سرنخ است و نباید کورکورانه اجرا شود. این مفهوم را در کنار سایر متریکهای پلن تفسیر کنید و از نتیجهگیری جدا از Context خودداری کنید.
مطالعه مقاله تخصصی Missing Index Recommendation با مثالهای عملی
جدول مقایسهای موضوعات
| تابع یا موضوع | کاربرد اصلی | نوع خروجی یا نکته مهم | لینک آموزش کامل |
|---|
| SET SHOWPLAN_XML ON | فعالکردن نمایش پلن تخمینی در قالب XML بدون اجرای واقعی Query | Query اجرا نمیشود و ShowPlanXML برگردانده میشود. | آموزش SET SHOWPLAN_XML ON |
| SET SHOWPLAN_XML OFF | خاموشکردن SHOWPLAN_XML و بازگرداندن Session به اجرای عادی | دستورات بعدی دوباره بهصورت معمول اجرا میشوند. | آموزش SET SHOWPLAN_XML OFF |
| SET SHOWPLAN_TEXT ON | دریافت پلن تخمینی متنی و سلسلهمراتبی بدون اجرای Query | خروجی متنی ساختار اپراتورها را نشان میدهد. | آموزش SET SHOWPLAN_TEXT ON |
| SET SHOWPLAN_TEXT OFF | پایاندادن به حالت SHOWPLAN_TEXT | Session از حالت فقط-پلن خارج میشود. | آموزش SET SHOWPLAN_TEXT OFF |
| SET SHOWPLAN_ALL ON | دریافت پلن تخمینی متنی با ستونهای جزئیتر درباره اپراتورها | جزئیات بیشتری از برآورد Optimizer نمایش داده میشود. | آموزش SET SHOWPLAN_ALL ON |
| SET SHOWPLAN_ALL OFF | خاموشکردن SHOWPLAN_ALL و ادامه اجرای معمول Queryها | اجرای عادی Session ادامه پیدا میکند. | آموزش SET SHOWPLAN_ALL OFF |
| Estimated Execution Plan | بررسی برنامه انتخابی Optimizer پیش از اجرای Query | پلن تخمینی بدون Runtime Metrics نمایش داده میشود. | آموزش Estimated Execution Plan |
| Actual Execution Plan | تحلیل پلن واقعی همراه با اطلاعات زمان اجرا و Actual Rows | پلن واقعی اطلاعات Runtime را به تصمیم Optimizer اضافه میکند. | آموزش Actual Execution Plan |
| Graphical Execution Plan | خواندن نمای گرافیکی اپراتورها، فلشها، هزینهها و جریان داده | اپراتورها در نمای گرافیکی SSMS قابل بررسی هستند. | آموزش Graphical Execution Plan |
| Live Query Statistics | مشاهده پیشرفت زنده اپراتورها هنگام اجرای Queryهای طولانی | پیشرفت اجرای اپراتورها در زمان اجرا قابل مشاهده است. | آموزش Live Query Statistics |
| Actual Number of Rows | تعداد واقعی ردیفهایی که در Runtime از اپراتور عبور کردهاند | Actual Rows مقدار واقعی مشاهدهشده هنگام اجرا است. | آموزش Actual Number of Rows |
| Estimated Number of Rows | تعداد ردیف برآوردشده توسط Cardinality Estimator | Estimated Rows برآورد Optimizer پیش از اجرا است. | آموزش Estimated Number of Rows |
| Estimated Subtree Cost | هزینه نسبی تخمینی کل زیرشاخه یک اپراتور در مدل هزینه SQL Server | عدد Cost برای مقایسه گزینههای پلن است، نه زمان واقعی. | آموزش Estimated Subtree Cost |
| Operator Cost | سهم نسبی هزینه یک اپراتور و شیوه تفسیر صحیح آن | Cost اپراتور باید همراه با هزینه کل و Runtime دیده شود. | آموزش Operator Cost |
| Memory Grant | حافظه رزروشده برای Sort و Hash و اثر کمبود یا مازاد آن | Requested و Granted Memory برای Queryهای فعال قابل مشاهده است. | آموزش Memory Grant |
| Degree of Parallelism | تعداد Threadهای موازی Query و ارتباط با MAXDOP و Cost Threshold | پلن موازی میتواند اپراتورهای Parallelism و DOP بزرگتر از 1 داشته باشد. | آموزش Degree of Parallelism |
| Wait Statistics | تحلیل انتظارهای Query برای تشخیص CPU، I/O، Lock، Memory و همزمانی | Wait Typeهای غالب سرنخ فشار منابع هستند. | آموزش Wait Statistics |
| Warnings | هشدارهای پلن مانند Spill، تبدیل ضمنی، آمار و مشکلات Cardinality | Warningها باید در Context پلن و Runtime بررسی شوند. | آموزش Warnings |
| Missing Index Recommendation | پیشنهادهای Missing Index در پلن و روش ارزیابی سود و هزینه آنها | پیشنهاد Missing Index فقط سرنخ است و نباید کورکورانه اجرا شود. | آموزش Missing Index Recommendation |
مثالهای کاربردی ترکیبی
مثال ۱: دریافت پلن XML بدون اجرای Query
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SET SHOWPLAN_XML ON;
GO
SELECT TOP (10) * FROM sys.objects ORDER BY object_id;
GO
SET SHOWPLAN_XML OFF;
GO
| مورد | خروجی نمونه |
|---|
| نتیجه | ShowPlanXML برگردانده میشود و SELECT در حالت ON اجرا نمیشود. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
مثال ۲: بررسی پلن واقعی
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SELECT TOP (50) object_id,name,type_desc FROM sys.objects ORDER BY name;
| مورد | خروجی نمونه |
|---|
| نتیجه | پلن واقعی میتواند Actual Rows و Runtime Metadata را نشان دهد. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
مثال ۳: بررسی Sort و Memory
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SELECT TOP (100) name,create_date FROM sys.objects ORDER BY create_date DESC;
| مورد | خروجی نمونه |
|---|
| نتیجه | اپراتور Sort و نیاز احتمالی به Memory Grant قابل بررسی است. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
مثال ۴: Parallelism
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SELECT COUNT_BIG(*) FROM sys.all_objects a CROSS JOIN sys.all_objects b OPTION (MAXDOP 2);
| مورد | خروجی نمونه |
|---|
| نتیجه | در صورت انتخاب پلن موازی، Parallelism با محدودیت MAXDOP 2 دیده میشود. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
مثال ۵: Wait Statistics
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SELECT TOP (10) wait_type,waiting_tasks_count,wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
| مورد | خروجی نمونه |
|---|
| نتیجه | Wait Typeهای دارای زمان تجمعی بیشتر نمایش داده میشوند. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
مثال ۶: Missing Index DMV
این مثال برای ایجاد یک مشاهده قابل تکرار طراحی شده است. خروجی دقیق با نسخه SQL Server، Database، Statistics و workload تغییر میکند، بنابراین نتیجه را با Baseline همان محیط مقایسه کنید.
SELECT TOP (10) statement,equality_columns,inequality_columns,included_columns FROM sys.dm_db_missing_index_details;
| مورد | خروجی نمونه |
|---|
| نتیجه | پیشنهادهای موجود در حافظه نمایش داده میشوند؛ ممکن است Result Set خالی باشد. |
نکته عملی این است که پلن را با IO، CPU، Duration و تعداد اجرای Query ترکیب کنید تا ارزش واقعی بهینهسازی مشخص شود.
روش گامبهگام تحلیل حرفهای
- Query و پارامتر واقعی را ثبت و Baseline بسازید.
- پلن تخمینی یا واقعی را متناسب با ریسک اجرا انتخاب کنید.
- Estimated Rows و Actual Rows را در نقاط حساس مقایسه کنید.
- Sort، Hash، Lookup، Spool، Memory Grant و Parallelism را بررسی کنید.
- Warningها، Waitها و پیشنهادهای Missing Index را با Context تحلیل کنید.
- پس از هر تغییر Benchmark قبل و بعد انجام دهید و اثر جانبی بر Write و Concurrency را بسنجید.
سؤالات متداول
Execution Plan چه چیزی را نشان میدهد؟
نحوه انتخاب مسیر اجرای Query توسط Optimizer و در پلن واقعی بخشی از Runtime Metrics را نشان میدهد.
Estimated و Actual Plan چه تفاوتی دارند؟
Estimated بدون اجرای Query تولید میشود؛ Actual نیازمند اجرا است و اطلاعات واقعی بیشتری مانند Actual Rows دارد.
آیا Index Scan همیشه بد است؟
خیر؛ برای خواندن بخش بزرگی از داده Scan میتواند انتخاب بهینه باشد.
چرا Estimated Rows اشتباه میشود؟
Statistics، Skew، Parameter Sensitivity، Predicate و تبدیل ضمنی از علل رایج هستند.
آیا هر Missing Index را باید ساخت؟
خیر؛ همپوشانی، هزینه Write، اندازه و workload باید بررسی شود.
Memory Grant زیاد چه مشکلی دارد؟
میتواند Concurrency را کم کند و Queryهای دیگر را منتظر حافظه نگه دارد.
Wait Statistics چه کمکی میکند؟
نشان میدهد سیستم بیشتر برای چه منبع یا رویدادی منتظر میماند و جهت عیبیابی را مشخص میکند.
بزرگترین خطای تحلیل پلن چیست؟
تمرکز روی یک درصد Cost بدون بررسی Runtime و Context.
Best Practice اصلی چیست؟
اندازهگیری قبل و بعد، تغییر مرحلهای و ثبت دقیق محیط.
آیا پلن بین نسخههای SQL Server تغییر میکند؟
بله؛ Optimizer، Cardinality Estimator و Intelligent Query Processing میتوانند رفتار متفاوتی ایجاد کنند.
سؤالات مصاحبه
- تفاوت Estimated و Actual Plan چیست؟
- چگونه اختلاف Estimated Rows و Actual Rows را عیبیابی میکنید؟
- چرا Missing Index Recommendation را نباید مستقیم اجرا کرد؟
- Memory Grant و Spill چه رابطهای دارند؟
- Parallelism چه زمانی مفید یا مضر است؟
- یک روش سیستماتیک برای تحلیل Query کند توضیح دهید.
جمعبندی و مسیر مطالعه
Execution Plan زمانی بیشترین ارزش را دارد که همراه با اندازهگیری واقعی، Statistics، Index Design، Runtime Metrics و Wait Analysis استفاده شود. لینکهای زیر مسیر مطالعه کامل این مجموعه هستند.