تعریف و کاربرد اصلی

sys.dm_exec_query_profiles برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread طراحی شده است. این ابزار بخشی از مجموعه Dynamic Management Objects موتور Database Engine است و معمولاً زمانی استفاده می‌شود که DBA یا Developer می‌خواهد از علائم کلی مانند CPU بالا، کندی، Blocking یا Recompile به شواهد دقیق‌تری برسد.

در عمل هیچ DMV یا DMF به‌تنهایی پاسخ کامل نمی‌دهد. بهترین تحلیل زمانی شکل می‌گیرد که اطلاعات این آبجکت با sys.dm_exec_requests، sys.dm_exec_query_stats، sys.dm_exec_cached_plans، Query Store، Wait Statistics و متن SQL کنار هم قرار گیرد. این نگاه ترکیبی از اشتباه رایج «دیدن یک Plan و صدور حکم قطعی» جلوگیری می‌کند.

کاربرد شاخص این ابزار: مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread. بنابراین قبل از اجرا مشخص کنید سؤال فنی شما چیست و دقیقاً دنبال کدام Session، Plan یا Query هستید.

Syntax، ورودی و خروجی

نحو پایه

SELECT *
FROM sys.dm_exec_query_profiles
WHERE session_id = @session_id;

پارامتر یا Context موردنیاز

این آبجکت یک DMV است و پارامتر تابعی ندارد؛ با WHERE روی session_id، request_id، node_id یا سایر ستون‌ها محدود می‌شود.

نوع خروجی

اطلاعاتی مانند physical_operator_name، node_id، thread_id، row_count، estimate_row_count، elapsed_time_ms، cpu_time_ms و شمارنده‌های I/O را به‌صورت پویا برمی‌گرداند.

مقادیر Handle مانند plan_handle و sql_handle شناسه‌های باینری موقت هستند. آنها را به‌عنوان شناسه دائمی کسب‌وکار ذخیره نکنید. Recompile، Eviction، Restart سرویس یا تغییرات Cache می‌تواند اعتبار عملی آنها را تغییر دهد.

پیش‌نیازهای امنیتی و نکات نسخه

برای Live Query Statistics و تحلیل Real-time از زیرساخت Query Profiling استفاده می‌کند. Query باید پس از فعال شدن زیرساخت Profiling شروع شده باشد.

در بسیاری از نسخه‌ها برای مشاهده اطلاعات سراسری Server به مجوزهای خانواده VIEW SERVER STATE یا در نسخه‌های جدیدتر VIEW SERVER PERFORMANCE STATE نیاز دارید. برخی DMVهای Database-scoped نیز مجوزهای Database Performance State می‌خواهند. حساب Service یا Application را صرفاً برای راحتی مانیتورینگ عضو sysadmin نکنید؛ اصل Least Privilege را رعایت کنید.

در Azure SQL Database و Managed Instance دامنه دید Sessionها و نام Permissionها می‌تواند متفاوت باشد. اسکریپت Production باید نسخه، Edition و محیط اجرا را تشخیص دهد و در صورت نبود مجوز، خطای قابل فهم برگرداند.

مثال‌های عملی

مثال 1: مشاهده تمام Operatorهای Session هدف

ردیف‌های لحظه‌ای یک Session را بر اساس node_id و thread_id نمایش می‌دهیم.

DECLARE @session_id smallint = 54;

SELECT
    session_id,
    node_id,
    thread_id,
    physical_operator_name,
    row_count,
    estimate_row_count
FROM sys.dm_exec_query_profiles
WHERE session_id = @session_id
ORDER BY node_id, thread_id;

نکته کاربردی: Session نمونه را با SPID واقعی Query در حال اجرا جایگزین کنید.

مثال 2: محاسبه درصد تقریبی پیشرفت Operator

Row Count فعلی را با Estimate مقایسه می‌کنیم.

DECLARE @session_id smallint = 54;

SELECT
    node_id,
    physical_operator_name,
    SUM(row_count) AS row_count,
    SUM(estimate_row_count) AS estimate_row_count,
    CAST
    (
        SUM(row_count) * 100.0 /
        NULLIF(SUM(estimate_row_count), 0)
        AS decimal(10,2)
    ) AS approximate_percent
FROM sys.dm_exec_query_profiles
WHERE session_id = @session_id
GROUP BY node_id, physical_operator_name
ORDER BY node_id;

نکته کاربردی: این درصد تقریبی است و Estimate ضعیف می‌تواند آن را گمراه‌کننده کند.

مثال 3: Operatorهای پرCPU

