Pernah mengalami skenario ini: query yang berjalan mulus di environment development tiba-tiba timeout saat dipush ke production di AWS RDS. Anda cek CPU utilization, masih di kisaran 40 persen. Cek disk IOPS, belum menyentuh limit. Namun query tetap lambat, dan setelah ditelusuri lebih jauh, TempDB membengkak secara tidak wajar. Masalahnya bukan pada index yang hilang, bukan pada logic query yang salah, melainkan pada cara SQL Server mengalokasikan memori sebelum eksekusi dimulai.
Banyak developer dan DBA mengabaikan mekanisme ini karena sifatnya yang tersembunyi di balik execution plan. Padahal, SQL Server modern sudah menyertakan fitur yang secara otomatis belajar dari eksekusi sebelumnya untuk menyesuaikan alokasi memori. Namanya Memory Grant Feedback. Di environment cloud seperti AWS RDS, di mana resource bersifat finite dan biaya dihitung per jam, mengaktifkan dan memahami mekanisme ini bisa menjadi pembeda antara instance yang stabil dan instance yang boros biaya tanpa alasan teknis yang jelas.

Apa Itu Memory Grant dan Mengapa Sering Salah Hitung

Setiap query yang membutuhkan operasi pengurutan, penggabungan tabel, atau agregasi data memerlukan ruang memori kerja. SQL Server mengalokasikan ruang ini sebelum query benar-benar berjalan. Alokasi ini disebut memory grant.
Analoginya sederhana: memory grant adalah anggaran ruang meeting. Jika anggaran terlalu besar, ruang tersebut terbuang dan tidak bisa dipakai meeting lain. Jika anggaran terlalu kecil, peserta meeting harus pindah ke ruang cadangan yang lebih lambat. Dalam konteks SQL Server, ruang cadangan itu adalah disk. Ketika query tidak mendapat memori yang cukup, engine akan melakukan spill to TempDB. Operasi yang seharusnya berjalan di memori dipindahkan ke disk, dan latency meningkat secara drastis.
Penyebab utama memory grant meleset adalah keterbatasan statistik optimizer. SQL Server membuat estimasi jumlah baris dan ukuran data berdasarkan statistik indeks. Ketika data mengalami skew, atau ketika parameter yang dikirim pertama kali tidak mewakili distribusi data keseluruhan (parameter sniffing), estimasi optimizer menjadi tidak akurat. Hasilnya: grant terlalu besar atau terlalu kecil, dan performa sistem terdampak.
Evolusi Fitur Memory Grant Feedback
Microsoft tidak langsung memberikan solusi sempurna dalam satu versi. Mekanisme ini dikembangkan secara bertahap, dan setiap versi membawa perbaikan yang relevan dengan workload modern.

1. SQL Server 2017 (Compatibility Level 140): Memperkenalkan Batch Mode Memory Grant Feedback. Fokus pada workload analitik berat yang menggunakan columnstore dan operasi batch.
2. SQL Server 2019 (Compatibility Level 150): Memperluas cakupan ke Row Mode. Query OLTP standar yang sebelumnya tidak mendapat manfaat dari MGF kini bisa menyesuaikan grant secara otomatis.
3. SQL Server 2022 (Compatibility Level 160): Menambahkan Persistence dan Percentile Mode. Feedback tidak lagi hilang saat plan cache di-evict, dan engine menggunakan distribusi historis (misalnya persentil ke-90) alih-alih hanya mengandalkan eksekusi terakhir.

Catatan penting untuk pengguna AWS RDS: database yang baru dibuat di RDS SQL Server 2022 secara default menggunakan compatibility level tertinggi. Namun, database yang di-migrate atau di-upgrade dari versi lama akan mempertahankan level kompatibilitas lama. Memory Grant Feedback tidak akan aktif hingga compatibility level dinaikkan sesuai versi engine yang digunakan.

Cara Kerja Feedback Loop

Mekanisme ini berjalan secara otomatis di background. Alurnya dapat disederhanakan menjadi tiga tahap:

1. Eksekusi pertama mencatat jumlah memori yang sebenarnya digunakan, bukan sekadar yang dialokasikan.
2. Data aktual ini disimpan sebagai feedback. Pada eksekusi berikutnya dengan execution plan yang sama, optimizer menyesuaikan grant berdasarkan catatan tersebut.
3. Di mode percentile, engine tidak hanya melihat satu eksekusi terakhir. Ia menganalisis distribusi historis penggunaan memori dan memilih nilai yang aman (misalnya 90th percentile) untuk mencegah spill sekaligus menghindari waste.[/li]

