دسترسی سریع

• آموزش کامل sys.dm_exec_query_plan
• آموزش کامل sys.dm_exec_text_query_plan
• آموزش کامل sys.dm_exec_sql_text
• آموزش کامل sys.dm_exec_input_buffer
• آموزش کامل sys.dm_exec_plan_attributes
• آموزش کامل sys.dm_exec_cached_plan_dependent_objects
• آموزش کامل sys.dm_exec_query_statistics_xml
• آموزش کامل sys.dm_exec_query_profiles

Execution Plan چیست و چرا اهمیت دارد؟

Execution Plan نقشه‌ای است که Optimizer برای اجرای یک Statement انتخاب می‌کند. این نقشه شامل Operatorهایی مانند Index Seek، Scan، Join، Sort و Aggregate است و نشان می‌دهد موتور چگونه داده را پیدا، ترکیب و پردازش می‌کند. Estimated Plan برآورد Optimizer را نشان می‌دهد، در حالی که Actual یا Runtime اطلاعات واقعی‌تری از اجرای Query به دست می‌دهد.

برای تحلیل Performance باید بین «Plan کامپایل‌شده»، «Plan در Cache»، «Plan درخواست در حال اجرا» و «آمار Runtime» تفاوت قائل شویم. بعضی ابزارها XML کامپایل‌شده را می‌دهند، بعضی متن Plan را برای Statement مشخص، بعضی فقط متن SQL یا Attributeهای Cache و بعضی آمار لحظه‌ای Operatorها را فراهم می‌کنند.

Plan Handle یک شناسه باینری برای Plan است و SQL Handle Batch یا متن SQL را شناسایی می‌کند. این شناسه‌ها موقت و وابسته به وضعیت Engine هستند. Restart، Recompile یا Eviction می‌تواند باعث شود Handle قبلی دیگر نتیجه‌ای ندهد. بنابراین اسکریپت خوب باید Handle را همان لحظه از DMV معتبر استخراج کند.

دسته‌بندی منطقی ابزارها

این دسته‌بندی به معنی جدایی کامل نیست. در یک Incident واقعی معمولاً چند ابزار با هم استفاده می‌شوند. مثلاً ابتدا Query فعال را از sys.dm_exec_requests می‌گیریم، متن را با sys.dm_exec_sql_text، Plan را با sys.dm_exec_query_plan و در Query طولانی آمار لحظه‌ای را با sys.dm_exec_query_statistics_xml یا sys.dm_exec_query_profiles بررسی می‌کنیم.

معرفی همه توابع و لینک آموزش کامل

sys.dm_exec_query_plan

sys.dm_exec_query_plan برای بازیابی Showplan کامپایل‌شده یک Batch یا Query از روی plan_handle در قالب XML استفاده می‌شود. اگر طرح از Plan Cache خارج شده باشد یا Query اصولاً Cache نشده باشد، query_plan می‌تواند NULL شود. همچنین برای Dynamic SQL و UDFها ممکن است لازم باشد plan_handle جداگانه همان بخش را پیدا کنید. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_query_plan با مثال‌های عملی SQL Server

sys.dm_exec_text_query_plan

sys.dm_exec_text_query_plan برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها استفاده می‌شود. اشتباه در Offsetها باعث می‌شود بخش موردنظر بازیابی نشود. همچنین اگر plan_handle دیگر معتبر نباشد، خروجی طرح می‌تواند NULL باشد. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_text_query_plan با مثال‌های عملی SQL Server

sys.dm_exec_sql_text

sys.dm_exec_sql_text برای بازیابی متن Batch یا Query از روی sql_handle یا plan_handle استفاده می‌شود. برای Ad hoc Queryها مقدار dbid از sql_handle همیشه قابل تعیین نیست. در آبجکت‌های رمزگذاری‌شده نیز متن می‌تواند NULL باشد. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_sql_text با مثال‌های عملی SQL Server

sys.dm_exec_input_buffer

sys.dm_exec_input_buffer برای نمایش آخرین دستور یا Input Buffer ارسال‌شده برای یک Session و Request استفاده می‌شود. Input Buffer الزاماً همان Statement دقیق در حال اجرا نیست و ممکن است کل Batch یا آخرین فرمان ارسال‌شده را نشان دهد. محدودیت‌های Permission نیز باید در نظر گرفته شوند. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_input_buffer با مثال‌های عملی SQL Server

sys.dm_exec_plan_attributes

sys.dm_exec_plan_attributes برای نمایش Attributeهای یک Plan Cache Entry مانند dbid، set_options و Cache Keyها استفاده می‌شود. مقادیر value از نوع sql_variant هستند و برای مقایسه یا تبدیل باید نوع داده را درست مدیریت کنید. تفسیر set_options نیز نیازمند شناخت Bitmaskها است. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_plan_attributes با مثال‌های عملی SQL Server

