مقدمه و هدف این راهنما

در SQL Server بخش بزرگی از کارایی واقعی سرور به نحوه تخصیص، نگهداری و آزادسازی حافظه وابسته است. موتور پایگاه داده برای Buffer Pool، Plan Cache، Query Execution، Lockها، ساختارهای داخلی و بسیاری از اجزای دیگر از حافظه استفاده می‌کند. بنابراین مشاهده صرف مقدار RAM سیستم‌عامل برای تشخیص مشکل کافی نیست و باید نمای داخلی SQL Server نیز بررسی شود.

Dynamic Management Viewها یا DMVها امکان مشاهده وضعیت جاری موتور را بدون نصب ابزار جانبی فراهم می‌کنند. داده این نماها لحظه‌ای است و باید همراه با خط مبنا، زمان نمونه‌برداری، بار کاری و تنظیمات سرور تفسیر شود. یک عدد منفرد معمولاً علت مشکل را ثابت نمی‌کند؛ روند تغییر و هم‌بستگی چند شاخص است که تحلیل را قابل اعتماد می‌سازد.

در عیب‌یابی حرفه‌ای ابتدا باید مشخص شود فشار حافظه از سیستم‌عامل، خود فرایند SQL Server، Buffer Pool، Cacheها، Memory Clerkها یا معماری NUMA ناشی شده است. مجموعه DMVهای این راهنما هر کدام بخشی از این زنجیره را روشن می‌کنند و در کنار هم تصویری چندلایه از وضعیت حافظه ارائه می‌دهند.

این مجموعه ده DMV مهم حافظه را پوشش می‌دهد؛ از سطح سیستم‌عامل و فرایند تا Clerk، Broker، Cache، Buffer Pool، Memory Object، Virtual Address Space و آمار دسترسی NUMA. هدف آن است که مدیر پایگاه داده بتواند از یک بررسی سطح بالا به تشخیص دقیق‌تر برسد و برای هر مرحله ابزار مناسب را انتخاب کند.

دسترسی سریع به مقاله‌های تخصصی

• آموزش کامل sys.dm_os_process_memory — حافظه مصرفی فرایند SQL Server
• آموزش کامل sys.dm_os_sys_memory — وضعیت حافظه سیستم‌عامل
• آموزش کامل sys.dm_os_memory_clerks — تحلیل Memory Clerkها
• آموزش کامل sys.dm_os_memory_brokers — تحلیل Memory Brokerها
• آموزش کامل sys.dm_os_memory_cache_counters — شمارنده‌های کش حافظه
• آموزش کامل sys.dm_os_memory_cache_entries — ورودی‌های کش حافظه
• آموزش کامل sys.dm_os_buffer_descriptors — تحلیل Buffer Pool
• آموزش کامل sys.dm_os_memory_objects — تحلیل Memory Objectها
• آموزش کامل sys.dm_os_virtual_address_dump — فضای آدرس مجازی SQL Server
• آموزش کامل sys.dm_os_memory_node_access_stats — آمار دسترسی NUMA به حافظه

مدل ذهنی تحلیل حافظه در SQL Server

تحلیل حافظه را بهتر است از بیرون به داخل انجام دهید. ابتدا ظرفیت کل و حافظه در دسترس سیستم‌عامل را بررسی کنید، سپس وضعیت فرایند SQL Server را ببینید و بعد به مصرف‌کنندگان داخلی مانند Memory Clerkها، Cacheها و Buffer Pool وارد شوید. این ترتیب از نتیجه‌گیری عجولانه جلوگیری می‌کند.

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

در سرورهای NUMA باید محلی بودن دسترسی به حافظه را نیز جدی گرفت. حتی اگر مجموع RAM کافی باشد، عدم توازن میان Nodeها یا افزایش دسترسی Remote می‌تواند Latency ایجاد کند. بنابراین تحلیل نهایی باید سخت‌افزار، تنظیمات Max Server Memory، بار کاری و الگوی NUMA را هم‌زمان در نظر بگیرد.

برای گزارش‌گیری قابل اتکا، خروجی DMVها را دوره‌ای Snapshot کنید. ذخیره نمونه‌ها با Timestamp امکان مقایسه قبل و بعد از رخداد، تشخیص روند رشد، شناسایی Memory Leak احتمالی و ارزیابی اثر تغییرات پیکربندی را فراهم می‌کند. داده زنده بدون تاریخچه معمولاً تنها یک عکس لحظه‌ای است.