CPU مصرفی را در سطح Operator جمع می‌کنیم.

SELECT TOP (20)
    session_id,
    node_id,
    physical_operator_name,
    SUM(cpu_time_ms) AS cpu_time_ms
FROM sys.dm_exec_query_profiles
GROUP BY session_id, node_id, physical_operator_name
ORDER BY cpu_time_ms DESC;

نکته کاربردی: برای یافتن Hot Operatorهای یک Query طولانی مناسب است.

مثال 4: Operatorهای پرI/O

Logical Read را در سطح Node جمع می‌کنیم.

SELECT TOP (20)
    session_id,
    node_id,
    physical_operator_name,
    SUM(logical_read_count) AS logical_reads,
    SUM(physical_read_count) AS physical_reads
FROM sys.dm_exec_query_profiles
GROUP BY session_id, node_id, physical_operator_name
ORDER BY logical_reads DESC;

نکته کاربردی: این خروجی برای تشخیص Scan و I/O Pressure مفید است.

مثال 5: تحلیل Parallelism بر اساس Thread

ردیف‌ها و CPU هر Thread یک Node را مقایسه می‌کنیم.

DECLARE @session_id smallint = 54;

SELECT
    node_id,
    thread_id,
    physical_operator_name,
    row_count,
    cpu_time_ms,
    elapsed_time_ms
FROM sys.dm_exec_query_profiles
WHERE session_id = @session_id
ORDER BY node_id, thread_id;

نکته کاربردی: تفاوت شدید بین Threadها می‌تواند نشانه Skew باشد.

مثال 6: پیدا کردن Operatorهای دارای Spill Write

write_page_count را برای تشخیص نوشتن Pageها بررسی می‌کنیم.

SELECT
    session_id,
    node_id,
    physical_operator_name,
    SUM(write_page_count) AS write_pages
FROM sys.dm_exec_query_profiles
WHERE write_page_count > 0
GROUP BY session_id, node_id, physical_operator_name
ORDER BY write_pages DESC;

نکته کاربردی: نوشتن Page می‌تواند با Spill در Sort یا Hash همراه باشد و نیازمند تحلیل Memory Grant است.

مثال 7: مقایسه Actual Read و Estimate Read

برای تشخیص خطای برآورد، تعداد ردیف خوانده‌شده را مقایسه می‌کنیم.

SELECT
    session_id,
    node_id,
    physical_operator_name,
    SUM(actual_read_row_count) AS actual_read_rows,
    SUM(estimated_read_row_count) AS estimated_read_rows
FROM sys.dm_exec_query_profiles
WHERE estimated_read_row_count IS NOT NULL
GROUP BY session_id, node_id, physical_operator_name
ORDER BY ABS
(
    SUM(actual_read_row_count) -
    SUM(estimated_read_row_count)
) DESC;

نکته کاربردی: اختلاف بزرگ می‌تواند به Cardinality Estimation یا Statistics مرتبط باشد.

مثال 8: اتصال به نام Database و Object

شناسه‌های دیتابیس و آبجکت را برای خوانایی تبدیل می‌کنیم.

SELECT TOP (50)
    qp.session_id,
    DB_NAME(qp.database_id) AS database_name,
    OBJECT_NAME(qp.object_id, qp.database_id) AS object_name,
    qp.index_id,
    qp.physical_operator_name,
    qp.logical_read_count
FROM sys.dm_exec_query_profiles AS qp
WHERE qp.database_id IS NOT NULL
ORDER BY qp.logical_read_count DESC;

نکته کاربردی: نام Object تحلیل Operator را برای DBA سریع‌تر می‌کند.

مثال 9: فیلتر Sessionهای واقعاً فعال

Profiles را با Requests Join می‌کنیم تا Context Request کامل شود.

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    qp.node_id,
    qp.physical_operator_name,
    qp.row_count,
    qp.elapsed_time_ms
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_query_profiles AS qp
    ON qp.session_id = r.session_id
   AND qp.request_id = r.request_id
WHERE r.session_id <> @@SPID
ORDER BY r.session_id, qp.node_id;

نکته کاربردی: Join با Requests برای ساخت Live Diagnostic Dashboard بسیار مفید است.

مثال 10: نمونه مانیتورینگ کنترل‌شده

فقط Sessionهای طولانی را مانیتور و اطلاعات Nodeها را خلاصه می‌کنیم.

