مقدمه

وقتی یک Execution Plan شامل Sort، Hash Join، Hash Aggregate، Exchange یا عملگرهای حافظه‌بر باشد، SQL Server پیش از اجرا بخشی از Query Execution Memory را برای آن Request رزرو می‌کند. اگر ظرفیت کافی وجود داشته باشد Grant اعطا می‌شود؛ در غیر این صورت Request در صف Resource Semaphore می‌ماند. DMV با نام sys.dm_exec_query_memory_grants نقطه شروع اصلی برای دیدن هر دو گروه است: Queryهایی که هنوز منتظرند و Queryهایی که Grant گرفته‌اند و در حال اجرا هستند.

این نما Snapshot لحظه‌ای است و فقط Queryهایی را نشان می‌دهد که Memory Grant خواسته‌اند. بنابراین نتیجه خالی می‌تواند کاملاً طبیعی باشد و ثابت نمی‌کند سرور هیچ Query فعالی ندارد. ستون‌هایی مانند requested_memory_kb، required_memory_kb، granted_memory_kb، used_memory_kb و max_used_memory_kb امکان می‌دهند فاصله میان تخمین Optimizer، تصمیم موتور و مصرف واقعی بررسی شود. wait_time_ms و grant_time نیز برای تشخیص صف و شدت Incident ضروری‌اند.

کاربرد حرفه‌ای DMV صرفاً مرتب کردن requested_memory_kb از بزرگ به کوچک نیست. باید SQL Text، Plan، DOP، Resource Pool، مدت انتظار و الگوی چند Snapshot را کنار هم قرار داد. Grant بزرگ ممکن است برای یک گزارش سنگین مشروع باشد، درحالی‌که مجموعه‌ای از Grantهای متوسط و هم‌زمان می‌تواند Concurrency را از بین ببرد. همچنین نسبت مصرف پایین در یک اجرای منفرد دلیل قطعی Over-Grant نیست و باید در اجراهای نماینده تکرار شود.

این مطلب بخشی از راهنمای جامع DMVهای حافظه Query در SQL Server است. برای دیدن ارتباط این نما با Semaphoreهای اجرا، Gateهای Compile و Workerهای Parallel می‌توانید ابتدا یا پس از مطالعه این مقاله به راهنمای مادر بازگردید.

تعریف sys.dm_exec_query_memory_grants

sys.dm_exec_query_memory_grants برای هر Request دارای درخواست Memory Grant یک ردیف بازمی‌گرداند. ردیف منتظر معمولاً grant_time برابر NULL، granted_memory_kb تهی یا بدون Grant قابل استفاده، queue_id و wait_order معتبر و wait_time_ms رو به رشد دارد. ردیف دارای Grant، زمان اعطا و مقدار حافظه اعطاشده را ثبت می‌کند و با used_memory_kb و max_used_memory_kb می‌توان میزان بهره‌برداری را سنجید.

Handleهای plan_handle و sql_handle پل ارتباطی با sys.dm_exec_query_plan و sys.dm_exec_sql_text هستند. این اتصال باید هدفمند انجام شود، چون واکشی Plan XML برای همه Sessionها می‌تواند هزینه‌بر باشد. شناسه‌های pool_id و group_id نیز محیط Resource Governor را مشخص می‌کنند. resource_semaphore_id در کنار pool_id برای تطبیق با نمای Resource Semaphore مفید است.

از SQL Server 2016 ستون‌های reserved_worker_count، used_worker_count، max_used_worker_count و reserved_node_bitmap اطلاعات Workerهای Queryهای موازی را اضافه می‌کنند. این ستون‌ها کمک می‌کنند Grant حافظه با ظرفیت Parallel Worker تحلیل شود. در Azure برخی ستون‌های سطح سرور یا Tenant ممکن است فیلتر یا NULL شوند؛ بنابراین Collector باید تفاوت محیط اجرا را در نظر بگیرد.

Syntax پایه

SELECT
        session_id,
        request_id,
        request_time,
        grant_time,
        requested_memory_kb,
        granted_memory_kb,
        used_memory_kb,
        max_used_memory_kb,
        wait_time_ms
    FROM sys.dm_exec_query_memory_grants;