همچنین باید تفاوت Cache مفید با مصرف غیرعادی را درک کرد. بالا بودن حافظه مصرفی SQL Server ذاتاً مشکل نیست؛ موتور عمداً از RAM برای کاهش I/O استفاده می‌کند. مشکل زمانی مطرح می‌شود که نشانه‌های فشار، Paging، افت شدید Page Life Expectancy، رشد کنترل‌نشده یک Clerk یا Cache، یا عدم تعادل مستمر دیده شود.

مقایسه DMVهای اصلی حافظه

معرفی و دسته‌بندی هر DMV

sys.dm_os_process_memory — حافظه مصرفی فرایند SQL Server

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

آموزش کامل sys.dm_os_process_memory با مثال‌های عملی و نکات کارایی

sys.dm_os_sys_memory — وضعیت حافظه سیستم‌عامل

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

آموزش کامل sys.dm_os_sys_memory با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_clerks — تحلیل Memory Clerkها

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

آموزش کامل sys.dm_os_memory_clerks با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_brokers — تحلیل Memory Brokerها

اطلاعات Brokerهای مدیریت حافظه و روند تخصیص، هدف‌گذاری و فشار حافظه میان اجزای مختلف موتور SQL Server. این DMV باید همراه با سایر شاخص‌های حافظه تفسیر شود و بهتر است خروجی آن در چند بازه زمانی مقایسه گردد.

آموزش کامل sys.dm_os_memory_brokers با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_cache_counters — شمارنده‌های کش حافظه

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

آموزش کامل sys.dm_os_memory_cache_counters با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_cache_entries — ورودی‌های کش حافظه

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

آموزش کامل sys.dm_os_memory_cache_entries با مثال‌های عملی و نکات کارایی

sys.dm_os_buffer_descriptors — تحلیل Buffer Pool

اطلاعات صفحات حاضر در Buffer Pool شامل پایگاه داده، فایل، نوع صفحه، وضعیت تغییر و فضای آزاد برای تحلیل دقیق حافظه دیتابیس. این DMV باید همراه با سایر شاخص‌های حافظه تفسیر شود و بهتر است خروجی آن در چند بازه زمانی مقایسه گردد.

آموزش کامل sys.dm_os_buffer_descriptors با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_objects — تحلیل Memory Objectها

جزئیات Memory Objectهای داخلی SQL Server و تخصیص صفحه، والد، نوع و Node برای ردیابی مصرف حافظه در سطح پایین. این DMV باید همراه با سایر شاخص‌های حافظه تفسیر شود و بهتر است خروجی آن در چند بازه زمانی مقایسه گردد.

آموزش کامل sys.dm_os_memory_objects با مثال‌های عملی و نکات کارایی

sys.dm_os_virtual_address_dump — فضای آدرس مجازی SQL Server

نمای تشخیصی از فضای آدرس مجازی فرایند SQL Server برای بررسی رزرو، Commit و نواحی حافظه در سناریوهای عیب‌یابی پیشرفته. این DMV باید همراه با سایر شاخص‌های حافظه تفسیر شود و بهتر است خروجی آن در چند بازه زمانی مقایسه گردد.

آموزش کامل sys.dm_os_virtual_address_dump با مثال‌های عملی و نکات کارایی

sys.dm_os_memory_node_access_stats — آمار دسترسی NUMA به حافظه

آمار دسترسی میان Nodeهای حافظه و NUMA برای تشخیص الگوهای دسترسی محلی و راه‌دور و بررسی اثر معماری سخت‌افزار بر کارایی. این DMV باید همراه با سایر شاخص‌های حافظه تفسیر شود و بهتر است خروجی آن در چند بازه زمانی مقایسه گردد.

آموزش کامل sys.dm_os_memory_node_access_stats با مثال‌های عملی و نکات کارایی

شش مثال عملی برای شروع تحلیل حافظه

مثال 1: بررسی سریع حافظه سیستم‌عامل

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

SELECT
    total_physical_memory_kb,
    available_physical_memory_kb,
    system_high_memory_signal_state,
    system_low_memory_signal_state
FROM sys.dm_os_sys_memory;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

مثال 2: بررسی حافظه فرایند SQL Server

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

SELECT
    physical_memory_in_use_kb,
    locked_page_allocations_kb,
    memory_utilization_percentage,
    process_physical_memory_low
FROM sys.dm_os_process_memory;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

مثال 3: یافتن Memory Clerkهای پرمصرف

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

