چرا DMV ها همیشه حقیقت Performance را نمی‌گویند؟

در دنیای مدیریت و بهینه‌سازی SQL Server، کمتر ابزاری به اندازه Dynamic Management Views یا همان DMVها محبوب، پرکاربرد و در عین حال بدفهمیده شده است. بسیاری از DBAها، توسعه‌دهندگان و حتی ابزارهای مانیتورینگ، تحلیل Performance SQL Server با DMV را نقطه شروع بررسی‌های خود قرار می‌دهند و گاهی همان‌جا هم متوقف می‌شوند. اما واقعیت این است که DMVها با وجود ارزش بالایی که دارند، تصویر کاملی از واقعیت Performance ارائه نمی‌دهند و در برخی سناریوها حتی می‌توانند گمراه‌کننده باشند.

DMV چیست و چرا این‌قدر محبوب است

DMVها مجموعه‌ای از Viewها و Functionهای سیستمی در SQL Server هستند که اطلاعات داخلی موتور پایگاه داده را در اختیار شما قرار می‌دهند. این اطلاعات شامل وضعیت Queryها، ایندکس‌ها، Lockها، Waitها، مصرف منابع و ده‌ها شاخص دیگر است. مزیت اصلی DMVها این است که بدون نیاز به ابزار جانبی، از داخل خود دیتابیس قابل دسترسی هستند و دید عملیاتی سریعی به وضعیت سرور می‌دهند.

برای مثال، بسیاری از DBAها برای یافتن Queryهای سنگین از sys.dm_exec_query_stats، برای بررسی ایندکس‌های استفاده نشده از sys.dm_db_index_usage_stats و برای تحلیل Waitها از sys.dm_os_wait_stats استفاده می‌کنند. این Viewها فوق‌العاده کاربردی هستند، اما یک پیش‌فرض خطرناک پشت استفاده از آن‌ها وجود دارد. این که فکر کنیم آنچه DMV نشان می‌دهد، کل حقیقت است.

مشکل اول، DMVها حافظه تاریخی کامل ندارند.

یکی از بزرگ‌ترین سوءتفاهم‌ها این است که DMVها تاریخچه Performance را نگه می‌دارند. در حالی که اغلب DMVها فقط اطلاعات را از زمان آخرین ری‌استارت سرویس SQL Server یا از زمان Load شدن یک Plan در Cache نگه می‌دارند.

به عنوان مثال، اگر سرور شما ری‌استارت شده باشد، بسیاری از آمارهای مربوط به Queryها، Waitها و استفاده از ایندکس‌ها ریست می‌شوند. حالا فرض کنید شما دو روز بعد از ری‌استارت، یک گزارش Performance تهیه می‌کنید و نتیجه می‌گیرید که این سرور مشکل خاصی ندارد. در حالی که شاید قبل از ری‌استارت، هر روز در ساعت مشخصی با بحران شدید Performance مواجه بوده است.

بنابراین DMVها بیشتر یک Snapshot از وضعیت فعلی یا اخیر هستند، نه یک سیستم مانیتورینگ تاریخی.

مشکل دوم، DMVها فقط آنچه Cache شده را می‌بینند.

بخش بزرگی از DMVهای مربوط به Query Performance بر اساس Plan Cache کار می‌کنند. یعنی فقط Queryهایی را می‌بینند که Plan آن‌ها هنوز در Cache وجود دارد. اگر Plan از Cache خارج شده باشد، DMV دیگر هیچ اطلاعی از آن ندارد.

در محیط‌هایی با Memory Pressure یا Workload بسیار متنوع، Planها سریع‌تر از Cache خارج می‌شوند. در چنین شرایطی، بدترین Queryهای شما ممکن است اصلاً در DMVها دیده نشوند، چون Plan آن‌ها مدت‌هاست Evict شده است.

این یعنی شما با اطمینان کامل می‌گویید “سنگین‌ترین Queryهای سیستم این‌ها هستند” در حالی که Query اصلی مشکل‌ساز اصلاً در لیست شما وجود ندارد.

مشکل سوم، میانگین‌ها حقیقت را پنهان می‌کنند.

بسیاری از DMVها اطلاعات را به صورت تجمیعی و میانگین‌شده ارائه می‌دهند.
برای مثال، sys.dm_exec_query_stats میانگین زمان اجرا، میانگین CPU و میانگین Logical Read را نشان می‌دهد. اما Performance واقعی معمولاً در Extremeها اتفاق می‌افتد، نه در میانگین.

فرض کنید یک Query هزار بار در روز اجرا می‌شود. ۹۹۰ بار در ۵ میلی‌ثانیه و ۱۰ بار در ۳۰ ثانیه. میانگین زمان اجرا شاید عددی کاملاً قابل قبول به شما بدهد. اما همان ۱۰ اجرای کند، سیستم را در ساعات شلوغ فلج می‌کند و کاربران را ناراضی می‌کند.

