Opsi konfigurasi memori server

Berlaku untuk: SQL Server

Pemanfaatan memori untuk Mesin Database SQL Server dibatasi oleh sepasang pengaturan konfigurasi, memori server min (MB) dan memori server maks (MB). Seiring waktu dan dalam keadaan normal, SQL Server akan mencoba mengklaim memori hingga batas yang ditetapkan oleh memori server maks (MB).

Catatan

Indeks penyimpan kolom: Ringkasan dan gambaran umum OLTP Dalam Memori dan objek skenario penggunaan memiliki petugas memori mereka sendiri, yang memudahkan untuk memantau penggunaan kumpulan buffer mereka. Untuk informasi selengkapnya, lihat sys.dm_os_memory_clerks.

Dalam versi SQL Server yang lebih lama, pemanfaatan memori hampir tidak terkendali, menunjukkan kepada SQL Server bahwa semua memori sistem tersedia untuk digunakan. Disarankan di semua versi SQL Server untuk mengonfigurasi batas atas untuk pemanfaatan memori SQL Server dengan mengonfigurasi memori server maks (MB).

• Karena SQL Server 2019 (15.x), Penyiapan SQL di server Windows memberikan rekomendasi untuk memori server maks (MB) untuk instans SQL Server mandiri berdasarkan persentase memori sistem yang tersedia pada saat penginstalan.
• Kapan saja Anda dapat mengonfigurasi ulang batas memori (dalam megabyte) untuk proses SQL Server yang digunakan oleh instans SQL Server melalui opsi konfigurasi memori server min (MB) dan memori server maks (MB ).

Catatan

Panduan ini mengacu pada instans SQL Server di Windows. Untuk informasi tentang konfigurasi memori di Linux, lihat Praktik terbaik performa dan panduan konfigurasi untuk SQL Server di Linux dan pengaturan memory.memorylimitmb.

Rekomendasi

Pengaturan default dan nilai minimum yang diizinkan untuk opsi ini adalah:

Opsi	Default	Minimum yang diperbolehkan	Direkomendasikan
memori server min (MB)	0	0	0
memori server maks (MB)	2.147.483.647 megabyte (MB)	128 MB	75% memori sistem yang tersedia tidak digunakan oleh proses lain, termasuk instans lain. Untuk rekomendasi yang lebih rinci, lihat memori server maks.

Dalam batas-batas ini, SQL Server dapat mengubah persyaratan memorinya secara dinamis berdasarkan sumber daya sistem yang tersedia. Untuk informasi selengkapnya, lihat manajemen memori dinamis.

• Mengatur nilai memori server maks (MB) terlalu tinggi dapat menyebabkan satu instans SQL Server bersaing untuk memori dengan instans SQL Server lain yang dihosting pada host yang sama.
• Namun, mengatur memori server maks (MB) terlalu rendah adalah peluang performa yang hilang, dan dapat menyebabkan tekanan memori dan masalah performa dalam instans SQL Server.
• Mengatur memori server maks (MB) ke nilai minimum bahkan dapat mencegah SQL Server dimulai. Jika Anda tidak dapat memulai SQL Server setelah mengubah opsi ini, mulai menggunakan -f opsi startup dan reset memori server maks (MB) ke nilai sebelumnya. Untuk informasi selengkapnya, lihat Opsi startup Database Engine Service.
• Tidak disarankan untuk mengatur memori server maks (MB) dan memori server min (MB) menjadi nilai yang sama, atau mendekati nilai yang sama.

Catatan

Opsi memori server maks hanya membatasi ukuran kumpulan buffer SQL Server. Opsi memori server maks tidak membatasi area memori yang belum disimpan yang tersisa yang dibiarkan SQL Server untuk alokasi komponen lain seperti prosedur tersimpan yang diperluas, objek COM, DLL yang tidak dibagikan, dan EXE.

