چند داینامیک ویو بسیار کاربردی برای Performance Tuning در SQL Server

در دنیای پایگاه داده‌های سازمانی، جایی که حجم تراکنش‌ها بالا و حساسیت روی عملکرد بسیار زیاد است، بهینه‌سازی دیگر یک انتخاب نیست، بلکه یک ضرورت است. در چنین شرایطی، ابزارهای داخلی مثل DMV ها در SQL Server نقش حیاتی در تحلیل رفتار سیستم دارند.

DMV ها مانند «داشبورد زنده» موتور پایگاه داده عمل می‌کنند و به شما اجازه می‌دهند بدون ابزارهای جانبی، داخل قلب SQL Server را مشاهده کنید.

‎DMV چیست و چرا در Performance Tuning حیاتی است؟

DMV یا Dynamic Management View مجموعه‌ای از ویوهای سیستمی در SQL Server است که اطلاعات لحظه‌ای یا تجمعی درباره وضعیت سیستم ارائه می‌دهد.

این ویوها به شما کمک می‌کنند پاسخ سوالات زیر را پیدا کنید:

• چرا Query کند اجرا می‌شود؟
• گلوگاه سیستم CPU است یا Disk؟
• کدام Session ها بیشترین فشار را ایجاد کرده‌اند؟
• آیا Index ها درست کار می‌کنند؟
• آیا Locking در سیستم وجود دارد؟

نکته مهم این است که DMV ها جایگزین ابزارهای مانیتورینگ نیستند، بلکه پایه اصلی تحلیل Performance محسوب می‌شوند.

۱. تحلیل عمیق Execution با sys.dm_exec_query_stats

این DMV یکی از مهم‌ترین منابع برای شناسایی کوئری‌های سنگین است. اما نکته‌ای که بسیاری از DBA ها نادیده می‌گیرند این است که این ویو فقط Aggregate داده‌ها را نشان می‌دهد، نه Context اجرای واقعی را.

sys.dm_exec_query_stats

چه چیزی به ما می‌دهد؟

• تعداد اجرای هر Query
• مجموع زمان اجرا
• میزان CPU مصرفی
• Logical Reads
• Execution Time تجمعی

نسخه حرفه‌ای‌تر Query:

SELECT TOP 20
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_time,
    qs.total_worker_time,
    qs.total_logical_reads,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

نکته مهم حرفه‌ای:

اگر یک Query Execution Count پایین ولی Total Time بالا دارد، آن Query یک Bottleneck جدی است.

۲. تحلیل Root Cause با sys.dm_os_wait_stats

Wait Stats مهم‌ترین ابزار Root Cause Analysis در Performance Tuning است.

در واقع هر اتفاقی در SQL Server که منتظر چیزی باشد، در این ویو ثبت می‌شود.

sys.dm_os_wait_stats

دسته‌بندی حرفه‌ای Wait ها:

۱. CPU Pressure

• SOS_SCHEDULER_YIELD

۲. Disk I/O Bottleneck

• PAGEIOLATCH_SH
• WRITELOG

۳. Locking / Blocking

• LCK_M_X
• LCK_M_S

۴. Memory Pressure

• RESOURCE_SEMAPHORE

کوئری استاندارد تحلیل:

SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;

نکته حرفه‌ای:

این DMV باید همیشه به صورت delta (تفاوت زمانی) تحلیل شود، نه snapshot.

۳. بررسی عمیق I/O با sys.dm_io_virtual_file_stats

این DMV یکی از دقیق‌ترین ابزارها برای بررسی عملکرد دیسک است.

در Performance Tuning واقعی، همیشه باید IO را به عنوان یکی از ۳ عامل اصلی (CPU, Memory, IO) در نظر گرفت.

sys.dm_io_virtual_file_stats

اطلاعات کلیدی:

• تعداد Read/Write
• Latency واقعی فایل‌ها
• میزان فشار روی Data و Log

کوئری تحلیلی حرفه‌ای:

SELECT 
    DB_NAME(database_id) AS db_name,
    file_id,
    io_stall_read_ms / NULLIF(num_of_reads,0) AS read_latency,
    io_stall_write_ms / NULLIF(num_of_writes,0) AS write_latency,
    num_of_reads,
    num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