DMV به شما می‌گوید این Query “به طور میانگین” مشکلی ندارد. اما کاربر با میانگین کار نمی‌کند، با بدترین تجربه کار می‌کند.

مشکل چهارم، DMVها زمینه کسب‌وکار را نمی‌فهمند.

DMVها فقط عدد می‌بینند، نه اهمیت کسب‌وکاری. یک Query با ۵۰ میلیون Logical Read ممکن است گزارش ماهانه باشد که شب‌ها اجرا می‌شود و مشکلی ایجاد نمی‌کند. در مقابل، یک Query با ۲۰ هزار Read ممکن است در صفحه پرداخت آنلاین اجرا شود و هر میلی‌ثانیه تأخیر آن، مستقیماً درآمد را کاهش دهد.

اگر فقط بر اساس DMVها اولویت‌بندی کنید، معمولاً به سراغ بزرگ‌ترین اعداد می‌روید، نه حساس‌ترین سناریوها. نتیجه این می‌شود که زمان تیم DBA صرف بهینه‌سازی Queryهایی می‌شود که از نظر کسب‌وکار اهمیت کمتری دارند، در حالی که گلوگاه‌های واقعی تجربه کاربر دست‌نخورده باقی می‌مانند.

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

DMV مربوط به Wait Statistics یکی از محبوب‌ترین ابزارهای تحلیل Performance است. اما بسیاری از DBAها فقط لیست Waitهای برتر را می‌بینند و سریع نتیجه‌گیری می‌کنند. مثلاً می‌گویند چون PAGEIOLATCH بالا است، مشکل از دیسک است. یا چون CXPACKET زیاد است، مشکل از Parallelism است.

در حالی که Waitها نشانه هستند، نه علت. PAGEIOLATCH می‌تواند ناشی از طراحی بد Query، ایندکس نامناسب یا Memory کم باشد. CXPACKET می‌تواند نتیجه طبیعی یک Query تحلیلی سالم باشد. اگر فقط عدد Wait را ببینید و Context Workload را در نظر نگیرید، به راحتی به تصمیم اشتباه می‌رسید.

مشکل ششم، Missing Index DMV همیشه دوست شما نیست.

یکی از خطرناک‌ترین DMVها، Viewهای مربوط به Missing Index هستند. این DMVها پیشنهاد ایجاد ایندکس می‌دهند و خیلی وسوسه‌کننده‌اند. اما این پیشنهادها بدون در نظر گرفتن هزینه نگهداری ایندکس، تأثیر روی Writeها و هم‌پوشانی با ایندکس‌های موجود ارائه می‌شوند.

اگر هرچه DMV پیشنهاد می‌دهد ایجاد کنید، به سرعت با Over-Indexing مواجه می‌شوید. حجم دیتابیس افزایش می‌یابد، سرعت Insert و Update کاهش پیدا می‌کند و Maintenance ایندکس‌ها سنگین‌تر می‌شود. در اینجا DMV حقیقت ناقصی گفته و شما بدون تحلیل معماری، آن را به فاجعه تبدیل کرده‌اید.

مشکل هفتم، Blocking و Deadlockهای لحظه‌ای از دید DMV پنهان می‌مانند.

بسیاری از مشکلات Performance به Blockingهای لحظه‌ای یا Deadlockهایی مربوط هستند که سریع رخ می‌دهند و تمام می‌شوند. DMVها معمولاً وضعیت فعلی یا نزدیک به لحظه را نشان می‌دهند. اگر شما در زمان وقوع Blocking روی سرور نباشید، ممکن است هیچ اثری از آن در DMVها پیدا نکنید.

کاربر می‌گوید سیستم هر روز چند بار قفل می‌کند. شما DMVها را بررسی می‌کنید و می‌گویید هیچ Blocking خاصی وجود ندارد. هر دو طرف راست می‌گویند، چون شما فقط لحظه‌ای را دیده‌اید که مشکل وجود نداشته است.

مشکل هشتم، DMVها رفتار اپلیکیشن را درک نمی‌کنند.

Performance دیتابیس فقط تابع Query نیست. الگوی اتصال، تعداد Connectionهای همزمان، Retryهای اشتباه در اپلیکیشن، تراکنش‌های طولانی و مدیریت نادرست Connection Pool همگی بر Performance اثر دارند. DMVها نشانه‌هایی از این رفتارها نشان می‌دهند، اما تصویر کامل لایه اپلیکیشن را ندارند.

اگر فقط به DMVها تکیه کنید، ممکن است مشکل اصلی در کد اپلیکیشن یا الگوی دسترسی باشد، اما شما ماه‌ها روی ایندکس‌ها کار کنید بدون این که مسئله اصلی حل شود.

DMVها به چه درد می‌خورند.