SQL Server dapat menggunakan memori secara dinamis. Namun, Anda dapat mengatur opsi memori secara manual dan membatasi jumlah memori yang dapat diakses SQL Server. Sebelum Anda mengatur jumlah memori untuk SQL Server, tentukan pengaturan memori yang sesuai dengan mengurangi, dari total memori fisik, memori yang diperlukan untuk sistem operasi (OS), alokasi memori yang tidak dikontrol oleh pengaturan memori server maks (MB), dan instans SQL Server lainnya (dan penggunaan sistem lainnya, jika server adalah rumah bagi aplikasi lain yang mengonsumsi memori, termasuk instans SQL Server lainnya). Perbedaan ini adalah jumlah maksimum memori yang dapat Anda tetapkan ke instans SQL Server saat ini.

Memori dapat dikonfigurasi hingga batas ruang alamat virtual proses di semua edisi SQL Server. Untuk informasi selengkapnya, lihat Batas Memori untuk Rilis Windows dan Windows Server.

Memori server min

Gunakan memori server min (MB) untuk menjamin jumlah minimum memori yang tersedia untuk SQL Server Memory Manager.

• SQL Server tidak akan segera mengalokasikan jumlah memori yang ditentukan dalam memori server min (MB) saat startup. Namun, setelah penggunaan memori mencapai nilai ini karena beban klien, SQL Server tidak dapat membebaskan memori kecuali nilai memori server min (MB) berkurang. Misalnya, ketika beberapa instans SQL Server diinstal secara bersamaan di server yang sama, pertimbangkan untuk mengatur parameter memori server min (MB) untuk memesan memori untuk instans.

• Mengatur nilai memori server min (MB) sangat penting dalam lingkungan virtual untuk memastikan tekanan memori dari host yang mendasar tidak mencoba membatalkan alokasi memori dari kumpulan buffer pada komputer virtual tamu (VM) di luar apa yang diperlukan untuk performa yang dapat diterima. Idealnya, instans SQL Server di komputer virtual tidak perlu bersaing dengan proses alokasi memori proaktif host virtual.

• SQL Server tidak dijamin untuk mengalokasikan jumlah memori yang ditentukan dalam memori server min (MB). Jika beban pada server tidak pernah memerlukan alokasi jumlah memori yang ditentukan dalam memori server min (MB), SQL Server akan menggunakan lebih sedikit memori.

Memori server maks

Gunakan memori server maks (MB) untuk menjamin OS dan aplikasi lain tidak mengalami tekanan memori yang merugikan yang berasal dari SQL Server.

• Sebelum Anda mengatur konfigurasi memori server maks (MB), pantau konsumsi memori keseluruhan server yang menghosting instans SQL Server, selama operasi normal, untuk menentukan ketersediaan dan persyaratan memori. Untuk konfigurasi awal atau ketika tidak ada kesempatan untuk mengumpulkan penggunaan memori proses SQL Server dari waktu ke waktu, gunakan pendekatan praktik terbaik umum berikut untuk mengonfigurasi memori server maks (MB) untuk satu instans:
  • Dari total memori OS, kurangi setara dengan potensi alokasi memori utas SQL Server di luar kontrol memori server maks (MB), yang merupakan ukurantumpukan 1 dikalikan dengan utas^{pekerja maks terhitung 2}.
  • Kemudian kurangi 25% untuk alokasi memori lain di luar kontrol memori server maks (MB), seperti buffer cadangan, DLL prosedur tersimpan yang diperluas, objek yang dibuat dengan menggunakan prosedur Automation (sp_OA panggilan), dan alokasi dari penyedia server tertaut. Ini adalah perkiraan generik, dan jarak tempuh Anda mungkin bervariasi.
  • Apa yang tersisa harus menjadi pengaturan memori server maks (MB) untuk satu penyiapan instans.

¹ Lihat panduan Arsitektur Manajemen Memori untuk informasi tentang ukuran tumpukan utas per arsitektur.

