MS SQL Server - Arsitektur

Kami telah mengklasifikasikan arsitektur SQL Server ke dalam bagian-bagian berikut untuk memudahkan pemahaman -

Arsitektur umum
Arsitektur memori
Arsitektur file data
Arsitektur file log

Arsitektur Umum

Client - Di mana permintaan dimulai.

Query - Kueri SQL yang merupakan bahasa tingkat tinggi.

Logical Units - Kata kunci, ekspresi dan operator, dll.

N/W Packets - Kode terkait jaringan.

Protocols - Di SQL Server kami memiliki 4 protokol.

Memori bersama (untuk koneksi lokal dan tujuan pemecahan masalah).
Pipa bernama (untuk koneksi yang ada dalam konektivitas LAN).
TCP / IP (untuk koneksi yang ada dalam konektivitas WAN).
VIA-Virtual Interface Adapter (memerlukan perangkat keras khusus untuk disiapkan oleh vendor dan juga tidak digunakan lagi dari versi SQL 2012).

Server - Tempat SQL Services terinstal dan database berada.

Relational Engine- Di sinilah eksekusi nyata akan dilakukan. Ini berisi Query parser, Query optimizer dan Query executor.

Query Parser (Command Parser) and Compiler (Translator) - Ini akan memeriksa sintaks kueri dan akan mengubah kueri ke bahasa mesin.

Query Optimizer - Ini akan menyiapkan rencana eksekusi sebagai output dengan mengambil kueri, statistik dan pohon Algebrizer sebagai input.

Execution Plan - Ini seperti peta jalan, yang berisi urutan semua langkah yang akan dilakukan sebagai bagian dari eksekusi kueri.

Query Executor - Di sinilah kueri akan dieksekusi selangkah demi selangkah dengan bantuan rencana eksekusi dan juga mesin penyimpanan akan dihubungi.

Storage Engine - Bertanggung jawab untuk penyimpanan dan pengambilan data pada sistem penyimpanan (disk, SAN, dll.,), Manipulasi data, penguncian, dan pengelolaan transaksi.

SQL OS- Ini terletak di antara mesin host (OS Windows) dan SQL Server. Semua aktivitas yang dilakukan di mesin database diurus oleh SQL OS. SQL OS menyediakan berbagai layanan sistem operasi, seperti manajemen memori yang menangani kumpulan buffer, buffer log, dan deteksi kebuntuan menggunakan struktur pemblokiran dan penguncian.

Checkpoint Process- Checkpoint adalah proses internal yang menulis semua halaman kotor (halaman yang dimodifikasi) dari Buffer Cache ke disk Fisik. Selain itu, ia juga menulis catatan log dari buffer log ke file fisik. Penulisan halaman kotor dari cache buffer ke file data juga dikenal sebagai Hardening of dirty pages.

Ini adalah proses khusus dan berjalan secara otomatis oleh SQL Server pada interval tertentu. SQL Server menjalankan proses checkpoint untuk setiap database secara individual. Checkpoint membantu mengurangi waktu pemulihan untuk SQL Server jika terjadi shutdown yang tidak terduga atau sistem crash \ Failure.

Pos pemeriksaan di SQL Server

Di SQL Server 2012 ada empat jenis file checkpoints -