مجوزها و سازگاری نسخه

در SQL Server 2022 و نسخه‌های جدیدتر، مجوز VIEW SERVER PERFORMANCE STATE لازم است. در نسخه‌های قدیمی‌تر SQL Server معمولاً VIEW SERVER STATE نیاز است. در Azure SQL Database سطح مجوز و فیلتر Tenant متفاوت است و معمولاً VIEW DATABASE STATE یا نقش مدیریتی متناظر استفاده می‌شود. ستون‌های Worker از SQL Server 2016 به بعد وجود دارند.

ستون‌های مهم و نحوه تفسیر

برای تحلیل سریع همه ستون‌ها لازم نیست. جدول زیر مهم‌ترین ستون‌ها را بر اساس نقش تشخیصی دسته‌بندی می‌کند؛ واحد حافظه در ستون‌های دارای پسوند kb، کیلوبایت است و برای گزارش انسانی معمولاً به مگابایت تبدیل می‌شود.

چرخه Grant و روش تفسیر درست

در زمان Compile، Optimizer بر پایه تخمین تعداد و عرض ردیف‌ها، نوع عملگرها و DOP مقدار Memory Grant را محاسبه می‌کند. required_memory_kb حداقل فضای ساختارهای داخلی است؛ requested_memory_kb مقدار درخواست عملی و ideal_memory_kb مقدار مطلوب برای کاهش Spill است. در زمان اجرا Resource Semaphore با توجه به ظرفیت Pool و درخواست‌های دیگر تصمیم می‌گیرد Grant را بدهد، کاهش دهد یا Request را منتظر نگه دارد.

پس از اعطا، used_memory_kb مصرف فعلی و max_used_memory_kb بیشینه مصرف تا همان لحظه را نشان می‌دهد. اگر Grant از مصرف بسیار بیشتر باشد، حافظه رزروشده می‌تواند Concurrency را محدود کند. اگر Grant کافی نباشد، عملگر Sort یا Hash ممکن است به tempdb Spill کند. بااین‌حال نتیجه‌گیری باید با Execution Plan، Warningهای Spill، Actual Row Count و چند اجرای نماینده تأیید شود.

برای ردیف‌های منتظر، wait_time_ms و requested_memory_kb را همراه Semaphore مربوط ببینید. ممکن است یک Query بزرگ صف را ایجاد کرده باشد یا تعداد زیادی Query متوسط حافظه را پر کرده باشند. is_next_candidate و wait_order سرنخ صف می‌دهند، اما مقادیر در طول زمان تغییر می‌کنند و نباید به عنوان اولویت دائمی ذخیره شوند.

در Query موازی، Worker و Memory Grant هم‌زمان اهمیت دارند. reserved_worker_count نشان می‌دهد موتور چه تعداد Worker را برای Plan رزرو کرده و reserved_node_bitmap محدوده NUMA را به‌صورت Bitmap مشخص می‌کند. Grant بزرگ با DOP بالا می‌تواند هم حافظه و هم Worker را تحت فشار قرار دهد؛ بنابراین مشاهده این ستون‌ها در کنار sys.dm_exec_query_parallel_workers دید دقیق‌تری می‌سازد.

قاعده کلیدی: ردیف منتظر را با grant_time برابر NULL پیدا کنید؛ Over-Grant را با مقایسه چندباره granted_memory_kb و max_used_memory_kb بررسی کنید؛ و هیچ‌گاه صرفاً با دیدن requested_memory_kb بزرگ، Query را مشکل‌دار اعلام نکنید.

ده مثال عملی و قابل اجرا

مثال ۱: نمایش Snapshot پایه Grantها

این نمونه ستون‌های ضروری را برای همه Requestهای دارای Memory Grant نمایش می‌دهد. ترتیب بر اساس مقدار درخواستی کمک می‌کند بزرگ‌ترین مصرف‌کنندگان احتمالی ابتدا دیده شوند، اما نتیجه باید با وضعیت انتظار و مصرف واقعی تفسیر شود.