Proses ini mirip dengan sistem navigasi yang menyesuaikan rute berdasarkan pola kemacetan historis, bukan hanya mengandalkan peta statis. Tidak ada perubahan kode yang diperlukan dari sisi aplikasi. Engine menangani penyesuaian secara internal.

Implementasi di AWS RDS: Langkah demi Langkah

Karena AWS RDS adalah managed service, beberapa konfigurasi yang biasanya dilakukan via SSMS GUI harus dieksekusi menggunakan T-SQL. Berikut urutan konfigurasi yang perlu dilakukan.

Langkah pertama adalah memeriksa compatibility level database.

SELECT

name,

compatibility_level,

CASE compatibility_level

WHEN 140 THEN 'SQL Server 2017'

WHEN 150 THEN 'SQL Server 2019'

WHEN 160 THEN 'SQL Server 2022'

ELSE 'Unknown/Legacy'

END AS VersionLabel

FROM sys.databases

WHERE name = 'NamaDatabaseAnda';

Jika nilai compatibility level lebih rendah dari versi engine RDS yang Anda jalankan, naikkan level tersebut.

ALTER DATABASE [NamaDatabaseAnda]

SET COMPATIBILITY_LEVEL = 160;

Untuk mengaktifkan fitur persistence pada SQL Server 2022, Query Store harus dalam keadaan aktif. Secara default, Query Store tidak selalu menyala di instance RDS.

ALTER DATABASE [NamaDatabaseAnda]

SET QUERY_STORE = ON (

OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

MAX_STORAGE_SIZE_MB = 1024,

INTERVAL_LENGTH_MINUTES = 60

);

Setelah konfigurasi dasar selesai, Anda dapat memantau aktivitas memory grant menggunakan DMV standar yang tetap berfungsi penuh di RDS.

-- Memantau query yang sedang menunggu atau menggunakan memory grant

SELECT

session_id,

requested_memory_kb,

granted_memory_kb,

used_memory_kb,

ideal_memory_kb,

CASE WHEN granted_memory_kb = 0 THEN 'Pending' ELSE 'Granted' END AS Status

FROM sys.dm_exec_query_memory_grants

ORDER BY requested_memory_kb DESC;

Untuk melihat histori penyesuaian feedback (khusus SQL Server 2022+ dengan Query Store aktif):

SELECT

qsqt.query_sql_text,

mf.initial_memory_grant_kb,

mf.adjusted_memory_grant_kb,

mf.feedback_cycle_count,

mf.last_execution_time

FROM sys.query_store_query_feedback mf

JOIN sys.query_store_query_text qsqt

ON mf.query_text_id = qsqt.query_text_id

ORDER BY mf.last_execution_time DESC;

The Cost Angle: Optimasi Sebelum Scale-Up

Di environment on-premise, menambah RAM server sering menjadi respons pertama ketika performa turun. Di AWS RDS, respons yang sama berarti upgrade instance class. Perubahan ini berdampak langsung pada tagihan bulanan. Memory Grant Feedback sering kali menjadi penghubung yang terlewat antara tuning teknis dan efisiensi biaya.
Ketika query secara konsisten mendapatkan memori yang terlalu kecil, spill ke TempDB meningkat. IOPS disk naik, latency meningkat, dan aplikasi mulai timeout. Solusi instan yang banyak dipilih adalah upgrade instance untuk mendapatkan lebih banyak memori dan IOPS. Padahal, jika grant dialokasikan dengan tepat, spill dapat dihilangkan tanpa menambah resource fisik.
Contoh simulasi realistis untuk workload batch reporting di us-east-1:

1. Sebelum optimasi: db.m5.2xlarge, duration query 80-100 detik, spill konsisten 2-3 GB, biaya sekitar 730 USD per bulan.
2. Setelah aktivasi MGF + compatibility level 160 + Query Store: db.m5.large, duration turun ke 1-2 detik, spill nol, biaya sekitar 182 USD per bulan.
3. Penghematan ini bukan hasil ajaib. Ia terjadi karena engine tidak lagi membuang memori untuk estimasi yang keliru, dan query tidak lagi terbebani operasi disk yang seharusnya tidak diperlukan.

Catatan penting: MGF bukan pengganti query tuning. Jika query Anda memang kompleks dan membutuhkan memori besar karena join bertingkat, sorting data dalam jumlah masif, atau aggregasi tanpa filter yang tepat, instance besar tetap diperlukan. MGF hanya memastikan alokasi yang diberikan benar-benar digunakan secara efisien.

