Panduan arsitektur manajemen memori

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Windows Virtual Memory Manager

Wilayah ruang alamat yang berkomitmen dipetakan ke memori fisik yang tersedia oleh Windows Virtual Memory Manager (VMM).

Untuk informasi selengkapnya tentang jumlah memori fisik yang didukung oleh sistem operasi yang berbeda, lihat dokumentasi Windows tentang Batas Memori untuk Rilis Windows.

Sistem memori virtual memungkinkan komitmen memori fisik yang berlebihan, sehingga rasio memori virtual-ke-fisik dapat melebihi 1:1. Akibatnya, program yang lebih besar dapat berjalan di komputer dengan berbagai konfigurasi memori fisik. Namun, menggunakan memori virtual yang jauh lebih banyak daripada kumpulan kerja rata-rata gabungan dari semua proses dapat menyebabkan performa yang buruk.

Arsitektur memori SQL Server

SQL Server secara dinamis memperoleh dan membebaskan memori sesuai kebutuhan. Biasanya, administrator tidak perlu menentukan berapa banyak memori yang harus dialokasikan ke SQL Server, meskipun opsi masih ada dan diperlukan di beberapa lingkungan.

Salah satu tujuan desain utama dari semua perangkat lunak database adalah untuk meminimalkan I/O disk karena pembacaan dan penulisan disk adalah salah satu operasi yang paling intensif sumber daya. SQL Server membangun kumpulan buffer dalam memori untuk menahan halaman yang dibaca dari database. Sebagian besar kode di SQL Server didedikasikan untuk meminimalkan jumlah baca dan tulis fisik antara disk dan kumpulan buffer. SQL Server mencoba mencapai keseimbangan antara dua tujuan:

• Jaga agar kumpulan buffer tidak menjadi sangat besar sehingga seluruh sistem kehabisan memori.
• Minimalkan I/O fisik ke file database dengan memaksimalkan ukuran kumpulan buffer.

Dalam sistem dengan beban berat, beberapa kueri besar yang memerlukan banyak memori untuk dijalankan tidak dapat memperoleh jumlah minimum memori yang diminta dan mengalami galat batas waktu saat menunggu ketersediaan sumber daya memori. Untuk mengatasinya, tingkatkan opsi query wait. Untuk kueri paralel, pertimbangkan untuk mengurangi tingkat maksimum nilai paralelisme .

Dalam sistem dengan beban tinggi yang mengalami tekanan memori, kueri dengan merge join, pengurutan, dan bitmap dalam rencana kueri dapat membuang bitmap saat kueri tidak memperoleh alokasi memori minimum yang diperlukan untuk bitmap. Ini dapat memengaruhi kinerja kueri, dan jika proses pengurutan tidak dapat dimuat dalam memori, hal ini dapat meningkatkan penggunaan tabel kerja dalam database tempdb, sehingga menyebabkan tempdb bertambah besar. Untuk mengatasi masalah ini, tambahkan memori fisik, atau sesuaikan kueri untuk menggunakan rencana kueri yang berbeda dan lebih cepat.

Memori konvensional (virtual)

Semua edisi SQL Server mendukung memori konvensional pada platform 64-bit. Proses SQL Server dapat mengakses ruang alamat virtual hingga maksimum sistem operasi pada arsitektur x64.

Dengan arsitektur IA64, batasnya adalah 7 TB (IA64 tidak didukung di SQL Server 2012 (11.x) dan versi yang lebih baru).

Untuk informasi selengkapnya, lihat Batas Memori untuk Windows.

memori Address Windowing Extensions (AWE)

Dengan menggunakan Address Windowing Extensions (AWE) dan hak istimewa Mengunci halaman dalam memori (LPIM) yang diperlukan oleh AWE, Anda dapat mempertahankan sebagian besar memori proses SQL Server tetap terkunci dalam RAM fisik saat memori virtual rendah. Ini terjadi dalam alokasi AWE 32-bit dan 64-bit. Penguncian memori terjadi karena memori AWE tidak melewati Virtual Memory Manager di Windows, yang mengendalikan paging memori. API alokasi memori AWE memerlukan hak istimewa Mengunci halaman dalam memori (SeLockMemoryPrivilege); lihat catatan AllocateUserPhysicalPages. Oleh karena itu, manfaat utama menggunakan API AWE adalah menjaga sebagian besar memori tetap berada di RAM jika ada tekanan memori pada sistem. Untuk informasi tentang cara mengizinkan SQL Server menggunakan AWE, lihat Mengaktifkan halaman Kunci dalam opsi memori (Windows).

