Panduan arsitektur manajemen memori

Artikel
05/30/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics Analytics 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 yang sangat dimuat, beberapa kueri besar yang memerlukan sejumlah besar memori untuk dijalankan tidak bisa mendapatkan jumlah minimum memori yang diminta, dan menerima kesalahan waktu habis saat menunggu sumber daya memori. Untuk mengatasinya, tingkatkan opsi tunggu kueri. Untuk kueri paralel, pertimbangkan untuk mengurangi tingkat maksimum Opsi paralelisme.

Dalam sistem yang sangat dimuat di bawah tekanan memori, kueri dengan gabungan gabungan, pengurutan, dan bitmap dalam rencana kueri dapat menghilangkan bitmap ketika kueri tidak mendapatkan memori minimum yang diperlukan untuk bitmap. Ini dapat memengaruhi performa kueri dan jika proses pengurutan tidak dapat pas dalam memori, ini dapat meningkatkan penggunaan worktable dalam tempdb database, menyebabkan tempdb pertumbuhan. 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 (SQL Server Standard Edition mendukung hingga 128 GB). Dengan arsitektur IA64, batasnya adalah 7 TB (IA64 tidak didukung di SQL Server 2012 (11.x) ke atas). Lihat Batas Memori untuk Windows untuk informasi selengkapnya.

Memori Address Windows Extensions (AWE)

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

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

Jika LPIM tidak diaktifkan, SQL Server akan beralih menggunakan memori konvensional dan dalam kasus kelelahan memori OS, kesalahan 17890 dapat 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 (11.x)

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

Alokator Halaman Tunggal (SPA), termasuk hanya alokasi memori yang kurang dari, atau sama dengan 8 KB dalam proses SQL Server. Opsi konfigurasi memori server maks (MB) dan memori server min (MB) menentukan batas memori fisik yang dikonsumsi SPA. Kumpulan Buffer secara bersamaan merupakan mekanisme untuk SPA, dan konsumen terbesar alokasi satu halaman.
Alokator Multi-Halaman (MPA), untuk alokasi memori yang meminta lebih dari 8 KB.
Clr Allocator, termasuk tumpukan 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 dibuat langsung ke Windows. Ini termasuk penggunaan timbunan Windows dan alokasi virtual langsung yang dibuat oleh modul yang dimuat ke dalam proses SQL Server. Contoh permintaan alokasi memori tersebut termasuk alokasi dari DLL prosedur tersimpan yang diperluas, objek yang dibuat dengan menggunakan prosedur Automation (sp_OA panggilan), dan alokasi dari penyedia server tertaut.

Dimulai dengan SQL Server 2012 (11.x), alokasi Halaman Tunggal, alokasi Multi-Halaman, dan alokasi CLR semuanya dikonsolidasikan ke dalam Alokator Halaman "Ukuran Apa Pun", dan disertakan dalam batas memori yang dikontrol oleh memori server maks (MB) dan opsi konfigurasi memori server min (MB ). Perubahan ini memberikan kemampuan ukuran yang lebih akurat untuk semua persyaratan memori yang melalui manajer memori SQL Server.

Penting

Tinjau dengan cermat konfigurasi memori server maks (MB) dan memori server min (MB) Anda saat ini setelah Anda meningkatkan ke SQL Server 2012 (11.x) dan yang lebih baru. Ini karena mulai SQL Server 2012 (11.x), konfigurasi tersebut sekarang menyertakan dan memperhitungkan lebih banyak alokasi memori dibandingkan dengan versi sebelumnya. Perubahan ini berlaku untuk versi 32-bit dan 64-bit SQL Server 2012 (11.x) dan SQL Server 2014 (12.x), dan versi 64-bit SQL Server 2016 (13.x) dan yang lebih baru.

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

Jenis alokasi memori	SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) dan SQL Server 2008 R2 (10.50.x)	Dimulai dengan SQL Server 2012 (11.x)
Alokasi satu halaman	Ya	Ya, dikonsolidasikan ke dalam alokasi halaman "ukuran apa pun"
Alokasi multi-halaman	Tidak	Ya, dikonsolidasikan ke dalam alokasi halaman "ukuran apa pun"
Alokasi CLR	Tidak	Ya
Memori tumpukan utas	Tidak	Tidak
Alokasi langsung dari Windows	Tidak	Tidak