SELECT TOP (10)
    type,
    SUM(pages_kb) AS pages_kb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY pages_kb DESC;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

مثال 4: بررسی توزیع Buffer Pool بین دیتابیس‌ها

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

SELECT
    DB_NAME(database_id) AS database_name,
    COUNT_BIG(*) * 8 / 1024 AS buffer_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
GROUP BY database_id
ORDER BY buffer_mb DESC;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

مثال 5: بررسی Memory Objectهای بزرگ

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

SELECT TOP (20)
    type,
    name,
    pages_allocated_count,
    page_size_in_bytes
FROM sys.dm_os_memory_objects
ORDER BY pages_allocated_count DESC;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

مثال 6: Snapshot شمارنده‌های کش

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

SELECT
    GETDATE() AS sample_time,
    name,
    type,
    pages_kb,
    entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY pages_kb DESC;

کاربرد حرفه‌ای این مثال زمانی بیشتر می‌شود که نتیجه را با Timestamp ذخیره و در چند بازه زمانی مقایسه کنید. تغییر روند مهم‌تر از یک مقدار منفرد است.

روش استاندارد عیب‌یابی فشار حافظه

گام اول بررسی سیستم‌عامل است: مقدار Available Memory، Commit و سیگنال Low Memory را ببینید. اگر سیستم‌عامل تحت فشار است، قبل از متهم کردن SQL Server باید مصرف سایر پردازش‌ها، تنظیم Max Server Memory و Paging را بررسی کنید.

گام دوم وضعیت خود فرایند SQL Server است. نسبت حافظه فیزیکی، Locked Pages، Virtual Address Space و پرچم‌های Low Memory می‌تواند نشان دهد موتور با چه محدودیتی روبه‌رو است. در این مرحله تنظیمات Lock Pages in Memory و Service Account نیز ممکن است اهمیت پیدا کند.

گام سوم Drill Down روی مصرف‌کنندگان داخلی است. Memory Clerkها، Cache Counterها، Cache Entryها و Memory Objectها را مقایسه کنید تا مشخص شود رشد حافظه در کدام جزء متمرکز شده است. مصرف بالا الزاماً Leak نیست و باید با نوع Workload و رفتار طبیعی موتور تطبیق داده شود.

گام چهارم Buffer Pool و پایگاه داده‌های پرمصرف است. Buffer Descriptorها نشان می‌دهند کدام دیتابیس و چه نوع صفحاتی سهم بیشتری از Buffer Pool دارند. این داده می‌تواند در کنار I/O، ایندکس‌ها و الگوی اسکن برای بهینه‌سازی استفاده شود.

گام پنجم بررسی NUMA و Memory Nodeهاست. روی سرورهای چندسوکته، عدم توازن یا دسترسی Remote می‌تواند اثر جدی بر Latency داشته باشد. تحلیل NUMA باید با Affinity، Soft-NUMA، نسخه SQL Server و توپولوژی سخت‌افزار هماهنگ باشد.

در پایان، هر تغییر باید اندازه‌گیری شود. افزایش RAM یا تغییر Max Server Memory بدون Baseline ممکن است تنها علامت را جابه‌جا کند. قبل و بعد از تغییر، همان مجموعه Snapshotها را ثبت و اثر واقعی روی Query Duration، Waitها، I/O و مصرف حافظه مقایسه کنید.

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

آیا مصرف بالای RAM توسط SQL Server همیشه مشکل است؟

خیر. SQL Server برای کش داده و کاهش I/O عمداً حافظه را مصرف می‌کند. وجود فشار حافظه، Paging، افت کارایی یا رشد غیرعادی یک مصرف‌کننده معیار مهم‌تری از درصد خام مصرف RAM است.

از کدام DMV باید شروع کنیم؟

برای دید کلی ابتدا sys.dm_os_sys_memory و sys.dm_os_process_memory مناسب‌اند؛ سپس بر اساس نشانه‌ها به Clerkها، Cacheها، Buffer Pool و NUMA وارد شوید.

آیا یک Snapshot برای تشخیص کافی است؟

معمولاً خیر. Snapshot لحظه‌ای می‌تواند تحت تأثیر یک بار موقت باشد. ذخیره دوره‌ای داده و مقایسه روند، تشخیص را بسیار قابل اعتمادتر می‌کند.

برای مانیتورینگ سازمانی چه روشی بهتر است؟

ایجاد Job سبک برای ذخیره چند ستون کلیدی از DMVها در جدول تاریخچه و ساخت داشبورد روندی روش مناسبی است. نرخ نمونه‌برداری باید متناسب با بار سرور انتخاب شود.

