Dokumen ini menjelaskan cara meninjau dan mengoptimalkan instance Cloud SQL untuk SQL Server jika instance tersebut teridentifikasi
oleh pemberi rekomendasi instance yang tidak memadai sebagai instance yang mengalami pemakaian memori yang tinggi.
Objek ini pada disk yang dapat dimuat ulang, seperti halaman database dan prosedur yang disimpan. Hasilnya,
SQL Server dapat menambah dan mengurangi objek ini berdasarkan pemakaian memori. Cache mencakup kumpulan buffer dan cache rencana.
Memori tetap
Memori tetap dapat bertambah dan menyusut. Ini hanya akan menyusut jika tidak digunakan; misalnya, saat jumlah koneksi menurun atau jumlah kueri yang dieksekusi berkurang.
Ini berbeda dengan cache. Jika memori tetap tidak cukup, SQL Server dapat kehabisan memori.
Memori tetap mencakup memori koneksi dan hibah memori.
Overhead SQL Server
Overhead SQL Server mencakup thread dan stack.
OLTP Dalam Memori
OLTP Dalam Memori mencakup tabel Dalam Memori dan grup file Dalam Memori,
Konsumsi memori oleh SQL Server dikontrol dengan menetapkan maximum server memory dan memory.memory.limitmb. Parameter memory.memory.limitmb ditetapkan oleh Cloud SQL secara otomatis.
Sebaiknya Anda membiarkan Cloud SQL mengelola nilai flag ini.
Jika Anda harus mengelola nilai ini secara manual, gunakan formula penggunaan max_server_memory (mb)
yang diuraikan di Praktik terbaik
untuk membantu mencegah SQL Server menggunakan semua memori.
Untuk mengetahui informasi selengkapnya, lihat Flag khusus.
Pantau flag Page life expectancy.
Page life expectancy menunjukkan jumlah waktu, dalam detik, saat halaman terlama tetap berada di kumpulan buffer.
Nilai ini harus lebih dari 300 seperti yang direkomendasikan oleh Microsoft. Jika secara konsisten turun di bawah
300, ini dapat menjadi indikasi bahwa instance menghadapi penggunaan memori yang tinggi.
Jalankan kueri berikut untuk memantau Page life expectancy.
SELECT
[object_name],
[counter_name],
[cntr_value]FROM
sys.dm_os_performance_counters
WHERE
[object_name]LIKE
'%Manager%'AND
[counter_name]='Page life expectancy'
Periksa tanda Memory Grants Pending.
Memory Grants Pending menentukan jumlah total proses yang menunggu pemberian memori ruang kerja.
Jalankan kueri berikut untuk memeriksa Memory Grants Pending. Jika kueri ini secara konsisten menunjukkan hibah yang tertunda,
berarti ini menandakan penggunaan memori yang tinggi. Anda dapat menguranginya dengan membuat kueri agar database
menunggu dan menyesuaikan pernyataan apa pun yang menunggu memori.
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-08-19 UTC."],[],[],null,["# Optimize high memory usage in instances\n\n\u003cbr /\u003e\n\n[MySQL](/sql/docs/mysql/optimize-high-memory-usage \"View this page for the MySQL database engine\") \\| [PostgreSQL](/sql/docs/postgres/optimize-high-memory-usage \"View this page for the PostgreSQL database engine\") \\| SQL Server\n\n\u003cbr /\u003e\n\nThis document explains how to review and optimize a Cloud SQL for SQL Server instance if that instance is identified\nby the underprovisioned instance recommender as having high memory consumption.\n\nSQL Server memory\n-----------------\n\nSQL Server memory can be divided into the following:\n\n- [Caches](/sql/docs/sqlserver/optimize-high-memory-usage#cache)\n- [Fixed memory](/sql/docs/sqlserver/optimize-high-memory-usage#fixed-memory)\n- [SQL Server overhead](/sql/docs/sqlserver/optimize-high-memory-usage#sql-server-overhead)\n- [In-Memory online transactional processing (OLTP)](/sql/docs/sqlserver/optimize-high-memory-usage#oltp)\n\n### Caches\n\nThese are objects on a disk that can be reloaded, such as database pages and stored procedures. As a result,\nthe SQL Server can grow and shrink these objects based on memory utilization. Caches include buffer pools and plan caches.\n\n### Fixed memory\n\nFixed memory can grow and shrink. It only shrinks when not in use; for example, when the number of connections drops or the number of queries executing decreases.\nIt's different from caches. If there is not enough fixed memory, SQL Server can run out of memory.\nFixed memory includes connection memory and memory grants.\n\n### SQL Server overhead\n\nSQL Server overhead includes threads and stacks.\n\n### In-Memory OLTP\n\nIn-Memory OLTP includes In-Memory tables and In-Memory filegroups.\n\nThe memory consumption by SQL Server is controlled by setting `maximum server memory` and `memory.memory.limitmb`. The `memory.memory.limitmb` parameter is set by Cloud SQL automatically.\n\nTo learn more about `memory.memory.limitmb`, see the [Microsoft documentation](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver16#memorylimit).\n\nMemory optimization options\n---------------------------\n\nTo determine if an instance needs more memory tuning, do the following:\n\n- [Check the value of the `max server memory (mb)`](/sql/docs/sqlserver/flags#special-flags) flag.\n\n We recommend you let Cloud SQL manage the value of this flag.\n If you must manually manage this value, use the `max_server_memory (mb)`\n usage formula outlined on [Best practices](/sql/docs/sqlserver/best-practices#sqlserver_settings)\n to help prevent SQL Server from consuming all memory.\n\n For more information, see [Special flags](/sql/docs/sqlserver/flags#special-flags).\n- Monitor the `Page life expectancy` flag.\n\n `Page life expectancy` indicates the amount of time, in seconds, that the oldest page stays in the buffer pool.\n This value should be more than 300 as recommended by Microsoft. If it consistently falls under\n 300, it could be an indication that the instance is facing high memory utilization.\n Run the following query to monitor `Page life expectancy`. \n\n ```bash\n SELECT\n [object_name],\n [counter_name],\n [cntr_value]\n FROM\n sys.dm_os_performance_counters\n WHERE\n [object_name]\n LIKE\n '%Manager%'\n AND\n [counter_name] = 'Page life expectancy'\n \n ```\n- Check the `Memory Grants Pending` flag.\n\n `Memory Grants Pending` specifies the total number of processes waiting for a workspace memory grant.\n Run the following query to check `Memory Grants Pending`. If this query consistently shows grants pending,\n then it indicates high memory utilization. You can reduce it by querying the database\n waits and tuning any statement that's waiting on memory. \n\n ```bash\n SELECT\n @@SERVERNAME AS [Server Name],\n RTRIM([object_name]) AS [Object Name],\n cntr_value AS [Memory Grants Pending]\n FROM\n sys.dm_os_performance_counters WITH(NOLOCK)\n WHERE\n [object_name]\n LIKE\n N'%Memory Manager%' -- Handles named instances\n AND\n counter_name = N'Memory Grants Pending'\n \n ```\n\nWhat's next\n-----------\n\n- [Google Cloud recommenders](/recommender/docs/recommenders)"]]