Dimulai dengan SQL Server 2012 (11.x), SQL Server mungkin mengalokasikan lebih banyak memori daripada nilai yang ditentukan dalam pengaturan memori server maks (MB). Perilaku ini dapat terjadi ketika nilai Total Memori Server (KB) telah mencapai pengaturan Memori Server Target (KB), seperti yang ditentukan oleh memori server maks (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 secara singkat melebihi pengaturan memori server maks (MB ). Dalam situasi ini, pembacaan penghitung kinerja Total Server Memory (KB) akan melebihi pengaturan memori server maks (MB) dan Memori Server Target (KB).

Perilaku ini biasanya diamati selama operasi berikut:

Kueri indeks penyimpan kolom besar
Mode batch besar pada kueri rowstore
Indeks penyimpan kolom (re)build, yang menggunakan memori dalam volume besar untuk melakukan operasi Hash dan Sortir
Operasi pencadangan yang memerlukan buffer memori besar
Operasi pelacakan yang harus menyimpan parameter input besar

Perubahan pada memory_to_reserve dimulai dengan SQL Server 2012 (11.x)

Dalam versi SQL Server yang lebih lama, manajer memori SQL Server menyisihkan bagian dari ruang alamat virtual (VAS) proses untuk digunakan oleh Alokator Multi-Halaman (MPA), Alokator CLR, alokasi memori untuk tumpukan utas dalam proses SQL Server, dan alokasi Windows Langsung (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 alokator halaman "ukuran apa pun" juga menangani alokasi yang lebih besar dari 8 KB, nilai memory_to_reserve tidak menyertakan alokasi multi-halaman. Kecuali untuk perubahan ini, yang lainnya tetap sama dengan opsi konfigurasi ini.

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

Jenis alokasi memori	SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) dan SQL Server 2008 R2 (10.50.x)	Dimulai dengan SQL Server 2012 (11.x)
Alokasi satu halaman	Tidak	Tidak, dikonsolidasikan ke dalam alokasi halaman "ukuran apa pun"
Alokasi multi-halaman	Ya	Tidak, dikonsolidasikan ke dalam alokasi halaman "ukuran apa pun"
Alokasi CLR	Ya	Ya
Memori tumpukan utas	Ya	Ya
Alokasi langsung dari Windows	Ya	Ya

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 gratis ini mencegah sistem operasi (OS) dari penomor. Jika memori yang lebih sedikit gratis, SQL Server merilis 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.

Memori server maks mengontrol alokasi memori SQL Server, mengkompilasi memori, semua cache (termasuk kumpulan buffer), pemberian memori eksekusi kueri, memori manajer kunci, dan memori CLR¹ (pada dasarnya setiap petugas memori yang ditemukan di sys.dm_os_memory_clerks).

¹ memori CLR dikelola di bawah alokasi max_server_memory 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 ², file .dll prosedur yang diperluas, penyedia OLE DB yang dirujuk 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 memori server maks (MB).

¹ Lihat artikel tentang cara Mengonfigurasi Opsi Konfigurasi Server utas pekerja maks, untuk informasi tentang utas pekerja default terhitung untuk sejumlah CPU afinitas tertentu di host saat ini. Ukuran tumpukan SQL Server adalah sebagai berikut:

Arsitektur SQL Server	Arsitektur OS	Ukuran tumpukan
x86 (32-bit)	x86 (32-bit)	512 KB
x86 (32-bit)	x64 (64-bit)	768 KB
x64 (64-bit)	x64 (64-bit)	2048 KB
IA64 (Itanium)	IA64 (Itanium)	4096 KB

² memori CLR dikelola di bawah alokasi max_server_memory dimulai dengan SQL Server 2012 (11.x).

SQL Server menggunakan pemberitahuan memori API QueryMemoryResourceNotification untuk menentukan kapan manajer memori SQL Server dapat 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 komputasi ruang alamat virtual prosesnya untuk kumpulan buffer, tetapi memperoleh (penerapan) hanya jumlah memori fisik yang diperlukan untuk beban 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 memperoleh memori fisik sampai mencapai target alokasi memori server maks (MB) atau OS menunjukkan tidak ada lagi kelebihan memori bebas; ia membebaskan memori ketika memiliki lebih dari pengaturan memori server min, dan OS menunjukkan bahwa ada 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

Memori server min dan opsi konfigurasi memori server maks menetapkan batas atas dan bawah untuk jumlah memori yang digunakan oleh kumpulan buffer dan cache lain dari 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. Ketika beban kerja Mesin Database SQL Server meningkat, beban kerja terus memperoleh memori yang diperlukan untuk mendukung beban kerja. Kumpulan buffer tidak membebaskan memori yang diperoleh hingga mencapai jumlah yang ditentukan dalam memori server min. 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 memori server maks (MB).

Catatan

SQL Server sebagai proses memperoleh lebih banyak memori daripada yang ditentukan oleh opsi memori server maks (MB). Komponen internal dan eksternal dapat mengalokasikan memori di luar kumpulan buffer, yang mengonsumsi memori tambahan, tetapi memori yang dialokasikan untuk kumpulan buffer biasanya masih mewakili bagian terbesar memori yang dikonsumsi oleh SQL Server.

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 memori server min.

Jika nilai yang sama ditentukan untuk memori server min dan memori server maks (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 instans SQL Server berjalan di komputer di mana aplikasi lain sering dihentikan atau dimulai, alokasi dan dealokasi memori oleh instans SQL Server dapat memperlambat waktu mulai aplikasi lain. Selain itu, jika SQL Server adalah salah satu dari beberapa aplikasi server yang berjalan pada satu komputer, administrator sistem mungkin perlu mengontrol jumlah memori yang dialokasikan ke SQL Server. Dalam kasus ini, Anda dapat menggunakan memori server min dan opsi memori server maks (MB) untuk mengontrol berapa banyak memori yang dapat digunakan SQL Server. Memori server min dan opsi memori server maks 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 kumpulan hasil aktif (MARS) diaktifkan, koneksi pengguna sekitar (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.

Efek memori min per kueri

Opsi konfigurasi memori min per kueri 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.

Penting

Jangan mengatur memori min per opsi konfigurasi server kueri terlalu tinggi, terutama pada sistem yang sangat sibuk, karena melakukannya dapat menyebabkan:

Peningkatan persaingan untuk sumber daya memori.
Mengurangi konkurensi dengan meningkatkan jumlah memori untuk setiap kueri, bahkan jika memori yang diperlukan pada runtime lebih rendah daripada konfigurasi ini.

Untuk rekomendasi tentang menggunakan konfigurasi ini, lihat Mengonfigurasi memori min per kueri Opsi Konfigurasi Server.

Pertimbangan pemberian memori

Untuk eksekusi mode baris, pemberian memori awal tidak dapat dilampaui dalam kondisi apa pun. Jika lebih banyak memori daripada pemberian awal diperlukan untuk menjalankan operasi hash atau pengurutan , maka ini akan meluap ke disk. Operasi hash yang meluap didukung oleh Workfile di tempdb, sementara operasi pengurutan yang meluap didukung oleh Worktable.

Tumpahan yang terjadi selama operasi Sortir dikenal sebagai peringatan Urutkan. Mengurutkan peringatan menunjukkan bahwa operasi pengurutan tidak cocok dengan memori. Ini tidak termasuk operasi pengurutan yang melibatkan pembuatan indeks, hanya operasi pengurutan dalam kueri (seperti klausa yang ORDER BY digunakan dalam SELECT pernyataan).

Tumpahan yang terjadi selama operasi hash dikenal sebagai peringatan Hash. Ini terjadi ketika rekursi hash atau penghentian hashing (talangan hash) telah terjadi selama operasi 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.
Bailout hash terjadi ketika operasi hashing mencapai tingkat rekursi maksimumnya dan bergeser ke rencana alternatif untuk memproses data yang dipartisi yang tersisa. 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 pemberian memori dinamis ini dirancang untuk memungkinkan eksekusi hash atau operasi pengurutan memori-residen yang berjalan dalam mode batch. Jika operasi ini masih tidak sesuai dengan memori, maka operasi ini akan meluap 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.

Cara kerja manajemen buffer

Buffer adalah halaman 8 KB dalam memori, ukuran yang sama dengan data atau halaman indeks. Dengan demikian, cache buffer dibagi menjadi halaman 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 dan Menulis Halaman.

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 komputasi ruang alamat virtual prosesnya (disebut target memori) untuk cache buffer, tetapi hanya memperoleh (penerapan) jumlah memori fisik yang diperlukan untuk beban saat ini. Anda dapat mengkueri kolom committed_target_kb dan committed_kb dalam tampilan katalog sys.dm_os_sys_info untuk mengembalikan jumlah halaman yang dicadangkan sebagai target memori dan jumlah halaman yang saat ini diterapkan di cache buffer.

Interval antara startup SQL Server dan ketika cache buffer mendapatkan target memorinya disebut ramp-up. Selama waktu ini, permintaan baca 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. Ramp-up dipercepat dengan mengubah permintaan baca satu halaman menjadi delapan permintaan halaman yang selaras (menyusun satu tingkat). Ini memungkinkan ramp-up untuk menyelesaikan jauh lebih cepat, terutama pada mesin dengan banyak memori. Untuk informasi selengkapnya tentang halaman dan luasnya, lihat Panduan Arsitektur Halaman dan Tingkat.

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.
Manajer Log untuk pengelogan write-ahead.

Fitur yang didukung

Manajer buffer mendukung fitur-fitur berikut:

Manajer buffer sadar akses memori non-seragam (NUMA ). Halaman cache buffer didistribusikan di seluruh simpul NUMA perangkat keras, yang memungkinkan utas untuk mengakses halaman buffer yang dialokasikan pada simpul NUMA lokal daripada dari memori asing.
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.

Disk I/O

Manajer buffer hanya melakukan baca dan tulis ke database. Operasi file dan database lainnya seperti membuka, menutup, memperluas, dan menyusut dilakukan oleh manajer database dan komponen manajer file.

Operasi I/O disk oleh manajer buffer memiliki karakteristik berikut:

Semua I/Os dilakukan secara asinkron, yang memungkinkan utas panggilan untuk terus memproses saat operasi I/O berlangsung di latar belakang.
Semua I/Os dikeluarkan dalam utas panggilan kecuali opsi Afinitas I/O sedang digunakan. Opsi masker I/O afinitas mengikat I/O disk SQL Server ke subset CPU tertentu. Di lingkungan pemrosesan transaksional online (OLTP) SQL Server kelas atas, ekstensi ini dapat meningkatkan performa utas SQL Server yang mengeluarkan I/Os.
Beberapa I/Os halaman dicapai dengan I/O pengumpulan sebar, yang memungkinkan data ditransfer ke atau keluar dari area memori yang tidak bersebelahan. Ini berarti bahwa SQL Server dapat dengan cepat mengisi atau menghapus cache buffer sambil menghindari beberapa permintaan I/O fisik.

Permintaan I/O panjang

Manajer buffer melaporkan permintaan I/O apa pun yang telah beredar setidaknya selama 15 detik. Ini membantu administrator sistem membedakan antara masalah SQL Server dan masalah subsistem I/O. Pesan kesalahan 833 dilaporkan dan muncul di log kesalahan SQL Server sebagai berikut:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

I/O panjang mungkin berupa baca atau tulis; saat ini tidak ditunjukkan dalam pesan. Pesan I/O panjang adalah peringatan, bukan kesalahan. Mereka tidak menunjukkan masalah dengan SQL Server tetapi dengan sistem I/O yang mendasar. Pesan dilaporkan untuk membantu administrator sistem menemukan penyebab waktu respons SQL Server yang buruk lebih cepat, dan untuk membedakan masalah yang berada di luar kendali SQL Server. Dengan demikian, mereka tidak memerlukan tindakan apa pun, tetapi administrator sistem harus menyelidiki mengapa permintaan I/O membutuhkan waktu begitu lama, dan apakah waktunya dapat dibenarkan.

Penyebab permintaan I/O panjang

Pesan I/O panjang dapat menunjukkan bahwa I/O diblokir secara permanen dan tidak akan pernah selesai (dikenal sebagai I/O yang hilang), atau hanya belum selesai. Tidak dimungkinkan untuk mengetahui dari pesan skenario mana yang terjadi, meskipun I/O yang hilang sering menyebabkan batas waktu kait.

I/Os panjang sering menunjukkan beban kerja SQL Server yang terlalu intens untuk subsistem disk. Subsistem disk yang tidak memadai dapat ditunjukkan ketika:

Beberapa pesan I/O panjang muncul di log kesalahan selama beban kerja SQL Server yang berat.
Penghitung Monitor Performa menunjukkan latensi disk panjang, antrean disk panjang, atau tidak ada waktu menganggur disk.

I/Os panjang juga dapat disebabkan oleh komponen di jalur I/O (misalnya, driver, pengontrol, atau firmware) yang terus menunda layanan permintaan I/O lama untuk melayani permintaan yang lebih baru yang lebih dekat dengan posisi kepala disk saat ini. Teknik umum pemrosesan permintaan dalam prioritas berdasarkan mana yang paling dekat dengan posisi kepala baca/tulis saat ini dikenal sebagai "pencarian lift." Ini mungkin sulit untuk diperkuat dengan alat Monitor Performa karena sebagian besar I/Os segera dilayankan. Permintaan I/O panjang dapat diperburuk oleh beban kerja yang melakukan I/O berurutan dalam jumlah besar, seperti pencadangan dan pemulihan, pemindaian tabel, pengurutan, pembuatan indeks, beban massal, dan nol file.

I/Os panjang terisolasi yang tidak muncul terkait dengan salah satu kondisi sebelumnya mungkin disebabkan oleh masalah perangkat keras atau driver. Log peristiwa sistem mungkin berisi peristiwa terkait yang membantu mendiagnosis masalah.

Deteksi tekanan memori

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

Ekstra I/Os (seperti utas latar belakang penulis malas yang sangat aktif)
Rasio kompilasi ulang yang lebih tinggi
Kueri yang berjalan lebih lama (jika penantian pemberian memori ada)
Siklus CPU ekstra

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

Memori fisik (RAM) yang tersedia rendah. Ini menyebabkan sistem memangkas set kerja dari proses yang sedang berjalan, yang dapat mengakibatkan perlambatan keseluruhan. SQL Server dapat mengurangi target penerapan kumpulan buffer dan mulai memangkas cache internal lebih sering.
Memori sistem yang tersedia secara keseluruhan (yang mencakup file halaman sistem) rendah. Hal ini dapat menyebabkan sistem gagal mengalokasikan alokasi memori, karena tidak dapat mem-page out memori yang saat ini dialokasikan.

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 memori server maks.
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. Pemberitahuan ini adalah pesan internal dari masing-masing komponen mesin, dan disimpan dalam buffer cincin.

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 memiliki informasi status tergantung pada kondisi saat ini dari RESOURCE_MEMPHYSICAL_HIGH, , RESOURCE_MEMPHYSICAL_LOWRESOURCE_MEMPHYSICAL_STEADY, atau RESOURCE_MEMVIRTUAL_LOW.
Buffer cincin Memory Broker, yang berisi catatan pemberitahuan memori untuk setiap kumpulan 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 lagi: perlindungan halaman robek 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 ke halaman tepat sebelum menulisnya ke disk, dan diverifikasi setelah dibaca dari disk.

SQL Server akan mencoba kembali setiap bacaan yang gagal dengan checksum, halaman robek, atau kesalahan I/O lainnya empat kali. Jika baca berhasil dalam salah satu upaya coba lagi, pesan akan ditulis ke log kesalahan dan perintah yang memicu bacaan akan berlanjut. Jika upaya coba lagi gagal, perintah akan gagal dengan pesan kesalahan 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 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 mengkueri page_verify_optionkolom dalam tampilan katalog sys.databases atau IsTornPageDetectionEnabled properti fungsi DATABASEPROPERTYEX.

Catatan

Jika pengaturan perlindungan halaman diubah, pengaturan baru tidak segera memengaruhi seluruh database. Sebagai gantinya, halaman mengadopsi tingkat perlindungan database saat ini setiap kali ditulis berikutnya. Ini berarti bahwa database dapat terdiri dari halaman dengan berbagai jenis perlindungan.

Perlindungan 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 hanya dapat meninggalkan bagian dari halaman yang ditulis ke disk. Ketika perlindungan halaman robek digunakan, pola tanda tangan 2-bit tertentu untuk setiap sektor 512-byte di halaman database 8 kilobyte (KB) dan disimpan di header halaman database 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 dengan setiap tulisan, jadi selalu dimungkinkan untuk memberi tahu kapan hanya sebagian sektor yang membuatnya ke disk: jika sedikit berada dalam keadaan salah ketika halaman dibaca nanti, halaman ditulis dengan salah dan halaman robek 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 MENGUBAH Opsi SET DATABASE (Transact-SQL).

Perlindungan checksum

Perlindungan checksum, yang diperkenalkan di SQL Server 2005 (9.x), memberikan pemeriksaan integritas data yang lebih kuat. Checksum dihitung untuk data di 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. Perlindungan checksum dapat menangkap lebih banyak kesalahan daripada perlindungan halaman yang robek karena dipengaruhi oleh setiap byte halaman, namun, itu cukup intensif sumber daya.

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 Mengubah Opsi SET DATABASE (Transact-SQL).

Penting

Ketika database pengguna atau sistem ditingkatkan ke SQL Server 2005 (9.x) atau yang lebih baru, nilai PAGE_VERIFY (NONE atau TORN_PAGE_DETECTION) dipertahankan. Kami sangat menyarankan Anda menggunakan CHECKSUM. TORN_PAGE_DETECTION dapat menggunakan lebih sedikit sumber daya, tetapi memberikan subset CHECKSUM perlindungan minimal.

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 telah dirancang untuk memanfaatkan komputer berbasis NUMA tanpa memerlukan perubahan aplikasi apa pun. Untuk informasi selengkapnya, lihat Cara: Mengonfigurasi SQL Server untuk Menggunakan Soft-NUMA.

Partisi dinamis objek memori

Alokator timbunan, yang disebut objek memori di SQL Server, memungkinkan Mesin Database untuk mengalokasikan memori dari timbunan. 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 benar, objek CMemThread mengandalkan konstruksi sinkronisasi (mutex) untuk memastikan hanya satu utas yang memperbarui informasi penting pada satu waktu.

Catatan

Jenis objek CMemThread digunakan di seluruh basis kode Mesin Database untuk banyak alokasi yang berbeda, dan dapat dipartisi secara global, oleh simpul atau oleh CPU.

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 objek CMemThread. 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 partisi tunggal sudah cukup, tetapi dalam beberapa skenario ini dapat menyebabkan ketidakcocokan, yang hanya dapat dicegah dengan objek memori yang sangat dipartisi. Tidak diinginkan untuk mempartisi setiap objek memori karena lebih banyak partisi dapat mengakibatkan inefisiensi lain dan meningkatkan fragmentasi memori.

Catatan

Sebelum SQL Server 2016 (13.x), bendera pelacakan 8048 dapat digunakan untuk memaksa PMO berbasis simpul menjadi PMO berbasis CPU. Dimulai dengan SQL Server 2014 (12.x) SP2 dan SQL Server 2016 (13.x), perilaku ini dinamis dan dikendalikan oleh mesin.

Dimulai dengan SQL Server 2014 (12.x) SP2 dan SQL Server 2016 (13.x), Mesin Database dapat secara dinamis mendeteksi ketidakcocokan pada objek CMemThread tertentu dan mempromosikan objek ke per-simpul atau implementasi berbasis per CPU. Setelah dipromosikan, PMO tetap dipromosikan hingga proses SQL Server dimulai ulang. Ketidakcocokan CMemThread dapat dideteksi dengan adanya CMEMTHREAD tinggi menunggu di DMV sys.dm_os_wait_stats, dan dengan mengamati kolom contention_factorDMV sys.dm_os_memory_objects , , partition_typeexclusive_allocations_count, dan waiting_tasks_count.

Panduan arsitektur manajemen memori

Windows Virtual Memory Manager

Arsitektur memori SQL Server

Memori konvensional (virtual)

Memori Address Windows Extensions (AWE)

Perubahan pada manajemen memori yang dimulai dengan SQL Server 2012 (11.x)

Perubahan pada memory_to_reserve dimulai dengan SQL Server 2012 (11.x)

Manajemen memori dinamis

Ukuran tumpukan

Efek memori server min dan maks

Memori yang digunakan oleh spesifikasi objek SQL Server

Efek memori min per kueri

Pertimbangan pemberian memori

Manajemen buffer

Cara kerja manajemen buffer

Fitur yang didukung

Disk I/O

Permintaan I/O panjang

Penyebab permintaan I/O panjang

Deteksi tekanan memori

Deteksi kesalahan

Perlindungan halaman robek

Perlindungan checksum

Memahami akses memori non-seragam

Partisi dinamis objek memori

Langkah berikutnya

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: