MS SQL Server adalah arsitektur client-server. Proses MS SQL Server dimulai dengan aplikasi klien mengirimkan permintaan. SQL Server menerima, memproses, dan membalas permintaan dengan data yang diproses. Mari kita bahas secara rinci seluruh arsitektur yang ditunjukkan di bawah ini:
Seperti pada Diagram di bawah ini, ada tiga komponen utama dalam Arsitektur SQL Server:
- Lapisan Protokol
- Mesin Relasional
- Mesin Penyimpanan
Mari kita bahas secara detail tentang ketiga modul utama di atas. Dalam tutorial ini, Anda akan belajar.
- Lapisan Protokol - SNI
- Berbagi memori
- TCP / IP
- Pipa bernama
- Apa itu TDS?
- Mesin Relasional
- Pengurai CMD
- Pengoptimal
- Pelaksana Kueri
- Mesin Penyimpanan
- Jenis file
- Metode Akses
- Manajer Penyangga
- Rencanakan Cache
- Penguraian Data: Cache buffer & Penyimpanan Data
- Manajer Transaksi
Lapisan Protokol - SNI
MS SQL SERVER PROTOCOL LAYER mendukung 3 Jenis Arsitektur Server Klien. Kita akan mulai dengan " Tiga Jenis Arsitektur Server Klien" yang didukung MS SQL Server.
Berbagi memori
Mari kita pertimbangkan kembali skenario Percakapan di pagi hari.
MOM dan TOM - Di sini Tom dan ibunya, berada di tempat logis yang sama, yaitu di rumah mereka. Tom bisa meminta kopi dan ibu bisa menyajikannya panas.
MS SQL SERVER - Di sini server MS SQL menyediakan PROTOKOL MEMORI BERBAGI . Di sini CLIENT dan server MS SQL dijalankan pada mesin yang sama. Keduanya dapat berkomunikasi melalui protokol Memori Bersama.
Analogi: Mari memetakan entitas dalam dua skenario di atas. Kita dapat dengan mudah memetakan Tom ke Klien, Ibu ke server SQL, Rumah ke Mesin, dan Komunikasi Verbal ke Protokol Memori Bersama.
Dari meja konfigurasi dan instalasi:
Untuk Sambungan ke DB Lokal - Di SQL Management Studio, Opsi "Nama Server" bisa jadi
"."
"localhost"
"127.0.0.1"
"Mesin \ Instance"
TCP / IP
Sekarang pertimbangkan di malam hari, Tom sedang dalam mood pesta. Dia ingin Kopi dipesan dari Kedai Kopi ternama. Kedai Kopi ini terletak 10 km dari rumahnya.
Di sini Tom dan Starbuck berada di lokasi fisik yang berbeda. Tom di rumah dan Starbucks di pasar yang sibuk. Mereka berkomunikasi melalui jaringan Seluler. Demikian pula, MS SQL SERVER menyediakan kemampuan untuk berinteraksi melalui protokol TCP / IP, di mana CLIENT dan MS SQL Server berada jauh satu sama lain dan diinstal pada mesin terpisah.
Analogi: Mari memetakan entitas dalam dua skenario di atas. Kita dapat dengan mudah memetakan Tom ke Klien, Starbuck ke server SQL, tempat Rumah / Pasar ke lokasi Terpencil dan akhirnya jaringan Seluler ke protokol TCP / IP.
Catatan dari meja Konfigurasi / instalasi:
- Di SQL Management Studio - Untuk Koneksi melalui TCP \ IP, Opsi "Nama Server" harus "Mesin \ Contoh server."
- Server SQL menggunakan port 1433 di TCP / IP.
Pipa bernama
Sekarang akhirnya di malam hari, Tom ingin menikmati teh hijau muda yang disiapkan dengan baik oleh tetangganya, Sierra.
Di sini Tom dan Tetangganya , Sierra, berada di lokasi fisik yang sama , saling bertetangga. Mereka berkomunikasi melalui jaringan Intra. Demikian pula, MS SQL SERVER menyediakan kemampuan untuk berinteraksi melalui protokol Named Pipe . Di sini KLIEN dan MS SQL SERVER terhubung melalui LAN .
Analogi: Mari memetakan entitas dalam dua skenario di atas. Kita dapat dengan mudah memetakan Tom ke Klien, Sierra ke SQL server, Tetangga ke LAN dan akhirnya jaringan Intra ke Named Pipe Protocol.
Catatan dari meja Konfigurasi / instalasi:
- Untuk Koneksi melalui Pipa Bernama. Opsi ini dinonaktifkan secara default dan harus diaktifkan oleh Manajer Konfigurasi SQL.
Apa itu TDS?
Sekarang setelah kita mengetahui bahwa ada tiga jenis Arsitektur Server-Klien, mari kita lihat TDS:
- TDS adalah singkatan dari Tabular Data Stream.
- Semua 3 protokol menggunakan paket TDS. TDS dikemas dalam paket Jaringan. Ini memungkinkan transfer data dari mesin klien ke mesin server.
- TDS pertama kali dikembangkan oleh Sybase dan sekarang Dimiliki oleh Microsoft
Mesin Relasional
Mesin Relasional juga dikenal sebagai Pemroses Kueri. Ini memiliki komponen SQL Server yang menentukan apa sebenarnya kueri yang perlu dilakukan dan bagaimana hal itu dapat dilakukan dengan sebaik-baiknya. Ini bertanggung jawab atas eksekusi kueri pengguna dengan meminta data dari mesin penyimpanan dan memproses hasil yang dikembalikan.
Seperti yang digambarkan pada Diagram Arsitektur terdapat 3 komponen utama dari Mesin Relasional. Mari pelajari komponen secara detail:
Pengurai CMD
Data setelah diterima dari Protocol Layer kemudian diteruskan ke Relational Engine. "CMD Parser" adalah komponen pertama dari Mesin Relasional yang menerima data Kueri. Tugas utama CMD Parser adalah memeriksa kueri untuk kesalahan Sintaksis dan Semantik. Akhirnya, ini menghasilkan Pohon Kueri . Mari kita bahas secara detail.
Pemeriksaan sintaksis:
- Seperti setiap bahasa Pemrograman lainnya, MS SQL juga memiliki kumpulan Kata Kunci yang telah ditentukan sebelumnya. Selain itu, SQL Server memiliki tata bahasanya sendiri yang dipahami oleh SQL server.
- SELECT, INSERT, UPDATE, dan banyak lainnya termasuk dalam daftar Kata Kunci standar MS SQL.
- CMD Parser melakukan pemeriksaan sintaksis. Jika masukan pengguna tidak mengikuti sintaks bahasa atau aturan tata bahasa ini, ini akan mengembalikan kesalahan.
Contoh: Misalkan orang Rusia pergi ke restoran Jepang. Dia memesan makanan cepat saji dalam bahasa Rusia. Sayangnya, pelayannya hanya mengerti bahasa Jepang. Apa hasil yang paling jelas?
Jawabannya adalah - pelayan tidak dapat memproses pesanan lebih lanjut.
Tidak boleh ada penyimpangan dalam Tata Bahasa atau bahasa yang diterima SQL server. Jika ada, server SQL tidak dapat memprosesnya dan karenanya akan mengembalikan pesan kesalahan.
Kita akan belajar lebih banyak tentang query MS SQL di tutorial yang akan datang. Namun, pertimbangkan Sintaks Query paling dasar di bawah ini sebagai
SELECT * from;
Sekarang, untuk mendapatkan persepsi tentang apa yang dilakukan sintaksis, katakan jika pengguna menjalankan kueri dasar seperti di bawah ini:
SELECR * from
Perhatikan bahwa alih-alih pengguna 'PILIH', ketik "SELECR."
Hasil: Parser CMD akan mengurai pernyataan ini dan akan menampilkan pesan kesalahan. Karena "SELECR" tidak mengikuti nama kata kunci dan tata bahasa yang telah ditentukan sebelumnya. Di sini CMD Parser mengharapkan "PILIH".
Pemeriksaan semantik:
- Ini dilakukan oleh Normalizer .
- Dalam bentuknya yang paling sederhana, ia memeriksa apakah nama Kolom, nama Tabel yang ditanyai ada di Skema. Dan jika ada, ikat ke Query. Ini juga dikenal sebagai Binding .
- Kompleksitas meningkat ketika kueri pengguna berisi VIEW. Normalizer melakukan penggantian dengan definisi tampilan yang disimpan secara internal dan banyak lagi.
Mari kita pahami ini dengan bantuan contoh di bawah ini -
SELECT * from USER_ID
Hasil: Parser CMD akan mengurai pernyataan ini untuk pemeriksaan Semantik. Pengurai akan memunculkan pesan kesalahan karena Normalizer tidak akan menemukan tabel yang diminta (USER_ID) karena tidak ada.
Buat Pohon Kueri:
- Langkah ini menghasilkan pohon eksekusi yang berbeda di mana kueri dapat dijalankan.
- Perhatikan bahwa, semua pohon yang berbeda memiliki keluaran yang diinginkan sama.
Pengoptimal
Pekerjaan pengoptimal adalah membuat rencana eksekusi untuk kueri pengguna. Ini adalah rencana yang akan menentukan bagaimana kueri pengguna akan dieksekusi.
Perhatikan bahwa tidak semua kueri dioptimalkan. Optimasi dilakukan untuk perintah DML (Data Modification Language) seperti SELECT, INSERT, DELETE, dan UPDATE. Kueri tersebut pertama kali ditandai kemudian dikirim ke pengoptimal. Perintah DDL seperti CREATE dan ALTER tidak dioptimalkan, tetapi mereka dikompilasi menjadi bentuk internal. Biaya kueri dihitung berdasarkan faktor-faktor seperti penggunaan CPU, penggunaan memori, dan kebutuhan Input / Output.
Peran pengoptimal adalah menemukan rencana pelaksanaan yang termurah, bukan yang terbaik, dan hemat biaya.
Sebelum kita membahas lebih detail teknis tentang Pengoptimal, pertimbangkan contoh kehidupan nyata di bawah ini:
Contoh:
Misalkan, Anda ingin membuka rekening Bank online. Anda sudah tahu tentang satu Bank yang membutuhkan waktu maksimal 2 Hari untuk membuka rekening. Namun, Anda juga memiliki daftar 20 bank lain, yang mungkin memerlukan waktu kurang dari 2 hari atau tidak. Anda dapat mulai berinteraksi dengan bank-bank ini untuk menentukan bank mana yang membutuhkan waktu kurang dari 2 hari. Sekarang, Anda mungkin tidak menemukan bank yang membutuhkan waktu kurang dari 2 hari, dan ada waktu tambahan yang hilang karena aktivitas pencarian itu sendiri. Akan lebih baik membuka rekening di bank pertama itu sendiri.
Kesimpulan: Lebih penting memilih dengan bijak. Tepatnya, pilih opsi mana yang terbaik, bukan yang termurah.
Demikian pula, MS SQL Optimizer bekerja pada algoritme lengkap / heuristik bawaan. Tujuannya adalah untuk meminimalkan waktu berjalan kueri. Semua algoritma Pengoptimal adalah hak milik Microsoft dan rahasia. Meskipun , di bawah ini adalah langkah-langkah tingkat tinggi yang dilakukan oleh MS SQL Optimizer. Pencarian Optimasi mengikuti tiga fase seperti yang ditunjukkan pada diagram di bawah ini:
Fase 0: Pencarian Rencana Sepele:
- Ini juga dikenal sebagai tahap Pra-pengoptimalan .
- Untuk beberapa kasus, hanya ada satu rencana yang praktis dan dapat diterapkan, yang dikenal sebagai rencana sepele. Tidak perlu membuat rencana yang dioptimalkan. Alasannya adalah, mencari lebih banyak akan menghasilkan menemukan rencana eksekusi run time yang sama. Itu juga dengan biaya tambahan Mencari Rencana yang dioptimalkan yang tidak diperlukan sama sekali.
- Jika ada rencana Trivial ditemukan, maka 1 st Fase dimulai.
Tahap 1: Pencarian rencana pemrosesan Transaksi
- Ini termasuk pencarian Rencana Sederhana dan Kompleks .
- Pencarian Rencana Sederhana: Data masa lalu dari kolom dan Indeks yang terlibat dalam Query, akan digunakan untuk Analisis Statistik. Ini biasanya terdiri tetapi tidak terbatas pada satu Indeks Per tabel.
- Namun, jika rencana sederhana tidak ditemukan, maka rencana yang lebih kompleks dicari. Ini melibatkan Beberapa Indeks per tabel.
Tahap 2: Pemrosesan dan Pengoptimalan Paralel.
- Jika tidak ada strategi di atas yang berhasil, Pengoptimal akan mencari kemungkinan Pemrosesan Paralel. Ini tergantung pada kapabilitas pemrosesan dan konfigurasi Mesin.
- Jika itu masih tidak memungkinkan, maka tahap pengoptimalan terakhir dimulai. Sekarang, tujuan pengoptimalan terakhir adalah menemukan semua opsi lain yang memungkinkan untuk menjalankan kueri dengan cara terbaik. Fase optimasi terakhir Algoritma adalah Microsoft Propriety.
Pelaksana Kueri
Pelaksana kueri memanggil Metode Akses. Ini menyediakan rencana eksekusi untuk logika pengambilan data yang diperlukan untuk eksekusi. Setelah data diterima dari Storage Engine, hasilnya dipublikasikan ke lapisan Protokol. Terakhir, data dikirim ke pengguna akhir.
Mesin Penyimpanan
Pekerjaan Mesin Penyimpanan adalah menyimpan data dalam sistem penyimpanan seperti Disk atau SAN dan mengambil data saat diperlukan. Sebelum kita mendalami mesin Storage, mari kita lihat bagaimana data disimpan dalam Database dan jenis file yang tersedia.
File Data dan Luas:
File Data, secara fisik menyimpan data dalam bentuk halaman data, dengan setiap halaman data berukuran 8KB, membentuk unit penyimpanan terkecil di SQL Server. Halaman data ini secara logis dikelompokkan untuk membentuk luasan. Tidak ada objek yang diberi halaman di SQL Server.
Pemeliharaan objek dilakukan melalui luasan. Halaman tersebut memiliki bagian yang disebut Page Header dengan ukuran 96 byte, membawa informasi metadata tentang halaman seperti Jenis Halaman, Nomor Halaman, Ukuran Ruang Terpakai, Ukuran Ruang Kosong, dan Pointer ke halaman berikutnya dan halaman sebelumnya , dll.
Jenis file
- File utama
- Setiap database berisi satu file utama.
- Ini menyimpan semua data penting yang terkait dengan tabel, tampilan, Pemicu, dll.
- Ekstensi adalah. mdf biasanya tetapi dapat dari ekstensi apa pun.
- File sekunder
- Database mungkin atau mungkin tidak berisi beberapa file Sekunder.
- Ini opsional dan berisi data khusus pengguna.
- Ekstensi adalah. ndf biasanya tetapi dapat dari ekstensi apa pun.
- File log
- Juga dikenal sebagai Menulis di depan log.
- Ekstensi adalah. ldf
- Digunakan untuk Manajemen Transaksi.
- Ini digunakan untuk memulihkan dari setiap kejadian yang tidak diinginkan. Lakukan tugas penting Rollback to uncommitted transaksi.
Storage Engine memiliki 3 komponen; mari kita lihat secara detail.
Metode Akses
Bertindak sebagai antarmuka antara pelaksana kueri dan Buffer Manager / Transaction Logs.
Metode Akses itu sendiri tidak melakukan eksekusi apa pun.
Tindakan pertama adalah menentukan apakah kueri tersebut:
- Select Statement (DDL)
- Pernyataan Non-Select (DDL & DML)
Bergantung pada hasilnya, Metode Akses mengambil langkah-langkah berikut:
- Jika querynya adalah DDL , pernyataan SELECT, query tersebut diteruskan ke Buffer Manager untuk diproses lebih lanjut.
- Dan jika kueri jika DDL, pernyataan NON-SELECT , kueri tersebut diteruskan ke Manajer Transaksi. Ini sebagian besar mencakup pernyataan UPDATE.
Manajer Penyangga
Buffer manager mengelola fungsi inti untuk modul di bawah ini:
- Rencanakan Cache
- Penguraian Data: Cache buffer & Penyimpanan data
- Halaman Kotor
Kita akan mempelajari Plan, Buffer dan Data cache di bagian ini. Kami akan membahas halaman Kotor di bagian Transaksi.
Rencanakan Cache
- Rencana Kueri yang Ada: Manajer penyangga memeriksa apakah rencana eksekusi ada di Cache Rencana yang disimpan. Jika ya, maka cache paket kueri dan cache data yang terkait digunakan.
- Paket Cache pertama kali: Dari mana cache Paket yang ada berasal?
Jika rencana eksekusi kueri pertama kali sedang dijalankan dan kompleks, masuk akal untuk menyimpannya di cache Plane. Ini akan memastikan ketersediaan yang lebih cepat saat server SQL mendapatkan kueri yang sama di lain waktu. Jadi, tidak ada yang lain selain kueri itu sendiri yang eksekusi Plan disimpan jika dijalankan untuk pertama kalinya.
Penguraian Data: Cache buffer & Penyimpanan Data
Manajer penyangga menyediakan akses ke data yang dibutuhkan. Di bawah dua pendekatan dimungkinkan tergantung pada apakah data ada di cache data atau tidak:
Buffer Cache - Soft Parsing:
Buffer Manager mencari Data di Buffer di cache Data. Jika ada, maka Data ini digunakan oleh Query Executor. Ini meningkatkan kinerja karena jumlah operasi I / O berkurang saat mengambil data dari cache dibandingkan dengan mengambil data dari penyimpanan Data.
Penyimpanan Data - Parsing Keras:
Jika data tidak ada di Buffer Manager dari yang dibutuhkan Data dicari di Penyimpanan Data. Jika juga menyimpan data dalam cache data untuk digunakan di masa mendatang.
Halaman Kotor
Ini disimpan sebagai logika pemrosesan Manajer Transaksi. Kami akan belajar secara rinci di bagian Manajer Transaksi.
Manajer Transaksi
Manajer Transaksi dipanggil ketika metode akses menentukan bahwa Query adalah pernyataan Non-Select.
Manajer Log
- Pengelola Log melacak semua pembaruan yang dilakukan di sistem melalui log di Log Transaksi.
- Log memiliki Nomor Urutan Log dengan ID Transaksi dan Catatan Modifikasi Data .
- Ini digunakan untuk melacak Transaksi yang Dilakukan dan Transaksi Rollback .
Manajer Kunci
- Selama Transaksi, data terkait dalam Penyimpanan Data berada dalam status Terkunci. Proses ini ditangani oleh Lock Manager.
- Proses ini memastikan konsistensi dan isolasi data . Juga dikenal sebagai properti ACID.
Proses Eksekusi
- Log Manager mulai melakukan logging dan Lock Manager mengunci data terkait.
- Salinan data disimpan dalam cache Buffer.
- Salinan data yang seharusnya diperbarui disimpan di buffer Log dan semua peristiwa memperbarui data di buffer Data.
- Halaman yang menyimpan data juga dikenal sebagai Halaman Kotor .
- Checkpoint dan Write-Ahead Logging: Proses ini berjalan dan menandai semua halaman dari Dirty Pages ke Disk, tetapi halaman tersebut tetap berada di cache. Frekuensinya kira-kira 1 run per menit, tetapi halaman tersebut pertama-tama didorong ke halaman Data dari file log dari Buffer log. Ini dikenal sebagai Write Ahead Logging.
- Penulis Malas: Halaman Kotor bisa tetap ada di memori. Ketika SQL server mengamati beban besar dan memori Buffer diperlukan untuk transaksi baru, ini membebaskan Halaman Kotor dari cache. Ini beroperasi pada LRU - Algoritma yang paling sedikit digunakan baru-baru ini untuk membersihkan halaman dari kumpulan buffer ke disk.
Ringkasan:
- Tiga Jenis Arsitektur Server Klien ada: 1) Memori Bersama 2) TCP / IP 3) Pipa Bernama
- TDS, dikembangkan oleh Sybase dan sekarang dimiliki oleh Microsoft, adalah sebuah paket yang dikemas dalam paket Jaringan untuk transfer data dari mesin klien ke mesin server.
- Mesin Relasional berisi tiga komponen utama:
CMD Parser: Ini bertanggung jawab atas kesalahan Sintaksis dan Semantik & akhirnya menghasilkan Pohon Kueri.
Pengoptimal: Peran pengoptimal adalah menemukan rencana pelaksanaan yang termurah, bukan yang terbaik, dan hemat biaya.
Pelaksana Kueri: Pelaksana kueri memanggil Metode Akses dan menyediakan rencana eksekusi untuk logika pengambilan data yang diperlukan untuk eksekusi.
- Tiga jenis file ada File primer, file sekunder, dan file log.
- Mesin Penyimpanan: Memiliki komponen penting berikut ini
Metode Akses: Komponen ini Menentukan apakah kueri adalah Pernyataan Pilih atau Non-Pilih. Memanggil Buffer dan Transfer Manager sesuai dengan itu.
Manajer Buffer: Manajer buffer mengelola fungsi inti untuk Cache Rencana, Parsing Data & Halaman Kotor.
Transaction Manager: It manager Non-Select Transaction dengan bantuan Manajer Log dan Lock. Juga, memfasilitasi implementasi penting dari pembuatan log Write Ahead dan penulis Malas.