Automatic - Ini adalah pos pemeriksaan paling umum yang berjalan sebagai proses di latar belakang untuk memastikan Database SQL Server dapat dipulihkan dalam batas waktu yang ditentukan oleh Interval Pemulihan - Opsi Konfigurasi Server.
Indirect- Ini baru di SQL Server 2012. Ini juga berjalan di latar belakang tetapi untuk memenuhi waktu pemulihan target yang ditentukan pengguna untuk database tertentu di mana opsi telah dikonfigurasi. Setelah Target_Recovery_Time untuk database tertentu telah dipilih, ini akan menimpa Interval Pemulihan yang ditentukan untuk server dan menghindari checkpoint otomatis pada DB tersebut.
Manual- Yang ini berjalan seperti pernyataan T-SQL lainnya, setelah Anda mengeluarkan perintah checkpoint, itu akan berjalan sampai selesai. Pos pemeriksaan manual hanya berjalan untuk database Anda saat ini. Anda juga dapat menentukan Checkpoint_Duration yang bersifat opsional - durasi ini menentukan waktu di mana Anda ingin checkpoint Anda selesai.
Internal- Sebagai pengguna, Anda tidak dapat mengontrol pos pemeriksaan internal. Dikeluarkan pada operasi tertentu seperti
- Shutdown memulai operasi checkpoint pada semua database kecuali jika shutdown tidak bersih (shutdown with nowait).
- Jika model pemulihan diubah dari Penuh \ Bulk-login ke Sederhana.
- Saat mengambil backup database.
- Jika DB Anda dalam model pemulihan sederhana, proses pos pemeriksaan dijalankan secara otomatis baik ketika log menjadi 70% penuh, atau berdasarkan opsi Server-Interval Pemulihan.
- Perintah Alter database untuk menambah atau menghapus file data \ log juga memulai checkpoint.
- Checkpoint juga dilakukan ketika model pemulihan DB dicatat secara massal dan operasi yang dicatat minimal dilakukan.
- Pembuatan Snapshot DB.
Lazy Writer Process- Penulis yang malas akan mendorong halaman kotor ke disk untuk alasan yang sama sekali berbeda, karena ia perlu mengosongkan memori di kumpulan buffer. Ini terjadi ketika server SQL berada di bawah tekanan memori. Sejauh yang saya tahu, ini dikendalikan oleh proses internal dan tidak ada pengaturan untuk itu.

SQL server secara konstan memonitor penggunaan memori untuk menilai pertentangan sumber daya (atau ketersediaan); tugasnya adalah memastikan bahwa ada sejumlah ruang kosong yang tersedia setiap saat. Sebagai bagian dari proses ini, ketika ia melihat adanya pertentangan sumber daya, itu memicu Lazy Writer untuk mengosongkan beberapa halaman dalam memori dengan menulis halaman kotor ke disk. Ia menggunakan algoritma Least recent used (LRU) untuk memutuskan halaman mana yang akan di-flush ke disk.

Jika Lazy Writer selalu aktif, ini bisa menunjukkan kemacetan memori.

Arsitektur Memori

Berikut adalah beberapa fitur menonjol dari arsitektur memori.

Salah satu tujuan desain utama semua perangkat lunak database adalah meminimalkan I / O disk karena pembacaan dan penulisan disk adalah salah satu operasi yang paling banyak menggunakan sumber daya.
Memori di windows dapat dipanggil dengan Ruang Alamat Virtual, dibagi oleh mode Kernel (mode OS) dan mode Pengguna (Aplikasi seperti SQL Server).
SQL Server "Ruang alamat pengguna" dipecah menjadi dua wilayah: MemToLeave dan Buffer Pool.
Ukuran MemToLeave (MTL) dan Buffer Pool (BPool) ditentukan oleh SQL Server selama startup.
Buffer managementadalah komponen kunci dalam mencapai I / O yang sangat efisien. Komponen manajemen buffer terdiri dari dua mekanisme: manajer buffer untuk mengakses dan memperbarui halaman database, dan buffer pool untuk mengurangi I / O file database.
Kolam penyangga selanjutnya dibagi menjadi beberapa bagian. Yang paling penting adalah cache buffer (juga disebut sebagai cache data) dan cache prosedur.Buffer cachemenyimpan halaman data dalam memori sehingga data yang sering diakses dapat diambil dari cache. Alternatifnya adalah membaca halaman data dari disk. Membaca halaman data dari cache mengoptimalkan kinerja dengan meminimalkan jumlah operasi I / O yang diperlukan yang secara inheren lebih lambat daripada mengambil data dari memori.
Procedure cachemenyimpan prosedur yang tersimpan dan rencana eksekusi kueri untuk meminimalkan berapa kali rencana kueri harus dibuat. Anda dapat menemukan informasi tentang ukuran dan aktivitas dalam cache prosedur menggunakan pernyataan DBCC PROCCACHE.

Bagian lain dari kumpulan penyangga termasuk -