SELECT
        session_id,
        request_id,
        grant_time,
        requested_memory_kb,
        granted_memory_kb,
        used_memory_kb,
        max_used_memory_kb
    FROM sys.dm_exec_query_memory_grants
    ORDER BY requested_memory_kb DESC;

نکتهٔ کاربردی: ردیف اول بزرگ‌تر است، اما ردیف دوم نسبت Grant به مصرف پایین‌تری دارد. برای تعیین مشکل، Plan، تعداد اجرای هم‌زمان و اثر بر صف را نیز ببینید.

مثال ۲: پیدا کردن Requestهای منتظر Grant

شرط grant_time IS NULL مستقیم‌ترین فیلتر برای صف Memory Grant است. مدت انتظار و ترتیب صف برای اولویت‌بندی Incident نمایش داده می‌شوند و Queryهای قدیمی‌تر در ابتدای نتیجه قرار می‌گیرند.

SELECT
        session_id,
        request_id,
        wait_time_ms,
        wait_order,
        is_next_candidate,
        requested_memory_kb,
        required_memory_kb,
        dop
    FROM sys.dm_exec_query_memory_grants
    WHERE grant_time IS NULL
    ORDER BY wait_time_ms DESC;

نکتهٔ کاربردی: خروجی بدون ردیف یعنی در همان لحظه Grant منتظر وجود ندارد. برای تشخیص الگوی گذرا Query را در چند بازه کنترل‌شده اجرا کنید.

مثال ۳: نمایش Queryهای دارای Grant فعال

در این Query فقط ردیف‌هایی انتخاب می‌شوند که Grant گرفته‌اند. درصد مصرف نسبت به Grant با NULLIF محاسبه می‌شود تا تقسیم بر صفر رخ ندهد و Queryهای کم‌بهره در انتهای نتیجه دیده شوند.

SELECT
        session_id,
        GrantedMemoryMB = CAST(granted_memory_kb / 1024.0 AS decimal(18, 2)),
        MaxUsedMemoryMB = CAST(max_used_memory_kb / 1024.0 AS decimal(18, 2)),
        GrantUsedPercent = CAST(100.0 * max_used_memory_kb /
            NULLIF(granted_memory_kb, 0) AS decimal(6, 2)),
        dop
    FROM sys.dm_exec_query_memory_grants
    WHERE grant_time IS NOT NULL
    ORDER BY GrantedMemoryMB DESC;

نکتهٔ کاربردی: درصد پایین فقط سرنخ است. اجرای فعلی ممکن است هنوز به مرحله پرمصرف نرسیده باشد؛ max_used_memory_kb و تاریخچه چند Snapshot معتبرتر از used_memory_kb لحظه‌ای است.

مثال ۴: اتصال Grant به متن SQL

sql_handle با sys.dm_exec_sql_text ترکیب می‌شود تا متن Batch عامل Grant دیده شود. OUTER APPLY ردیف DMV را حتی اگر متن از Cache خارج شده یا در دسترس نباشد حفظ می‌کند.

SELECT TOP (20)
        mg.session_id,
        mg.requested_memory_kb,
        mg.granted_memory_kb,
        mg.wait_time_ms,
        QueryText = st.text
    FROM sys.dm_exec_query_memory_grants AS mg
    OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
    ORDER BY mg.requested_memory_kb DESC;

نکتهٔ کاربردی: متن Batch ممکن است چند Statement داشته باشد. برای جدا کردن Statement فعال می‌توان offsetهای sys.dm_exec_requests را نیز اضافه کرد، اما Collector پایه را سبک نگه دارید.

مثال ۵: دریافت Plan فقط برای Grantهای بزرگ

به جای واکشی Plan همه ردیف‌ها، ابتدا Grantهای حداقل 256 مگابایتی فیلتر می‌شوند. این روش سربار را محدود می‌کند و Plan XML را برای مظنون‌های اصلی در اختیار DBA قرار می‌دهد.