Jika LPIM diberikan, kami sangat menyarankan Anda mengatur max server memory (MB) ke nilai tertentu, daripada meninggalkan default 2.147.483.647 megabyte (MB). Untuk informasi selengkapnya, lihat Opsi konfigurasi memori server: Mengatur opsi secara manual dan Mengunci halaman dalam memori (LPIM).

Jika LPIM tidak diaktifkan, SQL Server beralih menggunakan memori konvensional dan dalam kasus kelelahan memori OS, dan kesalahan MSSQLSERVER_17890 mungkin dilaporkan dalam log kesalahan. Kesalahan menyerupai contoh berikut:

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: ##%.

Perubahan pada manajemen memori yang dimulai dengan SQL Server 2012

Dalam versi SQL Server yang lebih lama, alokasi memori dilakukan menggunakan lima mekanisme yang berbeda:

• Alokator Halaman Tunggal (SPA), yang hanya mencakup alokasi memori yang kurang dari atau sama dengan 8 KB di dalam proses SQL Server. Opsi konfigurasi max server memory (MB) dan min server memory (MB) menentukan batas penggunaan memori fisik yang digunakan SPA. Buffer Pool sekaligus menjadi mekanisme bagi SPA dan pengguna terbesar alokasi halaman tunggal.

• Alokator Multi-Halaman (MPA), untuk permintaan alokasi memori yang melebihi 8 KB.

• CLR Allocator, termasuk heap SQL CLR dan alokasi globalnya yang dibuat selama inisialisasi CLR.

• Alokasi memori untuk tumpukan utas dalam proses SQL Server.

• Alokasi Windows langsung (DWA), untuk permintaan alokasi memori yang diajukan langsung ke Windows. Ini termasuk penggunaan heap Windows dan alokasi virtual langsung yang dilakukan oleh modul yang dimuat ke dalam proses SQL Server. Contoh permintaan alokasi memori tersebut mencakup alokasi dari DLL prosedur tersimpan diperluas, objek yang dibuat dengan menggunakan prosedur Otomatisasi (panggilan sp_OA), dan alokasi dari penyedia server tertaut.

Mulai dengan SQL Server 2012 (11.x), alokasi halaman tunggal, alokasi multihalaman, dan alokasi CLR semuanya dikonsolidasikan ke dalam alokator halaman "ukuran apa pun", dan disertakan dalam batas memori yang dikendalikan oleh opsi konfigurasi max server memory (MB) dan min server memory (MB). Perubahan ini memberikan kemampuan ukuran yang lebih akurat untuk semua persyaratan memori yang melalui manajer memori SQL Server.

Tabel berikut menunjukkan apakah jenis alokasi memori tertentu dikontrol oleh max server memory (MB) opsi konfigurasi dan min server memory (MB) :

SQL Server mungkin menggunakan memori melebihi pengaturan memori server maksimum

Dimulai dengan SQL Server 2012 (11.x), SQL Server mungkin mengalokasikan lebih banyak memori daripada nilai yang ditentukan dalam max server memory (MB) pengaturan. Perilaku ini dapat terjadi ketika nilai Total Memori Server (KB) telah mencapai pengaturan Memori Server Target (KB), seperti yang ditentukan oleh max server memory (MB). Jika ada memori bebas yang tidak mencukupi untuk memenuhi permintaan memori multi-halaman (lebih dari 8 KB) karena fragmentasi memori, SQL Server dapat melakukan komitmen berlebihan alih-alih menolak permintaan memori.

Segera setelah alokasi ini dilakukan, tugas latar belakang Resource Monitor mulai memberi sinyal kepada semua konsumen memori untuk merilis memori yang dialokasikan, dan mencoba membawa nilai Total Server Memory (KB) di bawah spesifikasi Memori Server Target (KB). Oleh karena itu, penggunaan memori SQL Server dapat untuk sementara melebihi pengaturan max server memory (MB). Dalam situasi ini, nilai penghitung kinerja Total Server Memory (KB) melebihi max server memory (MB) pengaturan Memori Server Target (KB).

Perilaku ini biasanya diamati selama operasi berikut:

• Kueri indeks penyimpan kolom besar
• Kueri besar batch mode pada rowstore
• Pembangunan ulang indeks columnstore, yang memerlukan memori dalam jumlah besar untuk melakukan operasi hash dan pengurutan
• Operasi pencadangan yang memerlukan buffer memori besar
• Operasi pelacakan yang harus menyimpan parameter input besar
• Permintaan pemberian memori besar

Jika Anda sering mengamati perilaku ini, pertimbangkan untuk menggunakan bendera pelacakan 8121 di SQL Server 2019 (15.x) untuk memungkinkan Resource Monitor membersihkan dengan lebih cepat. Dimulai dengan SQL Server 2022 (16.x), fungsionalitas ini diaktifkan secara default, dan bendera pelacakan tidak berpengaruh.