sys.dm_exec_cached_plan_dependent_objects

sys.dm_exec_cached_plan_dependent_objects برای نمایش Execution Contextها، Cursorها و اشیای وابسته به یک Cached Plan استفاده می‌شود. خالی بودن نتیجه لزوماً خطا نیست؛ ممکن است Plan وابستگی قابل گزارش نداشته باشد یا از Cache خارج شده باشد. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_cached_plan_dependent_objects با مثال‌های عملی SQL Server

sys.dm_exec_query_statistics_xml

sys.dm_exec_query_statistics_xml برای بازیابی Showplan XML به همراه آمار موقت Runtime برای Queryهای در حال اجرا استفاده می‌شود. این تابع برای درخواست در حال اجراست؛ اگر Query تمام شده باشد یا شرایط Profiling فراهم نباشد، خروجی مناسب دریافت نمی‌شود. برخی جزئیات Runtime Parameter نیز بسته به نسخه و CU رفتار متفاوت دارند. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_query_statistics_xml با مثال‌های عملی SQL Server

sys.dm_exec_query_profiles

sys.dm_exec_query_profiles برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread استفاده می‌شود. اگر Profiling برای Query فعال نبوده باشد، ردیفی مشاهده نمی‌شود. داده‌ها لحظه‌ای هستند و در طول اجرا تغییر می‌کنند، بنابراین با Actual Plan نهایی یکسان نیستند. هنگام استفاده، ابتدا دامنه هدف را محدود و سپس خروجی را با Context اجرایی تفسیر کنید.

آموزش کامل sys.dm_exec_query_profiles با مثال‌های عملی SQL Server

جدول مقایسه‌ای توابع Execution Plan

روش استاندارد عیب‌یابی Performance

یک روش حرفه‌ای از «علامت» شروع می‌شود، نه از «ابزار». ابتدا مشخص کنید مشکل CPU، I/O، Blocking، Memory Grant، Plan Regression یا کندی یک Transaction خاص است. سپس Query یا Session کاندیدا را با یک Query سبک پیدا کنید. بعد جزئیات Plan و متن را فقط برای همان کاندیدا جمع کنید.

1. وضعیت لحظه‌ای سرور و Queryهای فعال را ثبت کنید.
2. Session یا Query هدف را بر اساس Duration، CPU، Logical Reads، Wait یا Blocking محدود کنید.
3. متن SQL و Statement دقیق را استخراج کنید.
4. Plan کامپایل‌شده و در صورت نیاز Runtime Plan را بررسی کنید.
5. Attributeهای Plan مانند Database Context و SET Options را برای Planهای تکراری مقایسه کنید.
6. نتیجه را با Query Store، Wait Statistics، Indexها و Statistics تطبیق دهید.
7. قبل از هر تغییر Production یک فرضیه قابل اندازه‌گیری و برنامه Rollback داشته باشید.

این روش جلوی دو رفتار پرخطر را می‌گیرد: پاک کردن Plan Cache بدون دلیل و افزودن Index صرفاً بر اساس یک Suggestion. هر تغییر باید با بار واقعی، تعداد اجرا، هزینه نگهداری Index و اثر روی Queryهای دیگر سنجیده شود.

مثال‌های ترکیبی و کاربردی

مثال 1: گزارش Queryهای پرCPU همراه متن و Plan

یک گزارش واحد برای یافتن Queryهای پرCPU، متن SQL و XML Plan می‌سازیم.

SELECT TOP (10)
    qs.total_worker_time,
    qs.execution_count,
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

نکته کاربردی: این Query نقطه شروع خوبی برای Tuning مبتنی بر شواهد است.

مثال 2: نمایش Query فعال و Input Buffer

اطلاعات Request و فرمان ورودی Client را کنار هم قرار می‌دهیم.

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    ib.event_info
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) AS ib
WHERE r.session_id <> @@SPID;

نکته کاربردی: Input Buffer به شناسایی Batch ارسال‌شده از Client کمک می‌کند.

مثال 3: تحلیل SET Options Planهای Cached

برای فهم علت چند Plan شدن Queryها set_options را نمایش می‌دهیم.

SELECT TOP (50)
    cp.usecounts,
    st.text,
    CONVERT(int, pa.value) AS set_options
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE pa.attribute = 'set_options'
ORDER BY cp.usecounts DESC;

نکته کاربردی: تفاوت SET Options می‌تواند یکی از علت‌های Plan Cache Bloat باشد.

مثال 4: Runtime Plan درخواست‌های طولانی

برای Queryهای در حال اجرا با Duration بالا Runtime Showplan می‌گیریم.

