SQL Server DMV Dynamic Management Views مانیتورینگ دیتابیس بهینه‌سازی Query عیب‌یابی SQL ایندکس در SQL Server Deadlock Performance Tuning SQL

DMV ها (Dynamic Management Views) در SQL Server مجموعه‌ای از ویوهای سیستمی هستند که اطلاعات لحظه‌ای و دقیقی از وضعیت سرور، دیتابیس، Queryها و منابع سخت‌افزاری ارائه می‌کنند. استفاده هوشمندانه از DMVها به شما کمک می‌کند تا:

  • عملکرد Query‌ را بهینه کنید.
  • گلوگاه‌های سیستمی (CPU, IO, Memory) را شناسایی کنید.
  • مشکلات لاک و تراکنش را عیب‌یابی کنید.

معرفی DMV ها و مزایای آنها

Dynamic Management Views، از نسخه‌ SQL Server 2005 به بعد معرفی شدند و در هر نسخه، تعداد و قابلیت‌های جدیدی به آنها اضافه شده است. ویژگی‌های اصلی DMVها:

  • نمایش آمار زنده از اجرای Queryها، منابع و وضعیت دیتابیس
  • گزارش‌های Read-Only، بدون تغییر در داده‌ها
  • نیاز به مجوزهای VIEW SERVER STATE یا VIEW DATABASE STATE

دسته‌بندی‌ اصلی DMVها

دسته‌بندی کاربرد کلی نمونه DMV
sys.dm_exec_* آنالیز Queryها و Execution Plan sys.dm_exec_query_stats
sys.dm_exec_query_plan
sys.dm_os_* مانیتور منابع OS و سرور sys.dm_os_wait_stats
sys.dm_os_memory_clerks
sys.dm_db_* وضعیت دیتابیس، ایندکس و فایل‌ها sys.dm_db_index_usage_stats
sys.dm_db_missing_index_details
sys.dm_tran_* اطلاعات تراکنش و لاک‌ها sys.dm_tran_locks
sys.dm_tran_active_transactions
sys.dm_io_* آمار ورودی/خروجی دیسک sys.dm_io_virtual_file_stats
sys.dm_clr_* گزارش‌های CLR و اسمبلی‌های بارگذاری‌شده sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies

کاربرد DMV ها در عیب‌یابی مشکلات

  1. تشخیص Queryهای کند و سنگین
    • با sys.dm_exec_query_stats و sys.dm_exec_requests ،Queryهایی با بالاترین زمان اجرا را بیابید.
    • Execution Plan را از sys.dm_exec_query_plan استخراج کنید تا Bottleneckها را تشخیص دهید.
  2. بررسی لاک‌ها و Deadlock
    • sys.dm_tran_locks برای شناسایی Lockهای فعال
    • ترکیب با sys.dm_exec_requests برای نمایش Sessionهای بلاک‌ شده
  3. تحلیل Wait Stats و گلوگاه منابع
    • sys.dm_os_wait_stats نوع و مدت زمان انتظار (مثل PAGEIOLATCH, CXPACKET) را نشان می‌دهد.
    • sys.dm_os_memory_clerks مصرف حافظه توسط اجزای مختلف SQL را گزارش می‌کند.
  4. مانیتورینگ IO دیسک
    • sys.dm_io_virtual_file_stats میزان خواندن/نوشتن و Latency فایل‌ها را می‌دهد.
    • sys.dm_io_pending_io_requests تعداد درخواست‌های معلق دیسک را نمایش می‌دهد.
  5. تحلیل استفاده از ایندکس‌ها
    • sys.dm_db_index_usage_stats میزان استفاده از هر ایندکس را نشان می‌دهد.
    • sys.dm_db_missing_index_details پیشنهاد ایندکس‌های جدید را ارائه می‌کند.

DMV ها و بهینه‌سازی Query

– شناسایی Queryهای پر هزینه با این اسکریپت:

SELECT TOP 5
  qs.total_elapsed_time/qs.execution_count AS avg_time_ms,
  qs.execution_count,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_time_ms DESC;

– بررسی Execution Plan برای هر Query:

SELECT 
  qp.query_plan,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 10;

نکات مهم

  • از SELECT* پرهیز کنید و فقط ستون‌های ضروری را انتخاب کنید.
  • Statistics را با دستور UPDATE STATISTICS دوره‌ای به‌روز نگه دارید.
  • در صورت Parameter Sniffing، از OPTION (RECOMPILE) یا OPTIMIZE FOR UNKNOWN استفاده کنید.

DMVها در نسخه‌های مختلف SQL Server

نسخه DMVهای کلیدی جدید
2005 معرفی پایه‌ای sys.dm_exec_, sys.dm_os_, sys.dm_db_*
2008 R2 SP1 sys.dm_server_services, sys.dm_os_windows_info, …
2012 sys.dm_hadr_*, sys.dm_db_uncontained_entities, …
2014 sys.dm_db_xtp_table_memory_stats, sys.dm_db_column_store_*
2016 sys.dm_exec_query_store_, sys.dm_os_buffer_pool_extension_
2019/2022 DMVهای Intelligent Query Processing، Big Data Clusters، Ledger Tables

نکات کلیدی برای استفاده اثربخش از DMVها

  • مطمئن شوید مجوزهای لازم را دارید (VIEW SERVER STATE / VIEW DATABASE STATE).
  • خروجی DMVها پس از ری‌استارت یا پاک شدن Cache از بین می‌رود؛ در صورت لزوم گزارش دوره‌ای بگیرید.
  • داده‌ها را در جداول تاریخچه ذخیره کنید تا روند بلندمدت را تحلیل کنید.
  • از ابزارهایی مانند Power BI ،Grafana یا SSRS برای داشبورد بلادرنگ بهره ببرید.

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

1. DMV چیست و چرا اهمیت دارد؟

Dynamic Management Views، ویوهای سیستمی‌ هستند که اطلاعات زنده‌ای از اجرا، منابع و وضعیت SQL Server ارائه می‌دهند و پایه‌ی هر کار مانیتورینگ و عیب‌یابی حرفه‌ای هستند.

2. چه مجوزهایی برای دسترسی به DMV نیاز است؟

برای DMVهای سروری به VIEW SERVER STATE و برای DMVهای دیتابیس به VIEW DATABASE STATE نیاز دارید.

3. آیا خواندن DMVها روی Performance تأثیر دارد؟

Queryهای معمولی DMVها Read-Only بوده و تاثیر ناچیزی دارند، اما اسکریپت‌های سنگین تحلیل می‌توانند منابع قابل توجهی مصرف کنند.

4. چطور می‌توانم داده‌های DMV را آرشیو کنم؟

با ایجاد Jobهای زمان‌بندی‌شده در SQL Server Agent و ذخیره خروجی DMVها در جداول گزارش، می‌توانید تاریخچه عملکرد را نگهداری کنید.

5. چگونه DMVها را با ابزارهای مانیتورینگ ترکیب کنم؟

از اتصال مستقیم SQL به Power BI یا Grafana و کشیدن Queryهای DMV به داشبورد، گزارش‌های بلادرنگ بسازید.

پیشنهاد مطالعه: تحلیل Wait-Stats در SQL Server

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

می‌خواهید Performance دیتابیس خود را ۳ برابر بهبود دهید؟

برای مشاوره رایگان در زمینه DMV، مانیتورینگ و Performance Tuning از مشاورین لاندا کمک بخواهید.

همین حالا با ما تماس ✆ بگیرید و مشاوره رایگان دریافت کنید.

روزبه امیرعصامی

بهینه سازی دیتابیس

SQL Serverاسکیوال سرورایندکسباز طراحی دیتابیسبهینه سازی کوئریپایگاه دادهطراحی دیتابیس

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

بدون دیدگاه

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *