Panduan Arsitektur Manajemen Memori

Artikel
07/20/2022
27 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Windows Virtual Memory Manager

Wilayah ruang alamat yang diterapkan 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 pada 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 bacaan fisik dan penulisan antara disk dan kumpulan buffer. SQL Server mencoba mencapai keseimbangan antara dua tujuan:

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

Catatan

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.

Catatan

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, hal ini dapat meningkatkan penggunaan worktable dalam database tempdb, menyebabkan tempdb tumbuh. 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 Ekstensi Windows Alamat (AWE)

Dengan menggunakan Address Windowing Extensions (AWE) dan Locked Pages in Memory privilege yang diperlukan oleh AWE, Anda dapat menyimpan sebagian besar memori proses SQL Server "terkunci": itu terus tetap dalam RAM fisik jika kondisi memori virtual 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 halaman memori. API alokasi memori AWE memerlukan hak istimewa Halaman Terkunci dalam Memori (SeLockMemoryPrivilege) ; lihat Catatan AllocateUserPhysicalPages. Oleh karena itu, manfaat utama menggunakan API AWE adalah menjaga sebagian besar penghuni memori dalam RAM jika terjadi tekanan memori pada sistem. Untuk informasi tentang cara mengizinkan SQL Server menggunakan AWE, lihat Mengaktifkan Halaman Kunci di Opsi Memori.

Jika hak istimewa lock-pages-in-memory (LPIM) diberikan (pada sistem 32-bit atau 64-bit), kami sangat menyarankan Anda mengatur memori server maks ke nilai tertentu, daripada membiarkan default 2.147.483.647 megabyte (MB). Untuk informasi selengkapnya, lihat Konfigurasi Server Memori Server: Mengatur opsi secara manual dan Halaman Terkunci dalam Memori (LPIM).

Jika halaman Terkunci dalam hak istimewa memori tidak diaktifkan, SQL Sever 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 sebelumnya ( SQL Server 2005 (9.x), SQL Server 2008 dan SQL Server 2008 R2), 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 adalah mekanisme untuk SPA, dan konsumen terbesar dari alokasi satu halaman.
Multi-Page Allocator (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 (panggilan sp_OA), dan alokasi dari penyedia server yang ditautkan.

Dimulai dengan SQL Server 2012 (11.x), Single-Page alokasi, alokasi Multi-Halaman, dan alokasi CLR semuanya dikonsolidasikan ke dalam Alokator Halaman "Ukuran apa pun", dan termasuk dalam batas memori yang dikendalikan 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) melalui SQL Server 2019 (15.x). Ini karena mulai dari 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) melalui SQL Server 2019 (15.x).

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

Jenis alokasi memori	SQL Server 2005 (9.x), SQL Server 2008 dan SQL Server 2008 R2	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. Perilaku ini dapat terjadi ketika nilai Total Memori Server (KB) telah mencapai pengaturan Memori Server Target (KB) ( seperti yang ditentukan oleh memori server maks). Jika 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. Dalam situasi ini, pembacaan penghitung kinerja Total Server Memory (KB) akan melebihi memori server maksimum dan pengaturan 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 sebelumnya ( SQL Server 2005 (9.x), SQL Server 2008 dan SQL Server 2008 R2), manajer memori SQL Server menyisihkan bagian dari ruang alamat virtual proses (VAS) 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 disediakan untuk alokasi ini ditentukan oleh opsi konfigurasi memory_to_reserve . Nilai default yang digunakan SQL Server adalah 256 MB. Untuk mengambil alih nilai default, gunakan parameter startup SQL Server -g . Lihat halaman dokumentasi tentang Opsi Startup Layanan Mesin Database untuk informasi tentang parameter startup -g .

Karena dimulai dengan SQL Server 2012 (11.x), alokator halaman "ukuran apa pun" baru 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 dan SQL Server 2008 R2	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 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 menambah ukuran ruang alamat virtualnya.

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 pegawai 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;

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

¹ Lihat halaman dokumentasi 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 API pemberitahuan memori QueryMemoryResourceNotification untuk menentukan kapan SQL Server Memory Manager dapat mengalokasikan memori dan memori rilis.

Ketika SQL Server dimulai, SQL Server menghitung ukuran ruang alamat virtual untuk kumpulan buffer berdasarkan sejumlah 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 atau OS menunjukkan tidak ada lagi kelebihan memori kosong; ini membebaskan memori ketika memiliki lebih dari pengaturan memori server min, dan OS menunjukkan bahwa ada kekurangan memori kosong.

Karena aplikasi lain dimulai di komputer yang menjalankan instans SQL Server, aplikasi tersebut mengonsumsi 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 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, ia terus memperoleh memori yang diperlukan untuk mendukung beban kerja. Kumpulan buffer tidak membebaskan memori yang diperoleh sampai 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.

Catatan

SQL Server sebagai proses memperoleh lebih banyak memori daripada yang ditentukan oleh opsi memori server maks. 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 memori terbesar yang dikonsumsi oleh SQL Server.

Jumlah memori yang diperoleh oleh SQL Server Database Engine 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, 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 tempat aplikasi lain sering dihentikan atau dimulai, alokasi dan pembatalan alokasi 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 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 tataan 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 minimum memori (dalam kilobyte) yang akan dialokasikan untuk eksekusi kueri. Ini juga dikenal sebagai peruntukan 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 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 saat runtime lebih rendah dari konfigurasi ini.

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