WITH TargetRequests AS
(
    SELECT session_id, request_id
    FROM sys.dm_exec_requests
    WHERE session_id <> @@SPID
      AND total_elapsed_time >= 5000
)
SELECT
    qp.session_id,
    qp.node_id,
    qp.physical_operator_name,
    SUM(qp.row_count) AS rows_so_far,
    SUM(qp.cpu_time_ms) AS cpu_ms,
    SUM(qp.logical_read_count) AS logical_reads
FROM sys.dm_exec_query_profiles AS qp
JOIN TargetRequests AS tr
    ON tr.session_id = qp.session_id
   AND tr.request_id = qp.request_id
GROUP BY qp.session_id, qp.node_id, qp.physical_operator_name
ORDER BY cpu_ms DESC;

نکته کاربردی: مانیتورینگ انتخابی از Polling بی‌هدف کل DMV بهتر است.

خطاهای رایج و روش عیب‌یابی

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

• Handle یا Session قدیمی را بدون کنترل وضعیت فعلی استفاده نکنید.
• خروجی NULL یا خالی را فوراً به‌عنوان خرابی SQL Server تعبیر نکنید؛ ابتدا Lifecycle داده و Permission را بررسی کنید.
• در Queryهای CROSS APPLY روی کل Plan Cache یا همه Sessionها، ابتدا دامنه را با TOP، WHERE و معیارهای Performance محدود کنید.
• برای مقایسه دو Snapshot، زمان جمع‌آوری و Context بار سیستم را ثبت کنید تا تغییرات طبیعی با Regression اشتباه نشود.
• پاک‌سازی Plan Cache، Restart سرویس یا تغییر SET Option را فقط برای «دیدن نتیجه» انجام ندهید؛ این اقدامات می‌توانند اثر سراسری داشته باشند.

ملاحظات Performance و بهینه‌سازی

پروفایلینگ می‌تواند سربار داشته باشد. در محیط Production از Lightweight Profiling و Sessionهای هدف استفاده کنید و از Polling بسیار پرتکرار خودداری کنید.

برای ابزارهای مانیتورینگ سازمانی یک الگوی دو مرحله‌ای مناسب است: مرحله اول Query سبک برای پیدا کردن کاندیداها و مرحله دوم جمع‌آوری جزئیات فقط برای همان کاندیداها. این روش هم سربار را پایین می‌آورد و هم داده قابل استفاده‌تری تولید می‌کند.

اگر خروجی شامل XML یا متن بسیار بزرگ است، آن را در هر Poll ذخیره نکنید. می‌توان Hash، اندازه، Handle، زمان و چند متریک کلیدی را ثبت کرد و فقط در صورت عبور از آستانه یا وقوع Incident جزئیات کامل را گرفت. این رویکرد برای داشبوردهای داخلی و سامانه‌های Alerting بسیار مقیاس‌پذیرتر است.

Best Practices برای محیط Production

1. مسئله را با یک معیار قابل اندازه‌گیری تعریف کنید؛ مانند CPU، Duration، Logical Reads، Blocking یا Plan Regression.
2. ابتدا Session یا Query هدف را با یک DMV سبک شناسایی کنید.
3. سپس sys.dm_exec_query_profiles را فقط برای همان هدف اجرا کنید و خروجی را همراه Timestamp ذخیره کنید.
4. متن SQL، Database Context، SET Options و پارامترهای مؤثر را تا حد امکان کنار شواهد نگه دارید.
5. قبل از تغییر Index، Hint یا Configuration، فرضیه را در محیط Test با داده نزدیک به Production آزمایش کنید.
6. اسکریپت عیب‌یابی را Version Control کنید تا تغییرات و نتایج قابل بازبینی باشند.

در پروژه‌های بزرگ بهتر است این Queryها به‌صورت Runbook استاندارد تهیه شوند. آموزش تیم عملیات و مستندسازی اینکه هر خروجی چه معنایی دارد، ارزش بیشتری از داشتن مجموعه‌ای از اسکریپت‌های پراکنده و بدون Context ایجاد می‌کند.

کاربرد واقعی در سناریوهای سازمانی

فرض کنید سامانه فروش در ساعات اوج با افزایش ناگهانی زمان پاسخ مواجه شده است. به‌جای Restart یا پاک‌سازی Cache، ابتدا Queryهای فعال و پرهزینه شناسایی می‌شوند، سپس از sys.dm_exec_query_profiles برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread استفاده می‌شود. داده حاصل با Wait Type، Blocking، Query Store و شاخص‌های CPU و I/O تطبیق داده می‌شود.