SELECT
    r.session_id,
    r.total_elapsed_time,
    qx.query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_query_statistics_xml(r.session_id) AS qx
WHERE r.session_id <> @@SPID
  AND r.total_elapsed_time >= 5000
ORDER BY r.total_elapsed_time DESC;

نکته کاربردی: فقط Queryهای هدف را Profiling کنید تا سربار کنترل شود.

مثال 5: پیشرفت Operatorهای Query زنده

Row Count فعلی و Estimate را برای Nodeهای در حال اجرا مقایسه می‌کنیم.

SELECT
    session_id,
    node_id,
    physical_operator_name,
    SUM(row_count) AS rows_so_far,
    SUM(estimate_row_count) AS estimated_rows
FROM sys.dm_exec_query_profiles
GROUP BY session_id, node_id, physical_operator_name
ORDER BY session_id, node_id;

نکته کاربردی: داده‌ها لحظه‌ای هستند و باید در Context Query Profiling تفسیر شوند.

مثال 6: گزارش Planهای بزرگ و پرتکرار

Planهای بزرگ را قبل از تحلیل عمیق اولویت‌بندی می‌کنیم.

SELECT TOP (20)
    cp.usecounts,
    cp.size_in_bytes,
    st.text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.usecounts > 1
ORDER BY cp.size_in_bytes DESC;

نکته کاربردی: اولویت‌بندی قبل از باز کردن Planها هزینه تحلیل را کاهش می‌دهد.

خطاهای رایج در تحلیل Execution Plan

• یک Plan قدیمی را بدون بررسی زمان و پارامترها به Incident فعلی نسبت دادن.
• نادیده گرفتن تفاوت Estimated و Actual یا Runtime Data.
• فرض اینکه هر Scan بد است یا هر Seek خوب است؛ هزینه واقعی به حجم داده و Selectivity وابسته است.
• پاک کردن Cache برای حل موقت مشکل و از دست دادن شواهد اصلی.
• نادیده گرفتن SET Options و Connection Context هنگام وجود چند Plan برای SQL مشابه.
• جمع‌آوری بی‌وقفه XML و Profile Data در Production بدون فیلتر و Retention مناسب.

تحلیل Plan یک مهارت Context-based است. Operator به‌تنهایی خوب یا بد نیست. Hash Match، Sort، Scan یا Parallelism باید با تعداد ردیف واقعی، Estimate، Memory Grant، Spill، I/O و الگوی کسب‌وکار تفسیر شوند.

Best Practices و معماری ابزار مانیتورینگ

برای ساخت ابزار مانیتورینگ حرفه‌ای بهتر است جمع‌آوری دو سطح داشته باشد. سطح اول سبک و دائمی است و فقط متریک‌های کلیدی مانند session_id، query_hash، plan_handle، Duration، CPU، Reads و Wait را نگه می‌دارد. سطح دوم فقط هنگام عبور از Threshold، XML Plan، متن کامل و Profile Data را ثبت می‌کند.

Retention نیز مهم است. ذخیره بی‌نهایت Plan XML در دیتابیس مانیتورینگ هزینه زیادی دارد. Deduplication بر اساس Handle یا Hash، Compression، نگهداری محدود و انتقال داده‌های قدیمی به Archive از رشد بی‌رویه جلوگیری می‌کند.

از نظر امنیتی، Credential ابزار مانیتورینگ باید فقط Permission موردنیاز را داشته باشد. در SQL Server 2022 و نسخه‌های جدیدتر برخی دسترسی‌ها با VIEW SERVER PERFORMANCE STATE یا VIEW DATABASE PERFORMANCE STATE تفکیک شده‌اند. دسترسی sysadmin برای یک Dashboard معمولاً انتخاب مناسبی نیست.

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

برای تحلیل کندی Query از کدام تابع شروع کنیم؟

از تابع خاص شروع نکنید؛ ابتدا Query یا Session هدف را با sys.dm_exec_requests یا Query Stats پیدا کنید. سپس متن، Plan و Runtime Data را متناسب با سؤال جمع‌آوری کنید.

فرق sys.dm_exec_query_plan و sys.dm_exec_text_query_plan چیست؟

اولی Showplan را به‌صورت XML می‌دهد و برای تحلیل ساختاری مناسب است؛ دومی خروجی متنی nvarchar(max) می‌دهد و می‌تواند Statement مشخص را با Offset هدف بگیرد.

آیا می‌توان این ابزارها را در داشبورد مانیتورینگ استفاده کرد؟

بله، اما جمع‌آوری باید هدفمند، محدود و مرحله‌ای باشد. ذخیره دائم تمام XMLها و Profile Rows معمولاً مقیاس‌پذیر نیست.

برای پروژه بهینه‌سازی SQL Server چه داده‌ای باید تحویل مشاور شود؟