SELECT TOP (10)
        mg.session_id,
        RequestedMemoryMB = CAST(mg.requested_memory_kb / 1024.0 AS decimal(18, 2)),
        mg.dop,
        qp.query_plan
    FROM sys.dm_exec_query_memory_grants AS mg
    OUTER APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
    WHERE mg.requested_memory_kb >= 256 * 1024
    ORDER BY mg.requested_memory_kb DESC;

نکتهٔ کاربردی: Plan را برای تخمین ردیف، MemoryGrantInfo، Sort، Hash و Warningهای Spill بررسی کنید. ذخیره مداوم XML حجیم در جدول مانیتورینگ توصیه نمی‌شود.

مثال ۶: تبدیل واحد و محاسبه زمان انتظار

برای گزارش Incident، کیلوبایت به مگابایت و میلی‌ثانیه به ثانیه تبدیل می‌شود. COALESCE روی wait_time_ms باعث می‌شود Grantهای فعال به‌جای NULL مقدار صفر نمایشی داشته باشند.

SELECT
        session_id,
        RequestedMB = CAST(requested_memory_kb / 1024.0 AS decimal(18, 2)),
        RequiredMB = CAST(required_memory_kb / 1024.0 AS decimal(18, 2)),
        GrantedMB = CAST(granted_memory_kb / 1024.0 AS decimal(18, 2)),
        WaitSeconds = CAST(COALESCE(wait_time_ms, 0) / 1000.0 AS decimal(18, 2))
    FROM sys.dm_exec_query_memory_grants
    ORDER BY WaitSeconds DESC, RequestedMB DESC;

نکتهٔ کاربردی: required_memory_kb حداقل شروع است و requested_memory_kb درخواست کامل. فاصله زیاد میان این دو می‌تواند در شرایط فشار باعث Forced Minimum Grant یا Spill شود.

مثال ۷: تحلیل رفتار NULL برای ردیف‌های منتظر

در ردیف منتظر granted_memory_kb یا grant_time می‌تواند NULL باشد. CASE وضعیت خوانا می‌سازد و COALESCE مقدار Grant نمایشی را صفر می‌کند، بدون آنکه NULL اصلی در منطق فیلتر از بین برود.

SELECT
        session_id,
        GrantState = CASE
            WHEN grant_time IS NULL THEN N'Waiting'
            ELSE N'Granted'
        END,
        GrantedMemoryKB = COALESCE(granted_memory_kb, 0),
        QueueID = queue_id,
        WaitOrder = wait_order
    FROM sys.dm_exec_query_memory_grants
    ORDER BY CASE WHEN grant_time IS NULL THEN 0 ELSE 1 END,
             wait_order;

نکتهٔ کاربردی: برای تشخیص انتظار همیشه grant_time IS NULL را مبنا قرار دهید؛ جایگزینی زودهنگام همه NULLها با صفر ممکن است معنای «ناموجود» را با مقدار واقعی صفر مخلوط کند.

مثال ۸: خلاصه Grantها به تفکیک Resource Pool

در سرور دارای Resource Governor، جمع‌بندی بر پایه pool_id نشان می‌دهد کدام Pool بیشترین حافظه را درخواست کرده و چند Request منتظر دارد. این Query برای مقایسه Workloadها مناسب است.

SELECT
        pool_id,
        RequestCount = COUNT_BIG(*),
        WaitingCount = SUM(CASE WHEN grant_time IS NULL THEN 1 ELSE 0 END),
        RequestedMemoryMB = CAST(SUM(requested_memory_kb) / 1024.0 AS decimal(18, 2)),
        GrantedMemoryMB = CAST(SUM(COALESCE(granted_memory_kb, 0)) / 1024.0 AS decimal(18, 2))
    FROM sys.dm_exec_query_memory_grants
    GROUP BY pool_id
    ORDER BY RequestedMemoryMB DESC;

نکتهٔ کاربردی: Aggregate روی DMV را با فاصله زمانی منطقی اجرا کنید. برای اتصال به Semaphore شرط pool_id و resource_semaphore_id را با هم به کار ببرید.

مثال ۹: شناسایی کاندیداهای Over-Grant