Studi Kasus: Before dan After di RDS Production

Sebuah tim engineering menjalankan pipeline ETL harian pada RDS SQL Server 2022 instance db.m5.xlarge. Pipeline terdiri dari 12 job paralel yang memproses data transaksi. Setelah volume data meningkat 40 persen, pipeline mulai memakan waktu lebih dari 90 menit. Monitoring menunjukkan FreeableMemory turun drastis saat job berjalan, dan beberapa job mengalami timeout karena menunggu memory grant.
Setelah melakukan audit execution plan, ditemukan bahwa 7 dari 12 job mengalami memory grant over-estimation, sementara 3 job lainnya under-estimation dan melakukan spill. Tim menaikkan compatibility level ke 160, mengaktifkan Query Store, dan membiarkan engine menjalankan feedback loop selama tiga hari eksekusi.

Hasil yang terukur:

1. Durasi pipeline turun dari 94 menit menjadi 28 menit.
2. Spill to TempDB berkurang 96 persen.
3. FreeableMemory stabil di kisaran 60-70 persen selama eksekusi puncak.
4. Concurrency berhasil ditingkatkan dari 12 job paralel menjadi 18 job paralel tanpa menambah instance size.
5. Dampak bisnisnya langsung terasa. Window batch processing menyusut, laporan pagi tersedia lebih awal, dan tim dapat menunda rencana upgrade instance yang diperkirakan akan menambah biaya operasional sekitar 350 USD per bulan.

Pitfall dan Best Practices di Environment RDS

Meskipun berjalan otomatis, Memory Grant Feedback memiliki batasan yang perlu dipahami agar tidak menimbulkan ekspektasi yang keliru.
Query yang menggunakan OPTION(RECOMPILE) tidak akan mendapat manfaat dari MGF. Hint tersebut memaksa optimizer membuat execution plan baru setiap kali query dijalankan. Karena plan tidak di-cache, tidak ada histori yang bisa disimpan untuk penyesuaian berikutnya.

Feedback dapat dinonaktifkan secara otomatis oleh engine jika terjadi osilasi grant yang berlebihan. Jika penyesuaian memori terus-menerus berubah signifikan antar eksekusi, engine akan mengunci grant pada nilai default untuk mencegah ketidakstabilan. Anda dapat mendeteksi kondisi ini melalui Extended Event memory_grant_feedback_loop_disabled.

Di RDS Multi-AZ, perubahan compatibility level dan Query Store bersifat database-level dan akan replikasi ke standby instance. Namun, pengujian penyesuaian memori tetap disarankan dilakukan di environment cloning terlebih dahulu. Buat snapshot database, restore sebagai instance terpisah, dan validasi dampaknya sebelum apply ke production.
Gunakan DMV sys.dm_exec_query_memory_grants secara berkala untuk memantau antrian grant. Jika nilai waiting for memory grant tinggi secara konsisten, kemungkinan ada bottleneck di konfigurasi max server memory atau instance class yang memang sudah tidak sesuai dengan workload.

Dokumentasikan setiap perubahan compatibility level dalam change management. Perubahan ini memengaruhi optimizer behavior secara luas, termasuk cardinality estimation dan plan generation. Pastikan tim aplikasi aware terhadap kemungkinan perubahan execution plan setelah upgrade level.

Memory Grant Feedback adalah contoh bagaimana engine database modern bergerak dari konfigurasi statis ke penyesuaian dinamis. Di AWS RDS, di mana Anda tidak memiliki akses penuh ke layer sistem operasi, mengandalkan fitur built-in seperti ini menjadi strategi paling efisien untuk menjaga stabilitas performa.
Fitur ini bukan solusi ajaib untuk query yang buruk. Ia adalah penguat untuk tuning yang sudah dilakukan dengan benar. Jika statistik indeks terjaga, parameterized query digunakan dengan tepat, dan compatibility level sudah sesuai, MGF akan bekerja di background untuk memastikan memori tidak terbuang dan query tidak terhambat oleh spill yang tidak perlu.

Periksa instance RDS Anda saat ini. Jika compatibility level masih di bawah 150, atau Query Store belum aktif, pertimbangkan untuk melakukan penyesuaian di environment staging terlebih dahulu. Bagikan hasil pengamatan Anda di thread ini. Sering kali, bottleneck yang terasa seperti masalah infrastruktur ternyata hanya menunggu satu konfigurasi yang tepat.