Perubahan pada memory_to_reserve dimulai dengan SQL Server 2012

Dalam versi SQL Server yang lebih lama, manajer memori SQL Server mencadangkan sebagian ruang alamat virtual (VAS) proses untuk digunakan oleh Multi-Page Allocator (MPA), CLR Allocator, alokasi memori untuk stack thread dalam proses SQL Server, dan alokasi langsung Windows (DWA). Bagian ruang alamat virtual ini juga dikenal sebagai wilayah "Mem-To-Leave" atau "non-Buffer Pool".

Ruang alamat virtual yang dicadangkan untuk alokasi ini ditentukan oleh opsi konfigurasi memory_to_reserve. Nilai default yang digunakan SQL Server adalah 256 MB.

Karena pengalokasi halaman "ukuran berapa pun" juga menangani alokasi yang lebih besar dari 8 KB, nilai memory_to_reserve tidak mencakup alokasi beberapa halaman. Kecuali untuk perubahan ini, yang lainnya tetap sama dengan opsi konfigurasi ini.

Tabel berikut menunjukkan apakah jenis alokasi memori tertentu termasuk dalam memory_to_reserve wilayah ruang alamat virtual untuk proses SQL Server:

Manajemen memori dinamis

Perilaku manajemen memori default dari Mesin Database SQL Server adalah memperoleh memori sebanyak yang dibutuhkan tanpa membuat kekurangan memori pada sistem. Mesin Database SQL Server melakukan ini dengan menggunakan API Pemberitahuan Memori di Microsoft Windows.

Ketika SQL Server menggunakan memori secara dinamis, SQL Server meminta sistem secara berkala untuk menentukan jumlah memori kosong. Mempertahankan memori bebas ini mencegah sistem operasi (OS) melakukan paging. Jika memori yang tersedia lebih sedikit, SQL Server melepaskan memori ke OS. Jika lebih banyak memori gratis, SQL Server dapat mengalokasikan lebih banyak memori. SQL Server menambahkan memori hanya ketika beban kerjanya membutuhkan lebih banyak memori; server tidak aktif tidak meningkatkan ukuran ruang alamat virtualnya. Jika Anda melihat bahwa Task Manager dan Monitor Performa menunjukkan penurunan memori yang tersedia secara stabil saat SQL Server menggunakan manajemen memori dinamis, ini adalah perilaku default dan tidak boleh dianggap sebagai kebocoran memori.

Opsi konfigurasi memori server mengontrol alokasi memori SQL Server, memori kompilasi, semua cache (termasuk buffer pool), alokasi memori untuk eksekusi kueri, memori pengelola kunci, dan memori CLR¹ (pada dasarnya, memory clerk apa pun yang ditemukan di sys.dm_os_memory_clerks).

¹ memori CLR dikelola di bawah max server memory (MB) alokasi yang dimulai dengan SQL Server 2012 (11.x).

Kueri berikut mengembalikan informasi tentang memori yang saat ini dialokasikan:

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;

Ukuran tumpukan

Memori untuk tumpukan utas ¹, CLR ², prosedur yang diperluas .dll file, penyedia OLE DB yang direferensikan oleh kueri terdistribusi, objek otomatisasi yang direferensikan dalam pernyataan Transact-SQL, dan memori apa pun yang dialokasikan oleh DLL non SQL Server, tidak dikendalikan oleh max server memory (MB).

¹ Rujuk ke Konfigurasi server: jumlah maksimum utas pekerja untuk informasi mengenai jumlah default utas pekerja yang dihitung untuk jumlah CPU yang diafinitaskan tertentu pada host saat ini. Ukuran tumpukan SQL Server adalah sebagai berikut:

² memori CLR dikelola di bawah max server memory (MB) alokasi yang dimulai dengan SQL Server 2012 (11.x).

SQL Server menggunakan API QueryMemoryResourceNotification pemberitahuan memori untuk menentukan kapan manajer memori SQL Server mungkin mengalokasikan memori dan memori rilis.

Ketika SQL Server dimulai, SQL Server menghitung ukuran ruang alamat virtual untuk kumpulan buffer berdasarkan beberapa parameter seperti jumlah memori fisik pada sistem, jumlah utas server dan berbagai parameter startup. SQL Server mencadangkan jumlah yang dihitung dari ruang alamat virtual proses untuk buffer pool, tetapi hanya mengalokasikan (meng-commit) jumlah memori fisik yang diperlukan untuk beban kerja saat ini.