این فیلتر فقط Grantهای حداقل 100 مگابایت را بررسی می‌کند که بیشینه مصرفشان کمتر از یک‌چهارم Grant بوده است. نتیجه فهرست کاندیداست، نه حکم نهایی؛ باید در چند اجرای کامل و نماینده تکرار شود.

SELECT
        session_id,
        granted_memory_kb,
        max_used_memory_kb,
        UnusedMemoryKB = granted_memory_kb - max_used_memory_kb,
        query_cost,
        dop
    FROM sys.dm_exec_query_memory_grants
    WHERE grant_time IS NOT NULL
      AND granted_memory_kb >= 100 * 1024
      AND max_used_memory_kb * 4 < granted_memory_kb
    ORDER BY UnusedMemoryKB DESC;

نکتهٔ کاربردی: بررسی Statistics، Parameter Sensitivity، عرض ردیف و Memory Grant Feedback گام بعدی است. اجرای کوتاه یا Query ناتمام می‌تواند نسبت مصرف را موقتاً پایین نشان دهد.

مثال ۱۰: ذخیره Snapshot در Temp Table و ایندکس‌گذاری

برای چند تحلیل روی یک لحظه ثابت، داده لازم در Temp Table ذخیره می‌شود. این کار از تغییر نتیجه میان Queryهای بعدی جلوگیری می‌کند و ایندکس روی وضعیت انتظار و حجم درخواست، جست‌وجوی محلی را سریع می‌سازد.

DROP TABLE IF EXISTS #MemoryGrantSnapshot;
    
    SELECT
        CaptureTime = SYSDATETIME(),
        session_id,
        request_id,
        grant_time,
        wait_time_ms,
        requested_memory_kb,
        granted_memory_kb,
        max_used_memory_kb,
        pool_id
    INTO #MemoryGrantSnapshot
    FROM sys.dm_exec_query_memory_grants;
    
    CREATE INDEX IX_MemoryGrantSnapshot_Wait_Request
        ON #MemoryGrantSnapshot(grant_time, requested_memory_kb DESC);
    
    SELECT TOP (20) *
    FROM #MemoryGrantSnapshot
    ORDER BY CASE WHEN grant_time IS NULL THEN 0 ELSE 1 END,
             requested_memory_kb DESC;

نکتهٔ کاربردی: Temp Table فقط Snapshot همان Session است. برای Trend بلندمدت از جدول مانیتورینگ با سیاست نگهداری، Batch Insert و ایندکس حداقلی استفاده کنید.

خطاهای رایج

بیشتر برداشت‌های اشتباه از این DMV ناشی از نادیده گرفتن ماهیت لحظه‌ای آن یا یکسان دانستن درخواست، Grant و مصرف است. موارد زیر را پیش از هر تغییر تنظیمات کنترل کنید.

• فرض اینکه نتیجه خالی نشانه خرابی DMV است؛ ممکن است در لحظه هیچ Query نیازمند Grant فعال نباشد.
• استفاده از used_memory_kb به‌جای max_used_memory_kb برای قضاوت نهایی درباره Over-Grant.
• نادیده گرفتن NULL در grant_time و انجام محاسباتی که ردیف‌های منتظر را حذف می‌کند.
• دریافت Plan XML برای همه ردیف‌ها در Polling سریع و ایجاد سربار اضافی.
• کشتن Session منتظر بدون یافتن Queryهایی که حافظه موجود را اشغال کرده‌اند.
• مقایسه Grantهای Workloadهای متفاوت بدون توجه به pool_id و Resource Governor.
• تعبیر requested_memory_kb بزرگ به‌عنوان مصرف فیزیکی قطعی؛ مقدار used و max_used را نیز ببینید.
• تغییر Max Server Memory یا MAXDOP بر پایه یک Snapshot منفرد و بدون Baseline.

ملاحظات Performance

خود Microsoft هشدار می‌دهد Queryهای DMV دارای ORDER BY یا Aggregate می‌توانند مصرف حافظه را افزایش دهند و به مسئله‌ای که بررسی می‌کنند کمک کنند. در Collector دائمی ابتدا ستون‌های کم و فیلترهای مشخص انتخاب کنید. واکشی SQL Text معمولاً از Plan XML سبک‌تر است؛ Plan را فقط برای ردیف‌های بزرگ، منتظر یا دارای نسبت غیرعادی دریافت کنید.