² Lihat halaman dokumentasi tentang cara Mengonfigurasi utas pekerja maks (opsi konfigurasi server), untuk informasi tentang utas pekerja default terhitung untuk sejumlah CPU afinitisasi tertentu di host saat ini.

Mengatur opsi secara manual

Opsi server min memori server (MB) dan memori server maks (MB) dapat diatur untuk menjangkau rentang nilai memori. Metode ini berguna bagi administrator sistem atau database untuk mengonfigurasi instans SQL Server dengan persyaratan memori aplikasi lain, atau instans SQL Server lain yang berjalan pada host yang sama.

Menggunakan Transact-SQL

Opsi memori server min (MB) dan memori server maks (MB) adalah opsi lanjutan. Saat menggunakan prosedur tersimpan sp_configure sistem untuk mengubah pengaturan ini, Anda hanya dapat mengubahnya saat memperlihatkan opsi tingkat lanjut diatur ke 1. Pengaturan ini berlaku segera tanpa menghidupkan ulang server. Untuk informasi selengkapnya, lihat sp_configure.

Contoh berikut mengatur opsi memori server maks (MB) ke 12.288 MB atau 12 GB. Meskipun sp_configure menentukan nama opsi sebagai max server memory (MB), Anda dapat menghilangkan (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

Kueri berikut mengembalikan informasi tentang nilai yang saat ini dikonfigurasi, dan nilai yang saat ini digunakan. Kueri ini mengembalikan hasil terlepas dari apakah sp_configure opsi 'perlihatkan opsi tingkat lanjut' diaktifkan.

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

Menggunakan SQL Server Management Studio

Gunakan memori server min (MB) dan memori server maks (MB) untuk mengonfigurasi ulang jumlah memori (dalam megabyte) yang dikelola oleh SQL Server Memory Manager untuk instans SQL Server.

1. Di Object Explorer, klik kanan server dan pilih Properti.

2. Pilih halaman Memori dari jendela Properti Server. Nilai memori server minimum saat ini dan Memori server maksimum ditampilkan.

3. Di Opsi memori server, masukkan angka yang diinginkan untuk Memori server minimum dan Memori server maksimum. Untuk rekomendasi, lihat memori server min (MB) dan memori server maks (MB) di artikel ini.

Cuplikan layar berikut menunjukkan ketiga langkah:

Mengunci halaman dalam memori (LPIM)

Aplikasi berbasis Windows dapat menggunakan API Address Windowing Extensions (AWE) Windows untuk mengalokasikan dan memetakan memori fisik ke dalam ruang alamat proses. Kebijakan LPIM Windows menentukan akun mana yang dapat mengakses API untuk menyimpan data dalam memori fisik, mencegah sistem menomori data ke memori virtual pada disk. Memori yang dialokasikan menggunakan AWE dikunci sampai aplikasi secara eksplisit membebaskannya atau keluar. Menggunakan API AWE untuk manajemen memori di SQL Server 64-bit juga sering disebut sebagai halaman terkunci. Mengunci halaman dalam memori mungkin menjaga server tetap responsif ketika memori halaman ke disk terjadi. Opsi Kunci halaman dalam memori diaktifkan dalam instans edisi Standar SQL Server dan lebih tinggi ketika akun dengan hak istimewa untuk dijalankan sqlservr.exe telah diberikan halaman Kunci Windows di memori (LPIM) hak pengguna.

Untuk menonaktifkan opsi Kunci halaman dalam memori untuk SQL Server, hapus halaman Kunci di pengguna memori langsung untuk akun dengan hak istimewa untuk menjalankan sqlservr.exe (akun startup SQL Server) akun startup.

Menggunakan LPIM tidak memengaruhi manajemen memori dinamis SQL Server, memungkinkannya untuk memperluas atau menyusut atas permintaan petugas memori lainnya. Saat menggunakan halaman Kunci di kanan pengguna memori , sangat disarankan untuk mengatur batas atas untuk memori server maks (MB). Untuk informasi selengkapnya, lihat memori server maks (MB).

LPIM harus digunakan ketika ada tanda-tanda bahwa sqlservr proses sedang di-page out. Dalam hal ini, kesalahan 17890 akan dilaporkan dalam Errorlog, menyerupai contoh di bawah ini:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Menggunakan LPIM dengan pengaturan memori server maks (MB) yang salah dikonfigurasi yang tidak memperhitungkan konsumen memori lain dalam sistem dapat menyebabkan ketidakstabilan, tergantung pada jumlah memori yang diperlukan oleh proses lain, atau persyaratan memori SQL Server di luar cakupan memori server maks (MB). Untuk informasi selengkapnya, lihat memori server maks. Jika hak istimewa Kunci halaman dalam memori (LPIM) diberikan (pada sistem 32-bit atau 64-bit), kami sangat menyarankan Anda mengatur memori server maks (MB) ke nilai tertentu, daripada meninggalkan default 2.147.483.647 megabyte (MB).

Catatan

Dimulai dengan SQL Server 2012 (11.x), Bendera Jejak 845 tidak diperlukan untuk Edisi Standar untuk menggunakan halaman terkunci.

Aktifkan Halaman kunci dalam memori

Setelah mempertimbangkan informasi sebelumnya, untuk mengaktifkan opsi Kunci halaman dalam memori dengan memberikan hak istimewa ke akun layanan untuk instans SQL Server, lihat Mengaktifkan halaman Kunci di Opsi memori (Windows).

Untuk menentukan akun layanan untuk instans SQL Server, lihat Pengelola Konfigurasi SQL Server atau kueri service_account dari sys.dm_server_services. Untuk informasi selengkapnya, lihat sys.dm_server_services.

Menampilkan halaman Kunci dalam status memori

Untuk menentukan apakah Halaman kunci dalam hak istimewa memori diberikan ke akun layanan untuk instans SQL Server, gunakan kueri berikut. Kueri ini didukung di SQL Server 2016 (13.x) SP1 dan yang lebih baru.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Nilai sql_memory_model_desc berikut menunjukkan status LPIM:

• CONVENTIONAL. Halaman kunci dalam hak istimewa memori tidak diberikan.
• LOCK_PAGES. Halaman kunci dalam hak istimewa memori diberikan.
• LARGE_PAGES. Halaman kunci dalam hak istimewa memori diberikan dalam mode Enterprise dengan Bendera Pelacakan 834 diaktifkan. Ini adalah konfigurasi tingkat lanjut dan tidak direkomendasikan untuk sebagian besar lingkungan. Untuk informasi selengkapnya dan peringatan penting, lihat Bendera Pelacakan 834.

Gunakan metode berikut untuk menentukan apakah instans SQL Server menggunakan halaman terkunci:

• Output kueri Transact-SQL berikut menunjukkan nilai bukan nol untuk locked_page_allocations_kb:

  SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
  FROM sys.dm_os_memory_nodes omn
  INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id)
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• Log kesalahan SQL Server saat ini melaporkan pesan, Using locked pages in the memory manager selama startup server.