Pertimbangan pemberian memori

Untuk eksekusi mode baris, peruntukan 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 Pengurutan. Mengurutkan peringatan menunjukkan bahwa operasi pengurutan tidak sesuai 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 hash (hash bailout) 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 cocok dengan memori yang tersedia atau sampai tingkat rekursi maksimum tercapai.
Bailout hash terjadi ketika operasi hashing mencapai tingkat rekursi maksimumnya dan beralih ke rencana alternatif untuk memproses data yang dipartisi yang tersisa. Peristiwa ini dapat menyebabkan penurunan performa di server Anda.

Untuk eksekusi mode batch, peruntukan memori awal dapat secara dinamis meningkat hingga ambang batas internal tertentu secara default. Mekanisme peruntukan memori dinamis ini dirancang untuk memungkinkan eksekusi hash atau operasi pengurutan residen memori 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 mengonsumsi 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 buffer cache berdasarkan sejumlah 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 memperoleh (penerapan) hanya 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 peningkatan 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 peningkatan untuk menyelesaikan jauh lebih cepat, terutama pada mesin dengan banyak memori. Untuk informasi selengkapnya tentang halaman dan jangkauan, lihat Panduan Arsitektur Halaman dan Jangkauan.

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 node 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 buffer cache.

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 komponen manajer database dan manajer file.

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

Semua I/Os dilakukan secara asinkron, yang memungkinkan utas panggilan untuk melanjutkan pemrosesan saat operasi I/O berlangsung di latar belakang.
Semua I/Os dikeluarkan dalam utas panggilan kecuali opsi I/O afinitas 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.
I/Os beberapa halaman dicapai dengan I/O pengumpulan sebar, yang memungkinkan data ditransfer ke atau ke luar area memori yang tidak berdekatan. Ini berarti bahwa SQL Server dapat dengan cepat mengisi atau membersihkan cache buffer sambil menghindari beberapa permintaan I/O fisik.

Permintaan I/O panjang

Manajer buffer melaporkan permintaan I/O apa pun yang telah terutang 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 memakan 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 mungkin untuk mengetahui dari pesan skenario mana yang terjadi, meskipun I/O yang hilang akan 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 perfmon 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) terus menunda layanan permintaan I/O lama demi 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 Windows System Monitor (PERFMON.EXE) karena sebagian besar I/Os segera dilayanakan. Permintaan I/O yang panjang dapat diperburuk oleh beban kerja yang melakukan I/O berurutan dalam jumlah besar, seperti pencadangan dan pemulihan, pemindaian tabel, pengurutan, pembuatan indeks, pemuatan massal, dan nol file.

I/Os panjang terisolasi yang tidak muncul terkait dengan 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:

I/Os ekstra (seperti utas latar belakang penulis malas yang sangat aktif)
Rasio kompilasi ulang yang lebih tinggi
Kueri yang berjalan lebih lama (jika penantian peruntukan 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. 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 mengatur 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 diberi sinyal atau tidak. Buffer cincin ini memiliki informasi status tergantung pada kondisi RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, atau RESOURCE_MEMVIRTUAL_LOW saat ini.
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 di antara 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 satu set 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 pembacaan apa pun yang gagal dengan checksum, halaman robek, atau kesalahan I/O lainnya empat kali. Jika pembacaan 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 kolom page_verify_option dalam tampilan katalog sys.databases atau properti IsTornPageDetectionEnabled dari fungsi DATABASEPROPERTYEX .

Catatan

Jika pengaturan perlindungan halaman diubah, pengaturan baru tidak segera memengaruhi seluruh database. Sebaliknya, 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 yang robek, yang diperkenalkan di SQL Server 2000, 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, sehingga selalu dimungkinkan untuk mengetahui kapan hanya sebagian sektor yang berhasil masuk ke disk: jika sedikit berada dalam keadaan salah ketika halaman kemudian dibaca, halaman ditulis dengan salah dan halaman robek terdeteksi. Deteksi halaman torn 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 OPSI UBAH 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 setiap kali manajer buffer membaca halaman dari disk. Untuk informasi tentang pengaturan checksum, lihat ALTER DATABASE SET Options (Transact-SQL).

Penting

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

Memahami Akses Memori Yang Tidak 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 sys.dm_os_memory_objects DMV. 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 node 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. Partisi 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 membuat pola partisi yang sempurna di muka. Dalam sebagian besar kasus, menggunakan partisi tunggal sudah cukup, tetapi dalam beberapa skenario ini dapat menyebabkan pertikaian 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 implementasi berbasis per node atau per-CPU. Setelah dipromosikan, PMO tetap dipromosikan sampai 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 DMV sys.dm_os_memory_objectscontention_factor, partition_type, exclusive_allocations_count, dan waiting_tasks_count.

Lihat juga

Opsi Konfigurasi Server Memori Server
Membaca Halaman
Menulis Halaman
Cara: Mengonfigurasi SQL Server untuk Menggunakan Soft-NUMA
Persyaratan untuk Menggunakan Tabel Memory-Optimized
Mengatasi Masalah Kehabisan Memori Menggunakan tabel Memory-Optimized