تحلیل واقعی:

• اگر Read Latency بالا باشد → مشکل Index / Storage
• اگر Write Latency بالا باشد → مشکل Log File یا Transaction overload

۴. بررسی Performance Counters با sys.dm_os_performance_counters

این DMV در واقع پل ارتباطی بین SQL Server و Windows Performance Monitor است.

اما نکته مهم این است که بسیاری از DBA ها فقط Snapshot می‌گیرند، در حالی که این داده‌ها باید Trend شوند.

sys.dm_os_performance_counters

شاخص‌های مهم:

• Batch Requests/sec
• SQL Compilations/sec
• Buffer Cache Hit Ratio
• Page Life Expectancy

مثال Query:

SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%';

۵. اشتباهات رایج در استفاده از DMV ها

۱. تحلیل بدون baseline

بزرگ‌ترین اشتباه این است که فقط یک لحظه را بررسی کنیم.

۲. عدم ترکیب DMV ها

DMV ها باید ترکیبی تحلیل شوند:

• Query Stats + Wait Stats + IO Stats

۳. برداشت اشتباه از Wait ها

Wait بالا همیشه بد نیست؛ بعضی Wait ها طبیعی هستند.

۴. نادیده گرفتن Plan Cache

گاهی مشکل در Execution Plan است، نه Query.

۶. سناریوی واقعی Performance Troubleshooting

فرض کنید یک سیستم گزارش‌گیری ناگهان کند شده است.

مراحل حرفه‌ای تحلیل:

مرحله ۱: بررسی Wait Stats

اگر PAGEIOLATCH بالا باشد → مشکل دیسک

مرحله ۲: بررسی Query های سنگین

sys.dm_exec_query_stats

مرحله ۳: بررسی IO

sys.dm_io_virtual_file_stats

مرحله ۴: بررسی Blocking

sys.dm_exec_requests + sys.dm_tran_locks

این روش باعث می‌شود به جای حدس، به Root Cause واقعی برسید.

۷. چرا DMV ها برای DBA حیاتی هستند؟

DMV ها فقط ابزار نیستند؛ بلکه یک فلسفه در Performance Tuning هستند:

• مشاهده رفتار واقعی سیستم
• تحلیل بدون ابزار خارجی
• تصمیم‌گیری داده‌محور
• کاهش هزینه ابزارهای مانیتورینگ

بسیاری از ابزارهای گران‌قیمت Monitoring در واقع همین DMV ها را پشت صحنه استفاده می‌کنند.

نتیجه‌گیری

در این مقاله به بررسی عمیق چند DMV کلیدی در SQL Server پرداختیم.

برخلاف تصور رایج، DMV ها فقط ابزار مشاهده نیستند؛ بلکه ستون اصلی Performance Engineering در SQL Server محسوب می‌شوند.

اگر به صورت حرفه‌ای و ترکیبی استفاده شوند، می‌توانند:

• Bottleneck ها را دقیق مشخص کنند
• هزینه زیرساخت را کاهش دهند
• و عملکرد سیستم را چند برابر بهبود دهند

FAQ (سوالات متداول)

DMV ها چه تفاوتی با Query Store دارند؟

DMV ها لحظه‌ای هستند، Query Store تاریخی.

آیا DMV ها برای همه دیتابیس‌ها قابل استفاده‌اند؟

بله، اما سطح دسترسی لازم دارند (VIEW SERVER STATE).

آیا استفاده زیاد از DMV ها خطر دارد؟

خیر، اما Query های سنگین و مکرر می‌تواند فشار ایجاد کند.

تماس و مشاوره با لاندا

اگر در سیستم‌های سازمانی با مشکلات Performance مواجه هستید، تحلیل حرفه‌ای DMV ها می‌تواند سریع‌ترین راه رسیدن به Root Cause باشد.

خدمات تخصصی ما:

• تحلیل Performance SQL Server
• طراحی معماری بهینه دیتابیس
• رفع Blocking و Bottleneck ها
• طراحی داشبورد مانیتورینگ سازمانی

برای دریافت مشاوره تخصصی، همین حالا با تیم توسعه فناوری اطلاعات لانداتماس✆ بگیرید.