罠①: メモリ grant 不足の検知 (DMV クエリ)

-- 今この瞬間メモリ grant を持ってる / 待ってるクエリ
SELECT
    mg.session_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb / 1024  AS requested_mb,
    mg.granted_memory_kb  / 1024  AS granted_mb,
    mg.required_memory_kb / 1024  AS required_mb,
    mg.used_memory_kb     / 1024  AS used_mb,
    mg.queue_id,                                -- NULL なら grant 済・値あれば待ち
    DATEDIFF(SECOND, mg.request_time, GETDATE()) AS waited_sec,
    SUBSTRING(qt.text, 1, 120) AS query_head
FROM sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) qt
ORDER BY mg.request_time;