با وجود همه این محدودیت‌ها، DMVها بسیار ارزشمند هستند، به شرطی که درست استفاده شوند. DMVها ابزار کشف نشانه‌ها هستند، نه ابزار صدور حکم نهایی. آن‌ها به شما می‌گویند کجا باید عمیق‌تر بررسی کنید، نه این که مستقیماً چه تغییری بدهید.

DMVها برای این کارها عالی هستند:

شناسایی الگوهای کلی مصرف CPU و I/O
دیدن روندهای کلی Waitها در بازه بعد از ری‌استارت
پیدا کردن Queryهای پرتکرار یا پرمصرف در سطح کلان
تشخیص ایندکس‌هایی که کاملاً بدون استفاده مانده‌اند

اما بعد از این مرحله، تحلیل باید وارد سطح عمیق‌تری شود.

جایگزین DMV نیست، مکمل آن چیست؟

برای رسیدن به حقیقت Performance، باید DMVها را با ابزارها و روش‌های دیگر ترکیب کرد. Query Store یکی از مهم‌ترین مکمل‌ها است. Query Store تاریخچه Planها، تغییرات Performance در طول زمان و Regressionها را ثبت می‌کند. چیزی که DMV به تنهایی نمی‌تواند.

Extended Events برای ثبت رویدادهای لحظه‌ای مثل Deadlock، Blocking طولانی یا Queryهای خاص بسیار حیاتی است. ابزارهای مانیتورینگ تاریخی هم به شما دید روندی می‌دهند که از طریق DMV به دست نمی‌آید.

در کنار این‌ها، تحلیل Workload از دید کسب‌وکار و شناخت سناریوهای حیاتی سیستم، چیزی است که هیچ DMVای نمی‌تواند به شما بدهد.

رویکرد حرفه‌ای به استفاده از DMV

یک DBA ارشد هرگز فقط با یک Query DMV تصمیم معماری نمی‌گیرد. رویکرد حرفه‌ای شامل این مراحل است:

اول، از DMV برای شناسایی نقاط مشکوک استفاده می‌شود.
دوم، همان نقاط با Query Store، Extended Events یا تست کنترل‌شده بررسی می‌شوند.
سوم، تأثیر تغییر پیشنهادی در محیط تست یا روی بخشی از Workload ارزیابی می‌شود.
چهارم، تغییر با در نظر گرفتن اثر آن بر کل سیستم و نه فقط یک Query اعمال می‌شود.

این یعنی DMV نقطه شروع تحلیل است، نه پایان آن.

نتیجه‌گیری

DMVها ابزارهای قدرتمند و ضروری در جعبه‌ابزار هر DBA هستند، اما حقیقت کامل Performance را نشان نمی‌دهند. آن‌ها حافظه تاریخی محدودی دارند، فقط آنچه در Cache است می‌بینند، میانگین‌ها را نشان می‌دهند نه بدترین سناریوها، زمینه کسب‌وکار را درک نمی‌کنند و بدون Context می‌توانند به تصمیم‌های اشتباه منجر شوند.

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

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

۱. آیا می‌توان فقط با DMVها Performance را تحلیل کرد؟
خیر، DMVها نقطه شروع خوبی هستند، اما برای تحلیل دقیق باید با Query Store، Extended Events و مانیتورینگ تاریخی ترکیب شوند.

۲. چرا بعد از ری‌استارت سرور، گزارش‌های DMV تغییر می‌کنند؟
چون بسیاری از DMVها اطلاعات را از زمان آخرین ری‌استارت یا از زمان Load شدن Plan در Cache نگه می‌دارند و تاریخچه قدیمی پاک می‌شود.

۳. آیا پیشنهادهای Missing Index را باید همیشه اجرا کرد؟
خیر، این پیشنهادها باید از نظر هم‌پوشانی با ایندکس‌های موجود، هزینه Write و اثر بر کل سیستم بررسی شوند.

۴. چرا Queryای که کاربر می‌گوید کند است در DMV دیده نمی‌شود؟
ممکن است Plan آن از Cache خارج شده باشد یا مشکل فقط در زمان‌های خاص رخ دهد که هنگام بررسی شما فعال نبوده است.

اگر DMV حقیقت کامل را نمی‌گوید، از کجا شروع کنیم.

از ترکیب DMV با Query Store، ثبت رویدادهای مهم با Extended Events و تحلیل سناریوهای واقعی کسب‌وکار. اگر تحلیل Performance دیتابیس شما بر پایه چند DMV ساده انجام می‌شود و نتیجه عملی قابل دفاعی ایجاد نکرده، وقت آن رسیده رویکرد حرفه‌ای‌تری اتخاذ کنید.
تیم لاندا با تحلیل عمیق Workload، استفاده ترکیبی از ابزارهای پیشرفته و طراحی راهکارهای پایدار، کمک می‌کند از تفسیر سطحی اعداد به درک واقعی رفتار دیتابیس برسید. برای یک ارزیابی تخصصی Performance و معماری SQL Server، با مشاوران لاندا تماس ✆ بگیرید.