Cara Cek query yang mengkonsumsi memory

Siap bro 👍 ini kasus klasik SQL Server di Windows dan belum tentu error. Kita bedah pelan-pelan ya:
cara cek SQL Server lagi ngapain sampai makan RAM 90% + mana yang normal dan mana yang bahaya.

1️⃣ Pahami dulu: SQL Server “suka” makan RAM (by design)

SQL Server akan mengambil RAM sebanyak mungkin untuk:

Buffer Pool (cache data page)
Plan Cache (execution plan)
Query execution memory

👉 Jadi 90% RAM terpakai ≠ langsung masalah
Masalahnya kalau:

OS kehabisan memory
App lain jadi lambat
Ada memory leak / query liar

2️⃣ Cek dari SQL Server: RAM dipakai buat apa

🔍 Cek distribusi memory SQL Server

SELECT 
    type, 
    SUM(pages_kb)/1024 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_mb DESC;

👉 Fokus ke:

MEMORYCLERK_SQLBUFFERPOOL → cache data (normal)
CACHESTORE_SQLCP / CACHESTORE_OBJCP → plan cache
Clerk aneh besar → red flag

3️⃣ Cek buffer pool (data cache)

SELECT 
    COUNT(*) * 8 / 1024 AS BufferPool_MB
FROM sys.dm_os_buffer_descriptors;

Kalau ini besar → SQL lagi nyimpen data di RAM (normal, malah bagus untuk performa).

4️⃣ Cek query yang lagi makan memory BESAR

Ini penting 👇

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.granted_query_memory,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.granted_query_memory > 0
ORDER BY r.granted_query_memory DESC;

👉 Kalau ada:

granted_query_memory besar banget
query lama + status running / suspended

➡️ Itu tersangka utama RAM habis

5️⃣ Cek top query paling boros memory (historical)

SELECT TOP 10
    qs.max_grant_kb / 1024 AS max_memory_mb,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_time_ms,
    SUBSTRING(t.text, qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1 
              THEN LEN(t.text) 
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.max_grant_kb DESC;

➡️ Ini buat tahu query mana historically rakus RAM

6️⃣ Cek memory pressure (ini KRUSIAL)

SELECT 
    total_physical_memory_kb/1024 AS total_ram_mb,
    available_physical_memory_kb/1024 AS free_ram_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

Kalau:

available_physical_memory_kb kecil
system_memory_state_desc = LOW_MEMORY

⚠️ Bahaya → SQL terlalu rakus, OS tercekik

7️⃣ Cek setting MAX MEMORY SQL Server

Ini sering jadi biang kerok di Windows Server 👇

EXEC sys.sp_configure 'max server memory (MB)';

Kalau:

Nilai = hampir sama dengan RAM fisik
Atau default (unlimited)

❌ Itu salah untuk server aplikasi

🔧 Best practice (rule kasar)

Sisakan 4–8 GB untuk OS
Sisanya baru buat SQL

Contoh:

RAM 32 GB → max SQL = 24–26 GB

Set:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sys.sp_configure 'max server memory (MB)', 26000;
RECONFIGURE;

8️⃣ Cek dari Windows (biar korelatif)

Di Task Manager → Performance → Memory:

Lihat Available
Kalau < 1–2 GB → OS udah megap-megap

Atau PowerShell:

Get-Counter '\Memory\Available MBytes'

9️⃣ Kesimpulan cepat (cheat sheet)

Kondisi	Aman / Bahaya
RAM SQL 90%, OS masih lega	✅ Normal
RAM SQL 90%, OS low memory	❌ Bahaya
Buffer Pool dominan	✅ Normal
Query grant memory besar & lama	❌ Investigasi
Max memory SQL unlimited	❌ Harus dibatasi

10️⃣ Mau lanjut lebih spesifik?

Kalau lo mau, bro:

Nanti gue bisa bantu:

Hitung max memory ideal
Analisa hasil DMV lo satu-satu 🔥