در یک سناریوی دیگر ممکن است دو Application با Connection Setting متفاوت یک SQL مشابه را اجرا کنند و Planهای متفاوت بسازند. ابزارهای Execution-related به DBA کمک می‌کنند تفاوت Context، Plan و متن را مستند کند و به جای حدس، علت قابل اثبات ارائه دهد.

برای تیم‌های DevOps نیز می‌توان Snapshotهای محدود و زمان‌دار ساخت و هنگام Alert فقط داده‌های ضروری را ثبت کرد. این الگو برای Postmortem بسیار ارزشمند است؛ زیرا چند ساعت بعد از Incident ممکن است Plan از Cache خارج شده یا Session پایان یافته باشد.

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

sys.dm_exec_query_profiles دقیقاً چه کاری انجام می‌دهد؟

sys.dm_exec_query_profiles برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread استفاده می‌شود. ارزش اصلی آن زمانی مشخص می‌شود که اطلاعات خام Plan Cache یا Session را با Context مناسب مانند متن SQL، آمار اجرا و وضعیت Request ترکیب کنید. در آموزش‌های حرفه‌ای SQL Server این تابع معمولاً بخشی از یک زنجیره عیب‌یابی است، نه یک Query مستقل.

از کجا ورودی یا Context مناسب برای sys.dm_exec_query_profiles را پیدا کنیم؟

این آبجکت یک DMV است و پارامتر تابعی ندارد؛ با WHERE روی session_id، request_id، node_id یا سایر ستون‌ها محدود می‌شود. در عمل بهتر است ورودی را از DMVهای همان لحظه یا Plan Cache به‌صورت پویا استخراج کنید تا Handleهای قدیمی، Session اشتباه یا داده‌های نامعتبر وارد تحلیل نشوند.

آیا استفاده از sys.dm_exec_query_profiles برای پروژه‌های سازمانی ارزش دارد؟

بله، مخصوصاً در سامانه‌هایی که کندی Query، Blocking، مصرف CPU یا رفتار Plan Cache باید سریع ریشه‌یابی شود. تیم DBA می‌تواند خروجی را در Runbookها و ابزارهای مانیتورینگ داخلی قرار دهد و در پروژه‌های بهینه‌سازی SQL Server از آن برای مستندسازی شواهد فنی استفاده کند.

در چه شرایطی مشاوره تخصصی برای تحلیل خروجی sys.dm_exec_query_profiles مفید است؟

وقتی خروجی با Queryهای پیچیده، Parallelism، Parameter Sensitivity، Plan Cache Bloat یا Incidentهای Production گره می‌خورد، تفسیر صرف یک ستون کافی نیست. مشاوره SQL Server کمک می‌کند داده این ابزار با Wait Stats، Query Store، Indexها و الگوی بار واقعی کنار هم قرار گیرد.

تفاوت sys.dm_exec_query_profiles با نگاه کردن ساده به Activity Monitor چیست؟

Activity Monitor یک نمای عمومی و رابط گرافیکی ارائه می‌کند، اما sys.dm_exec_query_profiles داده فنی دقیق‌تری برای Queryهای اسکریپتی و اتوماسیون فراهم می‌کند. برای تحلیل قابل تکرار، Queryهای DMV معمولاً شفاف‌تر، قابل ذخیره‌تر و مناسب‌تر برای مقایسه هستند.

چطور یک اسکریپت آماده مبتنی بر sys.dm_exec_query_profiles را در محیط واقعی اجرا کنیم؟

ابتدا Permission لازم را بررسی کنید، سپس Query را در محیط آزمایشی یا با فیلتر محدود اجرا کنید و بعد Session، Database یا Planهای هدف را انتخاب کنید. در سرویس‌های حساس بهتر است اسکریپت مانیتورینگ با TOP، شرط زمان و ثبت حداقلی خروجی ساخته شود.

رایج‌ترین خطا هنگام کار با sys.dm_exec_query_profiles چیست؟

اگر Profiling برای Query فعال نبوده باشد، ردیفی مشاهده نمی‌شود. داده‌ها لحظه‌ای هستند و در طول اجرا تغییر می‌کنند، بنابراین با Actual Plan نهایی یکسان نیستند. خطای رایج دیگر این است که یک Snapshot را به‌عنوان حقیقت دائمی در نظر بگیریم؛ Plan Cache و وضعیت Session پویا هستند و ممکن است چند ثانیه بعد تغییر کنند.

آیا sys.dm_exec_query_profiles می‌تواند روی Performance اثر بگذارد؟