Instans kemudian terus memperoleh memori sesuai kebutuhan untuk mendukung beban kerja. Karena lebih banyak pengguna menyambungkan dan menjalankan kueri, SQL Server memperoleh lebih banyak memori fisik sesuai permintaan. Instans SQL Server terus mengakuisisi memori fisik hingga mencapai target alokasinya max server memory (MB) atau hingga OS menunjukkan bahwa tidak lagi terdapat kelebihan memori bebas; instans tersebut melepaskan memori saat jumlahnya melebihi pengaturan memori server minimum, dan OS menunjukkan bahwa terjadi kekurangan memori bebas.

Karena aplikasi lain dimulai di komputer yang menjalankan instans SQL Server, aplikasi tersebut menggunakan memori dan jumlah memori fisik gratis turun di bawah target SQL Server. Instans SQL Server menyesuaikan konsumsi memorinya. Jika aplikasi lain dihentikan dan lebih banyak memori tersedia, instans SQL Server meningkatkan ukuran alokasi memorinya. SQL Server dapat membebaskan dan memperoleh beberapa megabyte memori setiap detik, memungkinkannya untuk dengan cepat menyesuaikan diri dengan perubahan alokasi memori.

Efek memori server min dan maks

Opsi konfigurasi min server memory dan max server memory menetapkan batas bawah dan batas atas jumlah memori yang digunakan oleh pool buffer dan cache lain pada Mesin Database. Kumpulan buffer tidak segera memperoleh jumlah memori yang ditentukan dalam memori server min. Kumpulan buffer dimulai hanya dengan memori yang diperlukan untuk menginisialisasi. Seiring meningkatnya beban kerja Mesin Basis Data SQL Server, mesin tersebut terus memperoleh memori yang diperlukan untuk mendukung beban kerja itu. Pool buffer tidak membebaskan memori apa pun yang telah dialokasikan hingga mencapai jumlah yang ditentukan dalam min server memory. Setelah memori server min tercapai, kumpulan buffer kemudian menggunakan algoritma standar untuk memperoleh dan membebaskan memori sesuai kebutuhan. Satu-satunya perbedaan adalah bahwa kumpulan buffer tidak pernah menghilangkan alokasi memorinya di bawah tingkat yang ditentukan dalam memori server min, dan tidak pernah memperoleh lebih banyak memori daripada tingkat yang ditentukan dalam max server memory (MB).

Jumlah memori yang diperoleh oleh Mesin Database SQL Server sepenuhnya tergantung pada beban kerja yang ditempatkan pada instans. Instans SQL Server yang tidak memproses banyak permintaan mungkin tidak pernah mencapai nilai yang ditentukan oleh min server memory (MB).

Jika nilai yang sama ditentukan untuk memori server min dan max server memory (MB), maka setelah memori yang dialokasikan ke Mesin Database SQL Server mencapai nilai tersebut, Mesin Database SQL Server berhenti membebaskan dan memperoleh memori secara dinamis untuk kumpulan buffer.

Jika sebuah instans SQL Server berjalan pada komputer tempat aplikasi lain sering dihentikan atau dijalankan, alokasi dan dealokasi memori oleh instans SQL Server dapat memperlambat waktu startup aplikasi lain. Selain itu, jika SQL Server adalah salah satu dari beberapa aplikasi server yang berjalan pada satu komputer, administrator sistem harus mengontrol jumlah memori yang dialokasikan ke SQL Server. Dalam kasus ini, Anda dapat menggunakan opsi min server memory dan max server memory (MB) untuk mengontrol berapa banyak memori yang dapat digunakan oleh SQL Server. Opsi min server memory (MB) dan max server memory (MB) ditentukan dalam megabyte. Untuk informasi selengkapnya termasuk rekomendasi tentang cara mengatur konfigurasi memori ini, lihat Opsi konfigurasi memori server.

Memori yang digunakan oleh spesifikasi objek SQL Server

Daftar berikut menjelaskan perkiraan jumlah memori yang digunakan oleh objek yang berbeda di SQL Server. Jumlah yang tercantum adalah perkiraan dan dapat bervariasi tergantung pada lingkungan dan bagaimana objek dibuat:

• Kunci (seperti yang dikelola oleh Manajer Kunci): 64 byte + 32 byte per pemilik
• Koneksi pengguna: Sekitar (3 * network_packet_size + 94 KB)

Ukuran paket jaringan adalah ukuran paket aliran data tabular (TDS) yang digunakan untuk berkomunikasi antara aplikasi dan Mesin Database. Ukuran paket default adalah 4 KB, dan dikontrol oleh opsi konfigurasi ukuran paket jaringan.