• Bagian Manajer Memori dari output DBCC MEMORYSTATUS menunjukkan nilai bukan nol untuk item.AWE Allocated

Beberapa instans SQL Server

Saat Anda menjalankan beberapa instans Mesin Database, ada berbagai pendekatan yang dapat Anda gunakan untuk mengelola memori:

• Gunakan memori server maks (MB) di setiap instans untuk mengontrol penggunaan memori, seperti yang dirinci sebelumnya. Tetapkan pengaturan maksimum untuk setiap instans, berhati-hatilah bahwa total jatah tidak lebih dari total memori fisik pada komputer Anda. Anda mungkin ingin memberikan setiap memori instans sebanding dengan beban kerja atau ukuran database yang diharapkan. Pendekatan ini memiliki keuntungan bahwa ketika proses atau instans baru dimulai, memori gratis akan segera tersedia untuk mereka. Kelemahannya adalah bahwa jika Anda tidak menjalankan semua instans, tidak ada instans yang berjalan yang akan dapat menggunakan memori bebas yang tersisa.

• Gunakan memori server min (MB) di setiap instans untuk mengontrol penggunaan memori, seperti yang dirinci sebelumnya. Tetapkan pengaturan minimum untuk setiap instans, sehingga jumlah minimum ini adalah 1 - 2 GB kurang dari total memori fisik pada komputer Anda. Sekali lagi, Anda dapat menetapkan minimum ini secara proporsional dengan beban instans tersebut yang diharapkan. Pendekatan ini memiliki keuntungan bahwa jika tidak semua instans berjalan pada saat yang sama, yang berjalan dapat menggunakan memori kosong yang tersisa. Pendekatan ini juga berguna ketika ada proses intensif memori lain di komputer, karena akan memastikan bahwa SQL Server setidaknya akan mendapatkan jumlah memori yang wajar. Kelemahannya adalah ketika instans baru (atau proses lain) dimulai, mungkin perlu beberapa waktu bagi instans yang sedang berjalan untuk merilis memori, terutama jika mereka harus menulis halaman yang dimodifikasi kembali ke database mereka untuk melakukannya.