System level data structures - Menyimpan data tingkat contoh SQL Server tentang database dan kunci.
Log cache - Dicadangkan untuk membaca dan menulis halaman log transaksi.
Connection context- Setiap koneksi ke instance memiliki area memori kecil untuk merekam status koneksi saat ini. Informasi ini mencakup prosedur tersimpan dan parameter fungsi yang ditentukan pengguna, posisi kursor, dan lainnya.
Stack space - Windows mengalokasikan ruang tumpukan untuk setiap utas yang dimulai oleh SQL Server.

Arsitektur File Data

Arsitektur Data File memiliki komponen berikut -

Grup File

File database dapat dikelompokkan bersama dalam grup file untuk tujuan alokasi dan administrasi. Tidak ada file yang dapat menjadi anggota lebih dari satu grup file. File log tidak pernah menjadi bagian dari grup file. Ruang log dikelola secara terpisah dari ruang data.

Ada dua jenis grup file di SQL Server, Utama dan Ditentukan pengguna. Grup file utama berisi file data utama dan file lain yang tidak secara khusus ditetapkan ke grup file lain. Semua halaman untuk tabel sistem dialokasikan dalam grup file utama. Grup file yang ditentukan pengguna adalah grup file apa pun yang ditentukan menggunakan kata kunci grup file dalam membuat database atau mengubah pernyataan database.

Satu grup file di setiap database beroperasi sebagai grup file default. Saat SQL Server mengalokasikan halaman ke tabel atau indeks yang tidak ada grup file yang ditentukan saat dibuat, halaman dialokasikan dari grup file default. Untuk mengalihkan grup file default dari satu grup file ke grup file lain, itu harus memiliki peran db tetap db_owner.

Secara default, grup file utama adalah grup file default. Pengguna harus memiliki peran database tetap db_owner untuk mengambil cadangan file dan grup file satu per satu.

File

Database memiliki tiga jenis file - file data primer, file data sekunder, dan file log. File data primer adalah titik awal dari database dan menunjuk ke file lain di database.

Setiap database memiliki satu file data primer. Kami dapat memberikan ekstensi apa pun untuk file data utama tetapi ekstensi yang disarankan adalah.mdf. File data sekunder adalah file selain file data primer yang ada di database tersebut. Beberapa database mungkin memiliki beberapa file data sekunder. Beberapa database mungkin tidak memiliki satu file data sekunder. Ekstensi yang disarankan untuk file data sekunder adalah.ndf.

File log menyimpan semua informasi log yang digunakan untuk memulihkan database. Database harus memiliki setidaknya satu file log. Kita dapat memiliki banyak file log untuk satu database. Ekstensi yang direkomendasikan untuk file log adalah.ldf.

Lokasi semua file dalam database dicatat di database master dan file utama untuk database. Sebagian besar waktu, mesin database menggunakan lokasi file dari database master.

File memiliki dua nama - Logis dan Fisik. Nama logis digunakan untuk merujuk ke file di semua pernyataan T-SQL. Nama fisik OS_file_name, harus mengikuti aturan OS. File Data dan Log dapat ditempatkan pada sistem file FAT atau NTFS, tetapi tidak dapat ditempatkan pada sistem file terkompresi. Bisa ada hingga 32.767 file dalam satu database.

Luas

Luas adalah unit dasar di mana ruang dialokasikan untuk tabel dan indeks. Luasnya adalah 8 halaman bersebelahan atau 64 KB. SQL Server memiliki dua jenis luasan - Seragam dan Campuran. Luas seragam hanya terdiri dari satu objek. Luas campuran dibagi hingga delapan objek.

Halaman

Ini adalah unit dasar penyimpanan data di MS SQL Server. Ukuran halaman 8KB. Awal setiap halaman adalah header 96 byte yang digunakan untuk menyimpan informasi sistem seperti jenis halaman, jumlah ruang kosong pada halaman dan id objek dari objek yang memiliki halaman. Ada 9 jenis halaman data di SQL Server.

Data - Baris data dengan semua data kecuali teks, ntext dan data gambar.
Index - Entri indeks.
Tex\Image - Teks, gambar dan data ntext.
GAM - Informasi tentang luasan yang dialokasikan.
SGAM - Informasi tentang luasan yang dialokasikan pada tingkat sistem.
Page Free Space (PFS) - Informasi tentang ruang kosong yang tersedia di halaman.
Index Allocation Map (IAM) - Informasi tentang luasan yang digunakan oleh tabel atau indeks.
Bulk Changed Map (BCM) - Informasi tentang luasan yang dimodifikasi oleh operasi massal sejak pernyataan log cadangan terakhir.
Differential Changed Map (DCM) - Informasi tentang luasan yang telah berubah sejak pernyataan basis data cadangan terakhir.