فاصله نمونه‌برداری باید با هدف هماهنگ باشد. برای Incident فعال ممکن است نمونه‌های کوتاه‌مدت لازم باشد، اما Polling دائمی هر ثانیه با Plan XML مناسب نیست. تاریخچه عددی سبک را نگه دارید و جزئیات حجیم را در رخداد یا با Sampling ذخیره کنید.

Query مانیتورینگ را از همان Resource Pool و بار Production آگاهانه اجرا کنید. Aggregateهای سنگین، Sortهای غیرضروری و ذخیره طولانی متن کامل Batch می‌توانند CPU، حافظه و فضای ذخیره‌سازی را مصرف کنند. Collector باید خودش Baseline و زمان اجرای قابل‌قبول داشته باشد.

• ستون‌های مورد نیاز را صریح انتخاب کنید و از SELECT * در Job دائمی بپرهیزید.
• ابتدا Snapshot خلاصه، سپس Text و Plan هدفمند بگیرید.
• تعداد ردیف و دوره نگهداری تاریخچه را محدود کنید.
• Collector را با حساب دارای حداقل مجوز لازم اجرا کنید.
• زمان اجرای Collector و Logical Read آن را نیز مانیتور کنید.
• برای Alert از چند نمونه متوالی و رشد wait_time_ms استفاده کنید.

بهترین روش‌ها و کاربرد سازمانی

در محیط سازمانی این DMV باید بخشی از Runbook باشد. یک Snapshot خام بدون زمان، Wait Statistics و مشخصات Instance ارزش پس از رخداد محدودی دارد. حداقل CaptureTime، Session، Pool، درخواست، Grant، مصرف، DOP و وضعیت انتظار را ذخیره کنید و هنگام Alert متن Query را برای ردیف‌های هدف ثبت کنید.

اقدام اصلاحی را از شواهد Plan استخراج کنید. تخمین Cardinality اشتباه ممکن است با Statistics یا بازنویسی Predicate اصلاح شود؛ Sort غیرضروری با Index مناسب حذف شود؛ عرض زیاد ردیف با انتخاب ستون‌های لازم کاهش یابد؛ و Concurrency گزارش‌ها با Resource Governor یا زمان‌بندی مدیریت شود. افزایش RAM تنها یکی از گزینه‌هاست.

در سیستم‌های حساس، تغییر را روی Workload نماینده آزمایش و با Query Store، Duration، CPU، Logical Reads، Spill و Grant مقایسه کنید. اگر تحلیل درون تیمی دشوار است، آموزش تخصصی یا بازبینی Performance می‌تواند به ساخت Collector امن و انتخاب اصلاح کم‌ریسک کمک کند.

1. Baseline چند دوره کاری را برای requested، granted، max used و wait time بسازید.
2. هنگام Alert ابتدا Grantهای منتظر و سپس دارندگان بزرگ حافظه را ثبت کنید.
3. Plan و SQL Text را فقط برای Sessionهای منتخب واکشی کنید.
4. Cardinality، Statistics، Spill، Parameter Sensitivity و DOP را بررسی کنید.
5. یک تغییر محدود انجام دهید و نتیجه را با Baseline مقایسه کنید.
6. Runbook، Threshold و روش بازگشت را پس از هر Incident به‌روزرسانی کنید.

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

پرسش ۱: sys.dm_exec_query_memory_grants چه ردیف‌هایی را برمی‌گرداند؟

هر Query که Memory Grant خواسته و هنوز منتظر است یا Grant گرفته، در لحظه مشاهده می‌شود. Query بدون نیاز به Grant در این نما ظاهر نمی‌شود. ردیف‌ها زنده و گذرا هستند و با پایان Request حذف می‌شوند؛ بنابراین برای تحلیل پس از رخداد باید Snapshot زمان‌دار داشته باشید.

پرسش ۲: چگونه Query منتظر حافظه را تشخیص دهیم؟