Ketika beberapa set hasil aktif (MARS) diaktifkan, koneksi pengguna memerlukan sekitar (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.

Dampak memori minimum per kueri

Opsi min memory per query konfigurasi menetapkan jumlah memori minimum (dalam kilobyte) yang akan dialokasikan untuk eksekusi kueri. Ini juga dikenal sebagai pemberian memori minimum. Semua kueri harus menunggu hingga memori minimum yang diminta dapat diamankan, sebelum eksekusi dapat dimulai, atau sampai nilai yang ditentukan dalam opsi konfigurasi server tunggu kueri terlampaui. Jenis tunggu yang terakumulasi dalam skenario ini adalah RESOURCE_SEMAPHORE.

Pertimbangan alokasi memori

Untuk eksekusi mode baris, alokasi memori awal tidak boleh dilampaui dalam kondisi apa pun. Jika diperlukan memori yang lebih besar daripada alokasi awal untuk menjalankan operasi hash atau sort, maka operasi tersebut akan ditulis ke disk. Operasi hash yang meluap didukung oleh Workfile di tempdb, sementara operasi pengurutan yang meluap didukung oleh Worktable.

Luapan data yang terjadi selama operasi pengurutan dikenal sebagai Sort Warnings Event Class. Peringatan pengurutan menunjukkan bahwa operasi pengurutan tidak muat dalam memori. Ini tidak termasuk operasi pengurutan yang melibatkan pembuatan indeks, hanya operasi pengurutan dalam kueri (seperti klausa yang ORDER BY digunakan dalam SELECT pernyataan).

Pelimpahan yang terjadi saat operasi hash dikenal sebagai Kelas Kejadian Peringatan Hash. Hal ini terjadi ketika rekursi hash atau penghentian proses hashing (hash bailout) terjadi selama proses hashing.

• Rekursi hash terjadi ketika input build tidak cocok dengan memori yang tersedia, menghasilkan pemisahan input menjadi beberapa partisi yang diproses secara terpisah. Jika salah satu partisi ini masih tidak cocok dengan memori yang tersedia, partisi tersebut dibagi menjadi subpartisi, yang juga diproses secara terpisah. Proses pemisahan ini berlanjut sampai setiap partisi sesuai dengan memori yang tersedia atau sampai tingkat rekursi maksimum tercapai.
• Hash bailout terjadi ketika operasi hashing mencapai tingkat rekursi maksimum dan beralih ke metode alternatif untuk memproses sisa data yang telah dipartisi. Peristiwa ini dapat menyebabkan penurunan performa di server Anda.

Untuk eksekusi mode batch, pemberian memori awal dapat meningkat secara dinamis hingga ambang batas internal tertentu secara default. Mekanisme alokasi memori dinamis ini dirancang untuk memungkinkan eksekusi dalam memori atas operasi hash atau sort yang berjalan dalam mode batch. Jika operasi ini masih tidak muat di memori, operasi tersebut akan dialihkan ke disk.

Untuk informasi selengkapnya tentang mode eksekusi, lihat Panduan Arsitektur Pemrosesan Kueri.

Manajemen buffer

Tujuan utama database SQL Server adalah untuk menyimpan dan mengambil data, sehingga I/O disk intensif adalah karakteristik inti dari Mesin Database. Dan karena operasi I/O disk dapat menggunakan banyak sumber daya dan membutuhkan waktu yang relatif lama untuk diselesaikan, SQL Server berfokus pada membuat I/O sangat efisien. Manajemen buffer adalah komponen utama dalam mencapai efisiensi ini. Komponen manajemen buffer terdiri dari dua mekanisme: manajer buffer untuk mengakses dan memperbarui halaman database, dan cache buffer (juga disebut kumpulan buffer), untuk mengurangi I/O file database.

Untuk penjelasan terperinci tentang I/O disk di SQL Server, lihat Dasar-dasar I/O SQL Server.

Cara kerja manajemen buffer

Buffer adalah halaman 8 KB dalam memori, ukuran yang sama dengan data atau halaman indeks. Dengan demikian, buffer cache dibagi menjadi halaman berukuran 8 KB. Manajer buffer mengelola fungsi untuk membaca data atau halaman indeks dari file disk database ke dalam cache buffer, dan menulis halaman yang dimodifikasi kembali ke disk. Halaman tetap berada di cache buffer hingga manajer buffer memerlukan area buffer untuk membaca lebih banyak data. Data ditulis kembali ke disk hanya jika dimodifikasi. Data dalam cache buffer dapat dimodifikasi beberapa kali sebelum ditulis kembali ke disk. Untuk informasi selengkapnya, lihat Membaca halaman data di halaman Mesin Database dan Tulis di Mesin Database.

Ketika SQL Server dimulai, SQL Server menghitung ukuran ruang alamat virtual untuk cache buffer berdasarkan beberapa parameter seperti jumlah memori fisik pada sistem, jumlah utas server maksimum yang dikonfigurasi, dan berbagai parameter startup. SQL Server mencadangkan jumlah yang dihitung dari ruang alamat virtual prosesnya ini (disebut target memori) untuk cache buffer, tetapi hanya mengalokasikan jumlah memori fisik yang diperlukan untuk beban saat ini. Anda dapat membuat kueri pada kolom committed_target_kb dan committed_kb di tampilan katalog sys.dm_os_sys_info untuk mengembalikan masing-masing jumlah halaman yang dicadangkan sebagai target memori dan jumlah halaman yang saat ini dialokasikan di buffer cache.

Interval antara startup SQL Server dan ketika cache buffer mendapatkan target memorinya disebut ramp-up. Selama waktu ini, permintaan pembacaan mengisi buffer sesuai kebutuhan. Misalnya, satu permintaan baca halaman 8 KB mengisi satu halaman buffer. Ini berarti ramp-up tergantung pada jumlah dan jenis permintaan klien. Proses ramp-up dipercepat dengan mengubah permintaan baca satu halaman menjadi permintaan delapan halaman yang selaras (yang membentuk satu extent). Ini memungkinkan proses ramp-up selesai jauh lebih cepat, terutama pada mesin dengan banyak memori. Untuk informasi selengkapnya tentang halaman dan ekstensi, lihat Panduan arsitektur halaman dan ekstensi.

Karena manajer buffer menggunakan sebagian besar memori dalam proses SQL Server, ia bekerja sama dengan manajer memori untuk memungkinkan komponen lain menggunakan buffer-nya. Manajer buffer berinteraksi terutama dengan komponen berikut:

• Resource Manager untuk mengontrol penggunaan memori secara keseluruhan dan, dalam platform 32-bit, untuk mengontrol penggunaan ruang alamat.
• Manajer Database dan Sistem Operasi SQL Server (SQLOS) untuk operasi I/O file tingkat rendah.
• Pengelola Log untuk pencatatan write-ahead.

Fitur yang didukung

Manajer buffer mendukung fitur-fitur berikut:

• Manajer buffer mendukung akses memori non-seragam (NUMA). Halaman cache penyangga didistribusikan ke seluruh simpul NUMA perangkat keras, yang memungkinkan utas mengakses halaman penyangga yang dialokasikan pada simpul NUMA lokal alih-alih dari memori nonlokal.

• Manajer buffer mendukung Hot Add Memory, yang memungkinkan pengguna untuk menambahkan memori fisik tanpa memulai ulang server.

• Manajer buffer mendukung halaman besar pada platform 64-bit. Ukuran halaman khusus untuk versi Windows.

  Catatan

  Sebelum SQL Server 2012 (11.x), mengaktifkan halaman besar di SQL Server memerlukan bendera pelacakan 834.

• Manajer buffer menyediakan diagnostik tambahan yang diekspos melalui tampilan manajemen dinamis. Anda dapat menggunakan tampilan ini untuk memantau berbagai sumber daya sistem operasi yang khusus untuk SQL Server. Misalnya, Anda dapat menggunakan tampilan sys.dm_os_buffer_descriptors untuk memantau halaman di cache buffer.

Deteksi tekanan memori

Tekanan memori adalah kondisi yang dihasilkan dari kekurangan memori, dan dapat mengakibatkan:

• I/O tambahan (seperti thread latar belakang lazy writer yang sangat aktif)
• Rasio kompilasi ulang yang lebih tinggi
• Kueri yang berjalan lebih lama (jika terdapat penantian alokasi memori)
• Siklus CPU tambahan

Situasi ini dapat dipicu oleh penyebab eksternal atau internal. Penyebab eksternal meliputi:

• Memori fisik (RAM) yang tersedia rendah. Hal ini menyebabkan sistem memangkas set kerja dari proses yang sedang berjalan, yang dapat mengakibatkan perlambatan keseluruhan. SQL Server dapat mengurangi target commit dari pool buffer dan mulai lebih sering memangkas cache internal.
• Memori sistem yang tersedia secara keseluruhan (yang mencakup file halaman sistem) rendah. Hal ini dapat menyebabkan sistem gagal mengalokasikan memori, karena tidak dapat memindahkan memori yang telah dialokasikan ke disk.

Penyebab internal meliputi:

• Merespons tekanan memori eksternal, ketika Mesin Database SQL Server menetapkan batas penggunaan memori yang lebih rendah.
• Pengaturan memori diturunkan secara manual dengan mengurangi konfigurasi max server memory.
• Perubahan distribusi memori komponen internal antara beberapa cache.

Mesin Database SQL Server mengimplementasikan kerangka kerja yang didedikasikan untuk mendeteksi dan menangani tekanan memori, sebagai bagian dari manajemen memori dinamisnya. Kerangka kerja ini mencakup tugas latar belakang yang disebut Resource Monitor. Tugas Monitor Sumber Daya memantau status indikator memori eksternal dan internal. Setelah salah satu indikator ini berubah status, ia menghitung pemberitahuan yang sesuai dan menyiarkannya. Notifikasi ini adalah pesan internal dari masing-masing komponen mesin dan disimpan dalam buffer melingkar.

Dua buffer cincin menyimpan informasi yang relevan dengan manajemen memori dinamis:

• Buffer cincin Resource Monitor, yang melacak aktivitas Resource Monitor seperti tekanan memori yang disinyalir atau tidak. Buffer cincin ini berisi informasi status yang bergantung pada kondisi terkini RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, atau RESOURCE_MEMVIRTUAL_LOW.

• Buffer sirkular Memory Broker, yang berisi rekaman notifikasi memori untuk setiap pool sumber daya Resource Governor. Saat tekanan memori internal terdeteksi, pemberitahuan memori rendah diaktifkan untuk komponen yang mengalokasikan memori, untuk memicu tindakan yang dimaksudkan untuk menyeimbangkan memori antar cache.

Broker memori memantau konsumsi memori permintaan oleh setiap komponen dan kemudian berdasarkan informasi yang dikumpulkan, ia menghitung dan nilai memori yang optimal untuk masing-masing komponen ini. Ada sekumpulan broker untuk setiap kumpulan sumber daya Resource Governor. Informasi ini kemudian disiarkan ke setiap komponen, yang tumbuh atau menyusutkan penggunaannya sesuai kebutuhan.

Untuk informasi selengkapnya tentang broker memori, lihat sys.dm_os_memory_brokers.

Deteksi kesalahan

Halaman database dapat menggunakan salah satu dari dua mekanisme opsional yang membantu memastikan integritas halaman, sejak ditulis ke disk hingga dibaca kembali: perlindungan halaman sobek dan perlindungan checksum. Mekanisme ini memungkinkan metode independen untuk memverifikasi kebenaran tidak hanya penyimpanan data, tetapi komponen perangkat keras seperti pengontrol, driver, kabel, dan bahkan sistem operasi. Perlindungan ditambahkan pada halaman tepat sebelum halaman tersebut ditulis ke disk, dan diverifikasi setelah halaman tersebut dibaca dari disk.

SQL Server akan mencoba membaca kembali setiap operasi baca yang gagal karena kesalahan checksum, halaman rusak, atau kesalahan I/O lainnya hingga empat kali. Jika pembacaan berhasil dalam salah satu percobaan ulang, sebuah pesan dicatat dalam log kesalahan dan perintah yang memicu pembacaan tersebut akan dilanjutkan. Jika upaya coba lagi gagal, perintah gagal dengan kesalahan MSSQLSERVER_824 .

Jenis perlindungan halaman yang digunakan adalah atribut database yang berisi halaman. Perlindungan checksum adalah perlindungan default untuk database yang dibuat di SQL Server 2005 (9.x) dan versi yang lebih baru. Mekanisme perlindungan halaman ditentukan pada waktu pembuatan database, dan dapat diubah dengan menggunakan ALTER DATABASE SET. Anda dapat menentukan pengaturan perlindungan halaman saat ini dengan membuat kueri terhadap kolom page_verify_option dalam tampilan katalog sys.databases atau properti IsTornPageDetectionEnabled dari fungsi DATABASEPROPERTYEX.

Perlindungan terhadap halaman robek

Perlindungan halaman robek, yang diperkenalkan di SQL Server 2000 (8.x), terutama merupakan cara mendeteksi kerusakan halaman karena kegagalan daya. Misalnya, kegagalan daya yang tidak terduga mungkin hanya meninggalkan bagian dari halaman yang ditulis ke disk. Ketika perlindungan terhadap halaman robek digunakan, pola tanda tangan 2-bit tertentu ditetapkan untuk setiap sektor 512 byte pada halaman basis data 8 kilobyte (KB) dan disimpan dalam header halaman basis data saat halaman ditulis ke disk.

Ketika halaman dibaca dari disk, bit robek yang disimpan di header halaman dibandingkan dengan informasi sektor halaman aktual. Pola tanda tangan bergantian antara biner 01 dan 10 pada setiap penulisan, sehingga selalu dimungkinkan untuk mengetahui kapan hanya sebagian sektor yang berhasil ditulis ke disk: jika sebuah bit berada dalam keadaan yang salah saat halaman kemudian dibaca, halaman tersebut telah ditulis secara tidak benar dan torn page terdeteksi. Deteksi halaman robek menggunakan sumber daya minimal; namun, ini tidak mendeteksi semua kesalahan yang disebabkan oleh kegagalan perangkat keras disk. Untuk informasi tentang pengaturan deteksi halaman yang robek, lihat ALTER DATABASE SET Opsi.

Perlindungan checksum

Perlindungan checksum, yang diperkenalkan di SQL Server 2005 (9.x), memberikan pemeriksaan integritas data yang lebih kuat. Sebuah checksum dihitung untuk data pada setiap halaman yang ditulis, dan disimpan di header halaman. Setiap kali halaman dengan checksum tersimpan dibaca dari disk, mesin database menghitung ulang checksum untuk data di halaman dan menimbulkan kesalahan 824 jika checksum baru berbeda dari checksum yang disimpan. Proteksi checksum dapat mendeteksi lebih banyak kesalahan daripada proteksi halaman rusak karena mencakup setiap byte pada halaman tersebut, namun mekanisme ini memerlukan sumber daya dalam tingkat sedang.

Ketika checksum diaktifkan, kesalahan yang disebabkan oleh kegagalan daya dan perangkat keras atau firmware yang cacat dapat dideteksi kapan saja manajer buffer membaca halaman dari disk. Untuk informasi tentang pengaturan checksum, lihat ALTER DATABASE SET Opsi.

Memahami akses memori non-seragam

SQL Server sadar akses memori non-seragam (NUMA), dan berkinerja baik pada perangkat keras NUMA tanpa konfigurasi khusus. Ketika kecepatan jam dan jumlah prosesor meningkat, menjadi semakin sulit untuk mengurangi latensi memori yang diperlukan untuk menggunakan daya pemrosesan tambahan ini. Untuk menghindari hal ini, vendor perangkat keras menyediakan cache L3 besar, tetapi ini hanya solusi terbatas. Arsitektur NUMA menyediakan solusi yang dapat diskalakan untuk masalah ini.

SQL Server dirancang untuk memanfaatkan komputer berbasis NUMA tanpa memerlukan perubahan aplikasi apa pun. Untuk informasi selengkapnya, lihat Soft-NUMA (SQL Server).

Partisi dinamis pada objek memori

Alokator heap, yang dikenal sebagai objek memori di SQL Server, memungkinkan Mesin Basis Data mengalokasikan memori dari heap. Ini dapat dilacak menggunakan DMV sys.dm_os_memory_objects .

CMemThread adalah jenis objek memori aman utas yang memungkinkan alokasi memori bersamaan dari beberapa utas. Untuk pelacakan yang akurat, objek CMemThread mengandalkan mekanisme sinkronisasi (mutex) untuk memastikan bahwa hanya satu utas yang memperbarui bagian-bagian informasi penting pada satu waktu.

Namun, penggunaan mutex dapat menyebabkan pertikaian jika banyak utas mengalokasikan dari objek memori yang sama dengan cara yang sangat bersamaan. Oleh karena itu, SQL Server memiliki konsep objek memori yang dipartisi (PMO) dan setiap partisi diwakili oleh satu CMemThread objek. Pemartisian objek memori ditentukan secara statis dan tidak dapat diubah setelah pembuatan. Karena pola alokasi memori sangat bervariasi berdasarkan aspek-aspek seperti penggunaan perangkat keras dan memori, tidak mungkin untuk muncul dengan pola pemartisian yang sempurna di muka.

Dalam kebanyakan kasus, menggunakan satu partisi saja sudah memadai, tetapi dalam beberapa skenario, hal ini dapat menyebabkan perebutan sumber daya, yang hanya dapat dicegah dengan objek memori yang dipartisi dalam banyak bagian. Tidak diinginkan untuk mempartisi setiap objek memori karena lebih banyak partisi dapat mengakibatkan inefisiensi lain dan meningkatkan fragmentasi memori.

Mulai dari SQL Server 2014 (12.x) SP2 dan SQL Server 2016 (13.x), Mesin Database dapat secara dinamis mendeteksi kontensi pada objek CMemThread tertentu dan meningkatkan objek tersebut ke implementasi per-node atau berbasis per-CPU. Setelah dipromosikan, PMO tetap dipromosikan hingga proses SQL Server dimulai ulang. CMemThread kontensi dapat dideteksi dengan adanya waktu tunggu CMEMTHREAD yang tinggi dalam DMV sys.dm_os_wait_stats, dan dengan mengamati kolom contention_factor, partition_type, exclusive_allocations_count, dan waiting_tasks_count pada DMV sys.dm_os_memory_objects.

Konten terkait

• Dasar-dasar I/O SQL Server
• Opsi konfigurasi memori server
• Membaca halaman data di Mesin Database
• Menulis halaman di Mesin Database
• Soft-NUMA (SQL Server)
• Persyaratan untuk menggunakan tabel yang dioptimalkan memori
• Memecahkan masalah kehabisan memori atau memori rendah di SQL Server
• Mengatasi masalah Kehabisan Memori