• Gunakan memori server maks (MB) dan memori server min (MB) di setiap instans untuk mengontrol penggunaan memori, mengamati dan menyetel pemanfaatan maksimum setiap instans dan perlindungan memori minimum dalam berbagai tingkat pemanfaatan memori potensial.

• Jangan lakukan apa-apa (tidak disarankan). Instans pertama yang disajikan dengan beban kerja cenderung mengalokasikan semua memori. Instans diam, atau instans dimulai nanti, mungkin berakhir dengan hanya menjalankan jumlah memori minimal yang tersedia. SQL Server tidak mencoba menyeimbangkan penggunaan memori di seluruh instans. Namun, semua instans akan merespons sinyal Pemberitahuan Memori Windows untuk menyesuaikan ukuran jejak memorinya. Windows tidak menyeimbangkan memori di seluruh aplikasi dengan API Pemberitahuan Memori. Ini hanya memberikan umpan balik global tentang ketersediaan memori pada sistem.

Anda dapat mengubah pengaturan ini tanpa memulai ulang instans, sehingga Anda dapat dengan mudah bereksperimen untuk menemukan pengaturan terbaik untuk pola penggunaan Anda.

Contoh

J. Atur opsi memori server maks ke 4 GB

Contoh berikut mengatur opsi memori server maks (MB) ke 4096 MB atau 4 GB. Meskipun sp_configure menentukan nama opsi sebagai max server memory (MB), Anda dapat menghilangkan (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Ini akan menghasilkan pernyataan yang mirip Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. dengan Batas memori baru berlaku segera setelah eksekusi RECONFIGURE. Untuk informasi selengkapnya, lihat sp_configure.

B. Menentukan alokasi memori saat ini

Kueri berikut menampilkan informasi alokasi memori saat ini.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

C. Menampilkan nilai max server memory (MB)

Kueri berikut mengembalikan informasi tentang nilai yang saat ini dikonfigurasi dan nilai yang digunakan. Kueri ini mengembalikan hasil terlepas dari apakah sp_configure opsi 'perlihatkan opsi tingkat lanjut' diaktifkan.

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

Konten terkait

• Panduan arsitektur manajemen memori
• Monitor dan Selaraskan Kinerja
• KONFIGURASI ULANG (Transact-SQL)
• Opsi konfigurasi server (SQL Server)
• sp_configure (T-SQL)
• Opsi startup Database Engine Service
• Batas Memori untuk Rilis Windows dan Windows Server