پروفایلینگ می‌تواند سربار داشته باشد. در محیط Production از Lightweight Profiling و Sessionهای هدف استفاده کنید و از Polling بسیار پرتکرار خودداری کنید. اصل مهم این است که Diagnostic Query هم باید خودش بهینه باشد؛ خواندن بی‌قید حجم زیادی XML، متن یا ردیف‌های Profiling می‌تواند در زمان Incident فشار اضافی ایجاد کند.

Best Practice استفاده از sys.dm_exec_query_profiles چیست؟

بهترین روش، شروع از یک سؤال مشخص است: کدام Session، Query یا Plan مشکل دارد؟ سپس کمترین داده لازم را جمع کنید، Timestamp و Context را ثبت کنید، خروجی را با معیارهای دیگر تطبیق دهید و از پاک‌سازی Cache یا تغییر تنظیمات بدون شواهد کافی خودداری کنید.

sys.dm_exec_query_profiles با کدام نسخه‌های SQL Server سازگار است؟

برای Live Query Statistics و تحلیل Real-time از زیرساخت Query Profiling استفاده می‌کند. Query باید پس از فعال شدن زیرساخت Profiling شروع شده باشد. همیشه مستندات نسخه دقیق SQL Server و سطح Permission را بررسی کنید، زیرا نام Permissionها و برخی جزئیات Profiling یا دسترسی DMVها بین نسخه‌ها و سرویس‌های ابری تفاوت دارد.

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

در مصاحبه چگونه کاربرد sys.dm_exec_query_profiles را توضیح می‌دهید؟

می‌گویم این ابزار برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread است و سپس توضیح می‌دهم چگونه آن را با DMVهای مرتبط ترکیب می‌کنم تا از یک شناسه خام به شواهد قابل اقدام برسم.

اگر خروجی sys.dm_exec_query_profiles خالی یا NULL باشد چه می‌کنید؟

ابتدا اعتبار Handle یا Session، وجود Query در حال اجرا، وضعیت Plan Cache، Permission و محدودیت نسخه را بررسی می‌کنم؛ سپس از یک منبع جایگزین مانند Query Store، Actual Plan یا Extended Events استفاده می‌کنم.

چطور از سربار مانیتورینگ با sys.dm_exec_query_profiles جلوگیری می‌کنید؟

با فیلتر دقیق، TOP، انتخاب بازه زمانی کوتاه، جلوگیری از Polling بی‌وقفه و جمع‌آوری فقط ستون‌های ضروری. در Production هر Diagnostic Query باید با همان دقت Queryهای کسب‌وکار طراحی شود.

چه داده‌هایی را همراه خروجی sys.dm_exec_query_profiles ذخیره می‌کنید؟

زمان جمع‌آوری، نام Instance و Database، session_id یا plan_handle مرتبط، متن Query، معیارهای CPU و I/O، Wait Type و توضیح Incident را ذخیره می‌کنم تا تحلیل بعداً قابل بازسازی باشد.

چرا یک Plan یا Snapshot به‌تنهایی برای نتیجه‌گیری کافی نیست؟

زیرا رفتار SQL Server به پارامترها، حجم داده، Statistics، Concurrency، Memory Grant، Waitها و لحظه اجرای Query وابسته است. شواهد باید از چند منبع مستقل کنار هم قرار گیرند.

چک‌لیست نهایی

• نسخه SQL Server و Permission لازم را مشخص کرده‌ام.
• Session، Query یا Plan هدف را قبل از جمع‌آوری جزئیات محدود کرده‌ام.
• خروجی را همراه زمان، Database Context و متن SQL ثبت کرده‌ام.
• نتیجه را با حداقل یک منبع دیگر مانند Query Store، Wait Stats یا Actual Plan تطبیق داده‌ام.
• هیچ اقدام پرریسک مانند Clear Cache را بدون دلیل و برنامه Rollback انجام نداده‌ام.
• اسکریپت را طوری نوشته‌ام که در صورت نبود داده یا Permission، رفتار قابل پیش‌بینی داشته باشد.

جمع‌بندی

sys.dm_exec_query_profiles یک ابزار تخصصی برای مانیتورینگ لحظه‌ای پیشرفت اجرای Query در سطح Operator و Thread است. ارزش واقعی آن در ترکیب با Context و متریک‌های دیگر نمایان می‌شود. با فیلتر هدفمند، ثبت Timestamp، رعایت Permission و تحلیل چندمنبعی می‌توان از آن برای Troubleshooting سریع و مستند در محیط‌های جدی SQL Server استفاده کرد.

برای دیدن ارتباط این ابزار با سایر توابع، مقاله مادر توابع Execution Plan در SQL Server را مطالعه کنید.