Arsitektur File Log

Log transaksi SQL Server beroperasi secara logis seolah-olah log transaksi adalah string catatan log. Setiap catatan log diidentifikasi oleh Log Sequence Number (LSN). Setiap catatan log berisi ID dari transaksi yang dimilikinya.

Catatan log untuk modifikasi data merekam baik operasi logis yang dilakukan atau merekam gambar sebelum dan sesudah dari data yang dimodifikasi. Gambar sebelumnya adalah salinan dari data sebelum operasi dilakukan; gambar setelah adalah salinan dari data setelah operasi dilakukan.

Langkah-langkah untuk memulihkan operasi bergantung pada jenis catatan log -

Operasi logis dicatat.
- Untuk menggulung operasi logis ke depan, operasi dilakukan lagi.
- Untuk menggulung kembali operasi logis, operasi logis terbalik dilakukan.
Gambar sebelum dan sesudah dicatat.
- Untuk menggulung operasi ke depan, gambar setelah diterapkan.
- Untuk mengembalikan operasi, gambar sebelumnya diterapkan.

Berbagai jenis operasi dicatat dalam log transaksi. Operasi ini termasuk -

Awal dan akhir setiap transaksi.
Setiap modifikasi data (memasukkan, memperbarui, atau menghapus). Ini termasuk perubahan dengan prosedur tersimpan sistem atau pernyataan bahasa definisi data (DDL) ke tabel mana pun, termasuk tabel sistem.
Setiap tingkat dan alokasi halaman atau de alokasi.
Membuat atau menghapus tabel atau indeks.

Operasi rollback juga dicatat. Setiap transaksi mencadangkan ruang di log transaksi untuk memastikan bahwa ada cukup ruang log untuk mendukung rollback yang disebabkan oleh pernyataan rollback eksplisit atau jika error ditemukan. Ruang yang dicadangkan ini dibebaskan saat transaksi selesai.

Bagian dari file log dari catatan log pertama yang harus ada untuk rollback seluruh database yang berhasil ke catatan log yang ditulis terakhir disebut bagian aktif dari log, atau log aktif. Ini adalah bagian dari log yang diperlukan untuk pemulihan penuh database. Tidak ada bagian dari log aktif yang dapat dipotong. LSN dari catatan log pertama ini dikenal sebagai LSN pemulihan minimum (Min LSN).

Mesin Database SQL Server membagi setiap file log fisik secara internal menjadi sejumlah file log virtual. File log virtual tidak memiliki ukuran tetap, dan tidak ada jumlah file log virtual yang tetap untuk file log fisik.

Mesin Basis data memilih ukuran file log virtual secara dinamis saat membuat atau memperluas file log. Mesin Database mencoba untuk mempertahankan sejumlah kecil file virtual. Ukuran atau jumlah file log virtual tidak dapat dikonfigurasi atau ditetapkan oleh administrator. Satu-satunya waktu file log virtual memengaruhi kinerja sistem adalah jika file log fisik ditentukan oleh ukuran kecil dan nilai pertumbuhan_peningkat.

Nilai size adalah ukuran awal untuk file log dan nilai growth_increment adalah jumlah ruang yang ditambahkan ke file setiap kali ruang baru diperlukan. Jika file log bertambah menjadi ukuran besar karena banyak penambahan kecil, mereka akan memiliki banyak file log virtual. Ini dapat memperlambat startup database dan juga mencatat backup dan operasi pemulihan.

Kami menyarankan agar Anda menetapkan file log dengan nilai ukuran yang mendekati ukuran akhir yang diperlukan, dan juga memiliki nilai growth_increment yang relatif besar. SQL Server menggunakan log depan tulis (WAL), yang menjamin bahwa tidak ada modifikasi data yang ditulis ke disk sebelum catatan log terkait ditulis ke disk. Ini mempertahankan properti ACID untuk transaksi.