متن Query، Plan، زمان Incident، متریک‌های CPU و I/O، Waitها، حجم داده، Indexها، Statistics و اطلاعات نسخه SQL Server بسیار مفید هستند.

Execution Plan با Query Store چه تفاوتی دارد؟

DMVهای Execution دید لحظه‌ای یا Cache-based می‌دهند؛ Query Store تاریخچه Query، Plan و Runtime Stats را در سطح Database نگه می‌دارد و برای مقایسه گذشته بسیار مناسب است.

چطور یک اسکریپت آماده عیب‌یابی را امن اجرا کنیم؟

با TOP، WHERE، بازه زمانی کوتاه، Permission حداقلی و اجرای آزمایشی. از Clear Cache یا Trace Flag سراسری در اسکریپت عمومی خودداری کنید.

چرا Plan Handle گاهی دیگر کار نمی‌کند؟

Plan ممکن است از Cache خارج، Recompile یا پس از Restart نامعتبر شده باشد. Handle شناسه دائمی نیست.

آیا Live Query Statistics سربار دارد؟

Profiling می‌تواند سربار داشته باشد، به‌خصوص اگر گسترده یا با فرکانس بالا استفاده شود. Queryهای هدف و Lightweight Profiling را ترجیح دهید.

بهترین روش برای نگهداری Planها چیست؟

Planهای مهم را با Timestamp و Metadata ذخیره کنید، Deduplicate کنید و Retention داشته باشید. Query Store نیز برای تاریخچه استاندارد گزینه مهمی است.

این توابع در همه نسخه‌های SQL Server یکسان‌اند؟

خیر. موجود بودن برخی DMVها، Permissionها و رفتار Profiling بین نسخه‌ها و سرویس‌های Azure تفاوت دارد؛ مستندات نسخه دقیق را بررسی کنید.

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

Plan Handle و SQL Handle چه تفاوتی دارند؟

SQL Handle Batch یا متن SQL را شناسایی می‌کند، در حالی که Plan Handle به Plan کامپایل‌شده اشاره دارد. هر دو شناسه موقت و وابسته به وضعیت Engine هستند.

چرا یک Query می‌تواند چند Plan در Cache داشته باشد؟

تفاوت SET Options، Database Context، Parameterization، Recompile، Plan Guide یا شرایط Compile می‌تواند نسخه‌های متفاوت بسازد. sys.dm_exec_plan_attributes برای مقایسه Context بسیار مفید است.

برای Query در حال اجرا چه ابزارهایی مناسب‌اند؟

sys.dm_exec_requests برای Context، sys.dm_exec_sql_text برای متن، sys.dm_exec_query_statistics_xml برای Runtime Showplan و sys.dm_exec_query_profiles برای Operator-level progress از ابزارهای مهم هستند.

آیا Index Scan همیشه نشانه مشکل است؟

خیر. برای جدول کوچک یا Query که درصد زیادی از داده را می‌خواند Scan می‌تواند بهترین انتخاب باشد. باید Actual Rows، Reads و Selectivity بررسی شود.

در Incident چرا نباید فوراً Plan Cache را پاک کرد؟

زیرا شواهد را از بین می‌برد، باعث Recompile گسترده می‌شود و می‌تواند CPU را افزایش دهد. ابتدا باید داده جمع‌آوری و علت ریشه‌ای مشخص شود.

جمع‌بندی و لینک مقاله‌ها

هشت ابزار این مجموعه یک زنجیره کامل از متن Query تا Plan Cache و Runtime Profiling می‌سازند. برای استفاده حرفه‌ای، آن‌ها را به‌صورت ترکیبی و بر اساس سؤال مشخص به کار ببرید. Queryهای سبک برای شناسایی کاندیدا و Queryهای عمیق برای جمع‌آوری جزئیات، بهترین معماری عملیاتی است.

• مطالعه مقاله تخصصی sys.dm_exec_query_plan
• مطالعه مقاله تخصصی sys.dm_exec_text_query_plan
• مطالعه مقاله تخصصی sys.dm_exec_sql_text
• مطالعه مقاله تخصصی sys.dm_exec_input_buffer
• مطالعه مقاله تخصصی sys.dm_exec_plan_attributes
• مطالعه مقاله تخصصی sys.dm_exec_cached_plan_dependent_objects
• مطالعه مقاله تخصصی sys.dm_exec_query_statistics_xml
• مطالعه مقاله تخصصی sys.dm_exec_query_profiles

با این مجموعه می‌توانید Runbook عیب‌یابی SQL Server بسازید، داشبوردهای داخلی را غنی‌تر کنید و در پروژه‌های Performance Tuning به‌جای حدس، تصمیم مبتنی بر شواهد بگیرید.