شرط grant_time IS NULL را اعمال و wait_time_ms، requested_memory_kb، wait_order و is_next_candidate را مشاهده کنید. سپس Resource Semaphore متناظر و Wait Type RESOURCE_SEMAPHORE را بررسی کنید. متن SQL و Plan Query منتظر و دارندگان بزرگ Grant هر دو برای یافتن علت لازم‌اند.

پرسش ۳: این DMV در مانیتورینگ تجاری چه ارزشی دارد؟

صف Memory Grant می‌تواند زمان پاسخ گزارش، API و عملیات مالی را بدون اشباع آشکار CPU افزایش دهد. ثبت این DMV امکان می‌دهد پیش از نقض SLA، رشد صف و Grantهای غیرعادی دیده شود. طراحی Threshold باید با Baseline بار واقعی و اهمیت سرویس‌های کسب‌وکار هماهنگ باشد.

پرسش ۴: چه زمانی برای بهینه‌سازی Memory Grant مشاوره لازم است؟

اگر Incident تکرار می‌شود، Queryهای متعدد Plan حساس دارند یا تغییرات سطح سرور پرریسک است، بازبینی تخصصی مفید خواهد بود. تحلیل باید Query Store، Planها، Statistics، tempdb، Resource Governor و Concurrency را یکجا ببیند. هدف ارائه اصلاح قابل‌آزمایش است، نه صرفاً پیشنهاد افزایش حافظه.

پرسش ۵: تفاوت requested_memory_kb و ideal_memory_kb چیست؟

requested_memory_kb مقدار Grant درخواست‌شده برای اجرای Plan است؛ ideal_memory_kb برآورد حافظه‌ای است که عملیات را تا حد ممکن در حافظه نگه می‌دارد. required_memory_kb نیز حداقل شروع است. رابطه این سه مقدار تحت فشار و بر اساس تصمیم Resource Semaphore می‌تواند به Grant واقعی متفاوتی منجر شود.

پرسش ۶: چگونه Collector این DMV را پیاده‌سازی کنیم؟

ابتدا ستون‌های عددی و CaptureTime را با فاصله معقول در جدول باریک ذخیره کنید. هنگام مشاهده Grant منتظر یا مقدار بزرگ، Text و Plan را به‌صورت هدفمند جمع‌آوری کنید. برای پروژه سازمانی، Retention، ایندکس، امنیت متن Query و هزینه Collector باید از ابتدا طراحی و تست شوند.

پرسش ۷: چرا محاسبه درصد مصرف گاهی NULL می‌شود؟

granted_memory_kb برای ردیف منتظر می‌تواند NULL یا در محاسبات خاص صفر باشد. استفاده از NULLIF از تقسیم بر صفر جلوگیری می‌کند. NULL در اینجا اطلاعات معنی‌دار است و نباید بدون توجه به وضعیت Grant به صفر تبدیل شود؛ ابتدا ردیف‌های منتظر و اعطاشده را تفکیک کنید.

پرسش ۸: آیا خواندن Plan از این DMV سنگین است؟

خود ردیف‌های عددی سبک‌ترند، اما sys.dm_exec_query_plan می‌تواند XML بزرگ تولید کند. واکشی Plan برای همه ردیف‌ها در Polling سریع توصیه نمی‌شود. ابتدا با حجم Grant، انتظار یا نسبت مصرف فیلتر کنید و سپس Plan چند کاندیدا را بگیرید تا ابزار تشخیصی سربار مسئله را تشدید نکند.

پرسش ۹: بهترین معیار برای تشخیص Over-Grant چیست؟

نسبت granted_memory_kb به max_used_memory_kb در چند اجرای کامل و نماینده سرنخ اصلی است، ولی به تنهایی کافی نیست. Plan، Actual Row Count، Parameterها، Memory Grant Feedback و اثر بر Concurrency باید بررسی شوند. used_memory_kb لحظه‌ای ممکن است پیش از رسیدن Query به مرحله پرمصرف پایین باشد.

پرسش ۱۰: سازگاری نسخه و مجوز این DMV چگونه است؟