تفاوت Memory Clerk و Memory Object چیست؟

Clerk سطح حسابداری و دسته‌بندی مصرف حافظه را نشان می‌دهد، در حالی که Memory Object جزئیات ساختارهای تخصیص داخلی را در سطح پایین‌تر ارائه می‌کند.

آیا می‌توان از این DMVها در پروژه مانیتورینگ استفاده کرد؟

بله. با طراحی Snapshot، Retention و Alert مناسب می‌توان سامانه مانیتورینگ حافظه ساخت و برای تحلیل ظرفیت، مشاوره کارایی و تشخیص رخدادها از آن بهره برد.

خطای Permission هنگام Query کردن DMVها چگونه رفع می‌شود؟

مجوز لازم بسته به DMV و نسخه متفاوت است. معمولاً مجوزهای سطح سرور مانند VIEW SERVER STATE یا مجوزهای جدیدتر عملکرد سرور مطرح می‌شوند؛ اصل Least Privilege را رعایت کنید.

Query کردن DMVهای حافظه چقدر سربار دارد؟

بسیاری از Queryهای ساده کم‌هزینه‌اند، اما Viewهای حجیم مانند Buffer Descriptorها یا Queryهای تجمیعی سنگین می‌توانند CPU مصرف کنند. Sampling را کنترل و ستون‌های موردنیاز را محدود کنید.

بهترین روش برای تحلیل Max Server Memory چیست؟

تنظیم را بر اساس RAM کل، نیاز سیستم‌عامل، سایر سرویس‌ها و بار واقعی تعیین کنید و سپس با DMVها صحت آن را پایش کنید. یک عدد ثابت برای همه سرورها مناسب نیست.

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

خیر. برخی DMVها یا ستون‌ها در نسخه‌ها و پلتفرم‌های مختلف تغییر می‌کنند. قبل از استقرار اسکریپت تولیدی، مستندات نسخه و Metadata همان سرور را بررسی کنید.

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

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

چگونه Memory Clerk پرمصرف را پیدا می‌کنید؟ پاسخ مناسب باید علاوه بر تعریف، روش اندازه‌گیری، محدودیت DMV و یک سناریوی واقعی عیب‌یابی را توضیح دهد.

چرا مصرف بالای Buffer Pool لزوماً بد نیست؟ پاسخ مناسب باید علاوه بر تعریف، روش اندازه‌گیری، محدودیت DMV و یک سناریوی واقعی عیب‌یابی را توضیح دهد.

NUMA چگونه می‌تواند بر کارایی حافظه اثر بگذارد؟ پاسخ مناسب باید علاوه بر تعریف، روش اندازه‌گیری، محدودیت DMV و یک سناریوی واقعی عیب‌یابی را توضیح دهد.

برای ساخت Baseline حافظه چه DMVهایی را Snapshot می‌کنید؟ پاسخ مناسب باید علاوه بر تعریف، روش اندازه‌گیری، محدودیت DMV و یک سناریوی واقعی عیب‌یابی را توضیح دهد.

جمع‌بندی

DMVهای حافظه زمانی بیشترین ارزش را دارند که به‌صورت زنجیره‌ای استفاده شوند: از سیستم‌عامل و فرایند شروع کنید، سپس مصرف‌کنندگان داخلی، Cache، Buffer Pool، Memory Object و در نهایت NUMA را بررسی کنید. این روش احتمال تشخیص اشتباه را کاهش می‌دهد.

برای ادامه مطالعه، هر یک از مقاله‌های تخصصی این مجموعه شامل تعریف، Syntax، حداقل ده مثال عملی، خروجی نمونه، خطاهای رایج، نکات کارایی، Best Practice و FAQ اختصاصی است.

• مطالعه تخصصی sys.dm_os_process_memory
• مطالعه تخصصی sys.dm_os_sys_memory
• مطالعه تخصصی sys.dm_os_memory_clerks
• مطالعه تخصصی sys.dm_os_memory_brokers
• مطالعه تخصصی sys.dm_os_memory_cache_counters
• مطالعه تخصصی sys.dm_os_memory_cache_entries
• مطالعه تخصصی sys.dm_os_buffer_descriptors
• مطالعه تخصصی sys.dm_os_memory_objects
• مطالعه تخصصی sys.dm_os_virtual_address_dump
• مطالعه تخصصی sys.dm_os_memory_node_access_stats