نمای اصلی در نسخه‌های پشتیبانی‌شده SQL Server وجود دارد، اما ستون‌های Worker از SQL Server 2016 اضافه شده‌اند. SQL Server 2022 و بعد از آن معمولاً VIEW SERVER PERFORMANCE STATE می‌خواهد و نسخه‌های قدیمی‌تر VIEW SERVER STATE. در Azure SQL Database سطح مجوز و فیلتر اطلاعات Tenant متفاوت است.

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

سؤال مصاحبه ۱: تفاوت required، requested، granted و ideal memory چیست؟

required حداقل شروع، requested درخواست محاسبه‌شده، granted مقدار اعطاشده توسط موتور و ideal مقدار مطلوب برای نگه داشتن عملیات در حافظه است. مقایسه آن‌ها نشان می‌دهد Query تحت فشار چگونه اجرا شده است.

سؤال مصاحبه ۲: برای یافتن متن Query از چه ستونی استفاده می‌کنید؟

sql_handle را با sys.dm_exec_sql_text از طریق APPLY ترکیب می‌کنم. برای Plan نیز plan_handle به sys.dm_exec_query_plan داده می‌شود، اما واکشی XML را به کاندیداهای منتخب محدود می‌کنم.

سؤال مصاحبه ۳: چرا max_used_memory_kb از used_memory_kb مهم‌تر است؟

used مقدار همان لحظه است و ممکن است Query هنوز به مرحله پرمصرف نرسیده باشد. max_used بیشینه مصرف تا زمان Snapshot را می‌دهد و برای سنجش بهره‌برداری از Grant سرنخ پایدار‌تری است.

سؤال مصاحبه ۴: Wait Type معمول ردیف منتظر چیست؟

در فشار Execution Memory معمولاً RESOURCE_SEMAPHORE دیده می‌شود. این Wait را نباید با RESOURCE_SEMAPHORE_QUERY_COMPILE که به Gateهای Compile مربوط است اشتباه گرفت.

سؤال مصاحبه ۵: چگونه اثر Resource Governor را می‌بینید؟

pool_id و group_id Request را ثبت می‌کنم و برای تطبیق با Semaphore از pool_id همراه resource_semaphore_id استفاده می‌کنم. سپس ظرفیت، Waiter و سیاست Pool را بررسی می‌کنم.

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

• grant_time برای تفکیک منتظر و اعطاشده بررسی شده است.
• wait_time_ms در چند Snapshot زمان‌دار ثبت شده است.
• requested، required، granted و max used با واحد یکسان مقایسه شده‌اند.
• SQL Text و Plan فقط برای کاندیداهای هدف واکشی شده‌اند.
• Pool، Group، Semaphore و DOP در تحلیل لحاظ شده‌اند.
• Spill، Cardinality Estimate و Statistics بررسی شده‌اند.
• Workerهای Query موازی با DMV مربوط تطبیق داده شده‌اند.
• هیچ تغییر سطح سرور بدون Baseline و برنامه بازگشت انجام نشده است.

جمع‌بندی

sys.dm_exec_query_memory_grants دقیق‌ترین نمای زنده برای دنبال کردن مسیر Memory Grant در سطح Request است. با آن می‌توان صف، مدت انتظار، حجم درخواست و Grant، مصرف واقعی، Plan و Resource Pool را کنار هم دید. استفاده درست از این اطلاعات تفاوت میان Query بزرگ اما مشروع، Over-Grant مزمن و کمبود واقعی ظرفیت را روشن می‌کند.

ارزش عملی DMV زمانی کامل می‌شود که چند Snapshot، Wait Statistics، Resource Semaphore و Execution Plan ترکیب شوند. Collector سبک بسازید، Plan را هدفمند واکشی کنید و اصلاح را با معیارهای قبل و بعد بسنجید. این رویکرد هم برای Incident فوری و هم برای پروژه‌های پایدارسازی Performance قابل استفاده است.

برای تکمیل تصویر عیب‌یابی، به مقاله مادر DMVهای Query Memory و نقشه تشخیص یکپارچه بازگردید.