Dasar-dasar I/O SQL Server

Berlaku untuk:SQL ServerAzure SQL Managed InstanceSQL Server di Azure Virtual Machines

Tujuan utama database SQL Server adalah untuk menyimpan dan mengambil data, sehingga input/output disk intensif (I/O) adalah karakteristik inti dari Mesin Database. 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.

Subsistem penyimpanan untuk SQL Server disediakan dalam beberapa faktor bentuk, termasuk drive mekanis dan penyimpanan berbasis solid-state. Artikel ini menyediakan detail tentang cara menggunakan prinsip penangelapan drive untuk meningkatkan I/O Mesin Database.

SQL Server mengharuskan sistem mendukung pengiriman terjamin ke media stabil seperti yang diuraikan di bawah Persyaratan Program Keandalan I/O SQL Server. Untuk informasi selengkapnya tentang persyaratan input dan output untuk Mesin Database SQL Server, lihat Persyaratan Input/Output Disk Mesin Database SQL Server (I/O).

Disk I/O (Masukan/Keluaran Disk)

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:

  • I/O biasanya dilakukan secara asinkron, yang memungkinkan utas panggilan untuk terus memproses saat operasi I/O berlangsung di latar belakang. Dalam beberapa keadaan (misalnya, I/O log yang tidak sejalan), I/Os sinkron dapat terjadi.

  • Semua I/Os dikeluarkan dalam thread yang dipanggil 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.

  • I/O halaman ganda dicapai dengan scatter-gather I/O, yang memungkinkan data ditransfer ke atau keluar dari area memori yang tidak bersambungan. Ini berarti bahwa SQL Server dapat dengan cepat mengisi atau menghapus cache buffer sambil menghindari beberapa permintaan I/O fisik.

Permintaan Proses I/O Berdurasi Lama

Manajer buffer melaporkan permintaan I/O apa pun yang tertunda setidaknya selama 15 detik. Proses ini membantu administrator sistem membedakan antara masalah SQL Server dan masalah subsistem I/O. Pesan kesalahan MSSQLSERVER_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 dapat berupa membaca maupun menulis; pesan saat ini tidak menunjukkan yang mana. Pesan I/O panjang adalah peringatan, bukan kesalahan. Mereka tidak menunjukkan adanya masalah pada SQL Server, melainkan pada sistem I/O dasar. Pesan membantu administrator sistem menemukan penyebab waktu respons SQL Server yang buruk lebih cepat, dan 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 lama

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. Anda tidak dapat mengetahui dari pesan skenario mana yang terjadi, meskipun I/O yang hilang sering menyebabkan waktu habis pengait.

I/Os panjang sering menunjukkan beban kerja SQL Server yang terlalu intens untuk subsistem disk. Indikasi subsistem disk yang tidak memadai mungkin muncul ketika:

  • Beberapa pesan I/O panjang muncul di log kesalahan selama beban kerja SQL Server yang berat.
  • Penghitung Kinerja menunjukkan latensi disk yang lama, antrean disk yang panjang, atau tidak ada waktu jeda pada disk.

Komponen di jalur I/O (misalnya, driver, pengontrol, atau firmware) dapat menyebabkan I/Os panjang dengan terus menunda layanan permintaan I/O lama, demi melayani permintaan yang lebih baru. Masalah ini dapat terjadi di lingkungan yang saling terhubung, seperti jaringan iSCSI dan Fibre Channel (baik karena kesalahan konfigurasi atau kegagalan jalur). Alat Monitor Performa dapat membuat masalah ini sulit dikonfirmasi karena sebagian besar I/Os segera dilayankan. Beban kerja yang melakukan I/O berurutan dalam jumlah besar, seperti pencadangan dan pemulihan, pemindaian tabel, pengurutan, pembuatan indeks, pemuatan massal, dan peniadaan file, dapat memperburuk permintaan I/O yang panjang.

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

Masalah kinerja I/O yang disebabkan oleh driver filter atau kueri yang tidak efisien

I/O lambat dapat disebabkan oleh kueri yang tidak ditulis secara efisien atau disetel dengan benar dengan indeks dan statistik. Faktor umum lain dalam latensi I/O adalah adanya antivirus atau program keamanan lainnya yang memindai file database. Perangkat lunak pemindaian ini mungkin meluas ke lapisan jaringan, yang menambahkan latensi jaringan, pada gilirannya secara tidak langsung memengaruhi latensi database. Meskipun skenario yang dijelaskan tentang I/O 15 detik lebih umum dengan komponen perangkat keras, penundaan I/O yang lebih singkat lebih sering diamati dengan kueri yang tidak optimal atau program antivirus yang salah dikonfigurasi.

Untuk informasi terperinci tentang cara mengatasi masalah ini, lihat Memecahkan masalah performa SQL Server lambat yang disebabkan oleh masalah I/O.

Untuk informasi tentang cara mengonfigurasi perlindungan antivirus di SQL Server, lihat Mengonfigurasi perangkat lunak antivirus untuk bekerja dengan SQL Server.

Menulis penembolokan di pengontrol penyimpanan

Transfer I/O yang tidak menggunakan cache dapat memakan waktu lebih lama pada drive mekanis karena kecepatan putar hard drive, waktu mekanis yang diperlukan untuk memindahkan kepala drive, dan faktor pembatas lainnya. Penginstalan SQL Server menargetkan sistem yang menyediakan pengontrol cache. Pengontrol ini menonaktifkan cache pada disk dan menyediakan cache media yang stabil untuk memenuhi persyaratan I/O SQL Server. Mereka menghindari masalah performa terkait dengan pencarian dan waktu tulis pada penyimpanan dengan memanfaatkan berbagai bentuk optimalisasi pengendali penyimpanan sementara.

Catatan

Beberapa vendor penyimpanan menggunakan memori persisten (PMEM) sebagai penyimpanan daripada cache, yang dapat meningkatkan performa keseluruhan. Untuk informasi selengkapnya, lihat Mengonfigurasi memori persisten (PMEM) untuk SQL Server di Windows dan Mengonfigurasi memori persisten (PMEM) untuk SQL Server di Linux.

Penggunaan pengontrol penyimpanan penulisan cache (juga disebut write-back cache) dapat meningkatkan performa SQL Server. Kontroler caching penulisan dan subsistem penyimpanan aman untuk SQL Server, jika dirancang untuk digunakan dalam lingkungan sistem manajemen basis data transaksional (DBMS) yang penting untuk data. Fitur desain ini harus mempertahankan data yang di-cache jika terjadi kegagalan sistem. Menggunakan catu daya eksternal yang tidak dapat diinterupsi (UPS) untuk mencapai perlindungan ini umumnya tidak cukup, karena mode kegagalan yang tidak terkait dengan daya dapat terjadi.

Important

SQL Server tergantung pada pengiriman yang dijamin ke media yang stabil untuk integritas dan pemulihan transaksional. Penembolokan tidak aman yang tidak memastikan keberlangsungan data di berbagai situasi kegagalan dapat merusak database, terlepas dari konsistensi penulisan log transaksi. Selalu verifikasi bahwa mekanisme penyimpanan sementara penulisan memberikan jaminan ketahanan penuh.

Pengontrol cache dan sub-sistem penyimpanan aman untuk digunakan oleh SQL Server. Sebagian besar platform server baru yang dibuat khusus yang menggabungkan pengontrol ini aman. Namun, Anda harus memeriksa dengan vendor perangkat keras Anda untuk memastikan bahwa subsistem penyimpanan diuji dan disetujui untuk digunakan di lingkungan sistem manajemen database hubungan transaksional (RDBMS) penting data.

Pedoman keamanan subsistem cache

Kontroler caching write-back dapat meningkatkan kinerja jika memenuhi persyaratan keamanan yang spesifik.

  • Sertakan cache yang didukung baterai atau memori non-volatil, seperti NVDIMM atau lampu kilat yang didukung super-kapasitor.
  • Disertifikasi oleh vendor untuk lingkungan basis data OLTP yang kritis terhadap data.
  • Memberikan perlindungan yang mencakup semua kondisi kegagalan, bukan hanya kehilangan daya.

Important

Jangan mengandalkan UPS eksternal saja. Kesalahan yang tidak terkait dengan daya, seperti bug firmware atau kegagalan perangkat keras, masih dapat menyebabkan kehilangan cache.

Pengelogan write-ahead

Pernyataan modifikasi data SQL Server menghasilkan penulisan halaman logis. Anda dapat menggambarkan aliran data penulisan ini sebagai menuju ke dua lokasi: log dan database itu sendiri. SQL Server menunda penulisan ke database untuk alasan performa melalui sistem buffer cache-nya. Sistem hanya menunda sementara penulisan ke log hingga waktu COMMIT. Ini tidak menyimpan cache penulisan ini dengan cara yang sama seperti penulisan data. Karena penulisan log untuk halaman tertentu selalu datang sebelum penulisan data halaman, log terkadang disebut sebagai "log tulis di depan" (WAL).

Protokol Write-ahead Logging (WAL)

Istilah protokol adalah cara yang sangat baik untuk menggambarkan WAL. WAL yang digunakan oleh SQL Server dikenal sebagai ARIES (Algoritma untuk Pemulihan dan Isolasi dengan Memanfaatkan Semantik). Untuk informasi selengkapnya, lihat Mengelola pemulihan database yang dipercepat.

Ini adalah serangkaian langkah implementasi tertentu dan ditentukan yang diperlukan untuk memastikan bahwa data disimpan dan ditukar dengan benar dan dapat dipulihkan ke keadaan yang diketahui jika terjadi kegagalan. Sama seperti jaringan yang berisi protokol yang ditentukan untuk bertukar data secara konsisten dan terlindungi, wal juga menjelaskan protokol untuk melindungi data. Semua versi SQL Server membuka file log dan data menggunakan fungsi Win32 CreateFile . Anggota dwFlagsAndAttributes menyertakan opsi FILE_FLAG_WRITE_THROUGH saat dibuka oleh SQL Server.

Bendera_Tulis_Langsung

SQL Server membuat file database menggunakan flagnya FILE_FLAG_WRITE_THROUGH. Opsi ini menginstruksikan sistem untuk menulis melalui cache perantara dan langsung masuk ke penyimpanan. Sistem masih dapat menyimpan operasi penulisan, tetapi tidak dapat mengosongkannya secara tertunda. Untuk informasi selengkapnya, lihat CreateFileA.

Opsi FILE_FLAG_WRITE_THROUGH memastikan bahwa ketika operasi tulis mengembalikan penyelesaian yang berhasil, data disimpan dengan benar dalam penyimpanan yang stabil. Fitur ini selaras dengan spesifikasi protokol Write-Ahead Logging (WAL) untuk memastikan integritas data. Banyak perangkat penyimpanan (NVMe, PCIe, SATA, ATA, SCSI, dan berbasis IDE) berisi cache onboard 512 KB, 1 MB, dan lebih besar. Cache penyimpanan biasanya mengandalkan kapasitor dan bukan solusi yang didukung baterai. Mekanisme caching ini tidak dapat menjamin penulisan berkelanjutan selama siklus daya atau saat titik kegagalan serupa. Mereka hanya menjamin penyelesaian operasi penulisan sektor. Karena ukuran perangkat penyimpanan terus bertambah, cache menjadi lebih besar, dan dapat mengekspos data dalam jumlah yang lebih besar selama kegagalan.

Untuk informasi selengkapnya tentang dukungan FUA oleh distribusi Linux dan efeknya pada SQL Server, lihat SQL Server di Linux: Internals Forced Unit Access (FUA).

Integritas transaksi dan pemulihan SQL Server

Integritas transaksional adalah salah satu konsep mendasar dari sistem database relasional. Transaksi adalah unit atomik pekerjaan yang sepenuhnya diterapkan atau dibatalkan sepenuhnya. Log transaksi write-ahead dari SQL Server adalah komponen vital dalam menjamin integritas transaksi.

Setiap sistem database relasional juga harus berurusan dengan konsep yang terkait erat dengan integritas transaksional, yaitu pemulihan dari kegagalan sistem yang tidak diencana. Beberapa efek dunia nyata yang tidak ideal dapat menyebabkan kegagalan ini. Pada banyak sistem manajemen database, kegagalan sistem dapat mengakibatkan proses pemulihan manual yang panjang yang diarahkan manusia.

Sebaliknya, mekanisme pemulihan SQL Server bersifat otomatis dan beroperasi tanpa intervensi manusia. Misalnya, SQL Server dapat mendukung aplikasi produksi misi penting, dan mengalami kegagalan sistem karena fluktuasi daya sesaat. Setelah pemulihan daya, perangkat keras server dimulai ulang, perangkat lunak jaringan dimuat dan diinisialisasi, dan mulai ulang SQL Server. Saat diinisialisasi SQL Server, SQL Server secara otomatis menjalankan proses pemulihannya berdasarkan data dalam log transaksi. Seluruh proses ini terjadi tanpa campur tangan manusia. Saat stasiun kerja klien dimulai ulang, pengguna menemukan semua data mereka ada, hingga transaksi terakhir yang mereka masukkan.

Integritas transaksi dan pemulihan otomatis di SQL Server merupakan kemampuan penghematan waktu dan tenaga kerja yang kuat. Jika pengontrol penyimpanan sementara penulisan tidak dirancang dengan benar untuk digunakan dalam lingkungan DBMS transaksional yang penting untuk data, pengontrol tersebut dapat mengganggu kemampuan pemulihan SQL Server, yang berpotensi merusak database. Masalah ini dapat terjadi jika pengontrol mencegat penulisan log transaksi SQL Server dan meng-buffer-nya dalam cache perangkat keras di papan pengontrol, tetapi tidak mempertahankan halaman tertulis ini selama kegagalan sistem.

Warning

Jika penulisan cache dibuang karena reset sistem, kerusakan database dapat terjadi bahkan jika UPS ada. Selalu pastikan cache tulis didukung oleh baterai atau teknologi yang setara untuk menjamin persistensi data.

Risiko cache tulis pada disk

Sebagian besar pengontrol cache perangkat penyimpanan melakukan cache penulisan. Anda tidak selalu dapat menonaktifkan fungsi penyimpanan sementara tulisan.

Bahkan jika server menggunakan UPS, perangkat tidak menjamin keamanan penulisan cache. Banyak jenis kegagalan sistem dapat terjadi yang tidak diatasi UPS. Misalnya, kesalahan paritas memori, perangkap sistem operasi (OS), atau kesalahan perangkat keras yang menyebabkan reset sistem dapat menghasilkan gangguan sistem yang tidak terkendali. Kegagalan memori dalam cache tulis perangkat keras juga dapat mengakibatkan hilangnya informasi log penting.

Kemungkinan masalah lain yang terkait dengan pengontrol penambahan cache penulisan dapat terjadi pada shutdown sistem. Tidak jarang siklus OS atau menghidupkan ulang sistem selama perubahan konfigurasi. Bahkan jika operator yang cermat mengikuti rekomendasi OS untuk menunggu sampai semua aktivitas penyimpanan berhenti sebelum memulai ulang sistem, penulisan cache masih dapat ada di pengontrol. Ctrl + Alt + Del Ketika kombinasi tombol ditekan, atau tombol reset perangkat keras ditekan, tulisan yang di-cache dapat dibuang, berpotensi merusak database.

Dimungkinkan untuk merancang cache tulis perangkat keras yang memperhitungkan semua kemungkinan penyebab membuang data cache kotor, yang membuatnya aman untuk digunakan oleh server database. Beberapa fitur desain ini termasuk mencegat sinyal bus RST (reset) untuk menghindari pengontrol cache yang tidak terkendali, cadangan baterai on-board, dan memori pencerminan atau pemeriksaan kesalahan dan koreksi (ECC). Tanyakan kepada vendor perangkat keras Anda untuk memastikan bahwa cache tulis menyertakan ini dan fitur lain yang diperlukan untuk menghindari kehilangan data.

Menggunakan cache penyimpanan dengan SQL Server

Sistem database adalah yang pertama dan paling bertanggung jawab atas penyimpanan dan pengambilan data yang akurat, bahkan jika terjadi kegagalan sistem yang tidak terduga.

Sistem harus menjamin atomitas dan durabilitas transaksi, sambil memperhitungkan eksekusi saat ini, beberapa transaksi, dan berbagai titik kegagalan. Properti ini sering disebut sebagai properti ACID (Atomitas, Konsistensi, Isolasi, dan Durabilitas).

Bagian ini membahas implikasi cache penyimpanan. Untuk informasi selengkapnya tentang cache dan diskusi mode kegagalan alternatif, lihat artikel berikut ini.

Tinjau juga konten yang diarsipkan berikut:

Konsep dalam kedua artikel ini tetap berlaku secara luas untuk versi SQL Server saat ini.

Solusi cache dengan dukungan baterai

Sistem pengontrol cache yang ditingkatkan menonaktifkan cache di disk dan menyediakan solusi cache yang didukung baterai fungsional. Cache-cache ini dapat mempertahankan data selama beberapa hari dan bahkan memungkinkan kartu cache untuk ditempatkan di komputer kedua. Setelah daya dipulihkan dengan benar, data yang belum tersimpan benar-benar dibersihkan sebelum akses data lebih lanjut diizinkan. Banyak dari sistem ini memungkinkan Anda untuk menetapkan persentase cache baca versus tulis untuk performa optimal. Beberapa sistem berisi area penyimpanan memori yang besar. Beberapa vendor perangkat keras menyediakan sistem caching drive dengan dukungan baterai berkualitas tinggi yang memiliki beberapa gigabyte cache. Sistem ini dapat secara signifikan meningkatkan performa database. Solusi caching yang didukung baterai memberikan durabilitas dan konsistensi data yang diharapkan oleh SQL Server.

Implementasi subsistem penyimpanan

Subsistem penyimpanan ada dalam banyak jenis. Dua contoh umum adalah RAID (redundant array of independent disks) dan SAN (area jaringan penyimpanan). Sistem ini biasanya menggunakan drive berbasis SCSI. Bagian berikut menjelaskan pertimbangan penyimpanan tingkat tinggi.

SCSI, SAS, dan NVMe

Perangkat penyimpanan SCSI, SAS, dan NVMe:

  • Biasanya dirancang untuk penggunaan tugas berat.
  • Biasanya ditargetkan pada implementasi berbasis server multiuser.
  • Biasanya memiliki waktu rata-rata yang lebih baik untuk tingkat kegagalan daripada implementasi lainnya.
  • Mengandung heuristik canggih untuk membantu memprediksi kegagalan yang akan datang.

Catatan

SQL Server mendukung komponen teknologi Internet Small Computer System Interface (iSCSI) yang memenuhi persyaratan Program Kompatibilitas Perangkat Keras Windows. Meskipun SQL Server tidak berinteraksi langsung dengan iSCSI, SQL Server beroperasi dengan mulus karena Windows menyajikan penyimpanan iSCSI sebagai drive standar. SQL Server kemudian dapat membaca dari, dan menulis ke, penyimpanan tingkat blok jarak jauh di seluruh jaringan IP. Karena iSCSI tergantung pada jaringan, Anda dapat mengalami penundaan atau hambatan. Pastikan performa caching server optimal, dan latensi diminimalkan. Untuk informasi selengkapnya, lihat Batas Skalabilitas Server Target iSCSI.

Non-SCSI

Implementasi drive lainnya, seperti IDE, ATA, dan SATA:

  • Biasanya dirancang untuk penggunaan ringan hingga sedang.
  • Biasanya ditargetkan pada aplikasi pengguna tunggal.

Pengontrol berbasis desktop non-SCSI memerlukan lebih banyak bandwidth prosesor utama (CPU) dan sering dibatasi oleh satu perintah aktif. Misalnya, ketika drive non-SCSI mengoreksi blok rusak, drive tersebut mengharuskan perintah host untuk menunggu. Bus ATA menyajikan contoh lain: bus ATA mendukung dua perangkat, tetapi hanya satu perintah yang dapat aktif. Batasan ini membuat satu drive menganggur ketika drive lainnya menangani perintah yang tertunda. Sistem RAID yang dibangun di atas teknologi desktop dapat mengalami gejala ini dan sangat dipengaruhi oleh pemberi respons paling lambat. Kecuali sistem ini menggunakan desain tingkat lanjut, performanya tidak seefisien performa sistem berbasis SCSI.

Pertimbangan penyimpanan

Drive atau array yang berbasis desktop dapat menjadi solusi berbiaya rendah untuk beberapa situasi. Misalnya, jika Anda menyiapkan database baca-saja untuk pelaporan, Anda tidak menemukan banyak faktor performa database OLTP saat penembolokan drive dinonaktifkan.

Ukuran perangkat penyimpanan terus meningkat. Drive berbiaya rendah dengan kapasitas tinggi dapat menarik. Tetapi saat Anda mengonfigurasi drive untuk SQL Server dan kebutuhan waktu respons bisnis Anda, pertimbangkan masalah berikut dengan cermat:

  • Desain jalur akses
  • Persyaratan untuk menonaktifkan cache pada disk

Hard drive mekanik

Penggerak mekanis menggunakan piring magnetik berputar untuk menyimpan data. Mereka tersedia dalam beberapa kapasitas dan faktor bentuk, seperti IDE, SATA, SCSI, dan Serial Attached SCSI (SAS). Beberapa drive SATA termasuk konstruksi prediksi kegagalan. Drive SCSI dirancang untuk siklus tugas yang lebih berat dan penurunan tingkat kegagalan.

SISTEM berbasis IDE dan ATA dapat menunda perintah host ketika mereka melakukan aktivitas seperti penyesuaian blok yang buruk, yang menyebabkan periode aktivitas I/O yang terhenti.

Manfaat SAS termasuk antrean lanjutan hingga 256 tingkat, serta antrean kepala dan antar-pesan di luar urutan. Backplane SAS dirancang dengan cara yang memungkinkan penggunaan drive SAS dan SATA dalam sistem yang sama.

Penginstalan SQL Server Anda tergantung pada kemampuan pengontrol untuk menonaktifkan cache pada disk dan untuk menyediakan cache I/O yang stabil. Pencatatan data yang tidak berurutan ke berbagai drive tidak menjadi masalah bagi SQL Server selama pengontrol menyediakan kemampuan penembolokan media yang benar-benar stabil. Kompleksitas desain pengontrol meningkat dengan teknik keamanan data tingkat lanjut seperti pencerminan.

Penyimpanan solid-state

Penyimpanan solid-state memiliki keunggulan dibandingkan hard drive mekanis (berputar), tetapi rentan terhadap banyak pola kegagalan yang sama dengan media berputar. Anda dapat menyambungkan penyimpanan solid-state ke server Anda menggunakan berbagai antarmuka, termasuk NVM Express (NVMe), PCI Express (PCIe), dan SATA. Perlakukan media solid-state sama seperti Anda memperlakukan media berputar, dan pastikan perlindungan yang sesuai diterapkan untuk kegagalan daya, seperti pengontrol cache yang didukung baterai.

Masalah umum yang disebabkan oleh kesalahan daya meliputi:

  • Kerusakan bit: Catatan menunjukkan kesalahan bit secara acak.
  • Penulisan terbang: Catatan yang terbentuk dengan baik berakhir di tempat yang salah.
  • Shorn menulis: Operasi sebagian dilakukan pada tingkat yang lebih rendah dari ukuran sektor yang diharapkan.
  • Kerusakan metadata: Metadata di lapisan terjemahan flash (FTL) rusak.
  • Perangkat tidak responsif: Perangkat tidak berfungsi sama sekali, atau sebagian besar tidak berfungsi.
  • Tidak dapat diserialisasi: Status akhir penyimpanan tidak dihasilkan dari urutan operasi yang dapat diserialisasikan.

512e

Sebagian besar perangkat penyimpanan solid-state melaporkan ukuran sektor 512 byte tetapi menggunakan halaman 4 KB di dalam blok penghapusan 1 MB. Menggunakan sektor selaras 512 byte untuk perangkat log SQL Server dapat menghasilkan lebih banyak aktivitas baca/ubah/tulis (RMW), yang dapat berkontribusi pada performa dan keausan drive yang lebih lambat.

Rekomendasi: Pastikan pengontrol cache mengetahui ukuran halaman yang benar dari perangkat penyimpanan, dan dapat menyelaraskan penulisan fisik data dengan infrastruktur penyimpanan solid-state secara tepat.

0xFFFFFFFF

Drive yang baru diformat biasanya menyimpan semua nol. Blok yang terhapus dari perangkat semikonduktor adalah semua 1, membuat pembacaan mentah dari blok yang terhapus semua karakter 0xFF. Namun, tidak biasa bagi pengguna untuk membaca blok yang dihapus selama operasi I/O normal.

Pencetakan pola

Teknik yang digunakan di masa lalu adalah menulis pola yang diketahui ke seluruh drive. Kemudian ketika aktivitas database dilakukan terhadap drive yang sama, perilaku tidak benar (pembacaan yang usang, penulisan yang hilang, atau pembacaan offset yang salah) dapat dideteksi ketika pola tersebut muncul secara tak terduga.

Teknik ini tidak berfungsi dengan baik pada penyimpanan solid-state. Penghapusan dan aktivitas Baca-Modifikasi-Tulis (RMW) pada penulisan mengganggu pola. Aktivitas pengumpulan sampah penyimpanan solid-state (GC), tingkat keausan, blok daftar proporsional/set-aside, dan pengoptimalan lainnya cenderung menyebabkan penulisan memperoleh lokasi fisik yang berbeda, tidak seperti penggunaan kembali sektor media berputar.

Perangkat tegar

Firmware yang digunakan dalam penyimpanan solid-state cenderung lebih kompleks jika dibandingkan dengan media bergerak tradisional. Banyak drive menggunakan beberapa inti pemrosesan untuk menangani permintaan masuk dan aktivitas pengumpulan sampah. Pastikan Anda selalu memperbarui firmware perangkat solid-state Anda untuk menghindari masalah yang diketahui.

Membaca kerusakan data dan tingkat keausan

Pendekatan pengumpulan sampah umum (GC) untuk penyimpanan solid-state membantu mencegah kerusakan data baca berulang. Ketika membaca sel yang sama berulang kali, ada kemungkinan aktivitas elektron dapat bocor dan menyebabkan kerusakan pada sel-sel tetangga. Penyimpanan solid-state melindungi data dengan berbagai tingkat kode koreksi kesalahan (ECC) dan mekanisme lainnya.

Salah satu mekanisme tersebut berkaitan dengan perataan keausan. Penyimpanan solid-state melacak aktivitas baca dan tulis pada perangkat penyimpanan. Pengumpulan sampah memori dapat menentukan hot spot atau lokasi yang aus lebih cepat daripada lokasi lainnya. Misalnya, GC menentukan bahwa blok berada dalam status baca-saja dan perlu dipindahkan. Gerakan ini umumnya menuju ke blok dengan lebih banyak keausan, sehingga blok asli dapat digunakan untuk tujuan penulisan. Proses ini membantu menyeimbangkan keausan pada drive, tetapi memindahkan data baca-saja ke lokasi yang memiliki lebih banyak keausan dan secara matematis meningkatkan peluang kegagalan, bahkan jika sedikit.

Efek samping lain dari penyeimbangan keausan dapat terjadi dengan SQL Server. Misalkan Anda menjalankan DBCC CHECKDB, dan melaporkan kesalahan. Jika Anda menjalankannya untuk kedua kalinya, ada kemungkinan kecil DBCC CHECKDB melaporkan kesalahan tambahan atau pola kesalahan yang berbeda, karena aktivitas GC mungkin membuat perubahan pada penyimpanan solid-state di antara DBCC CHECKDB eksekusi.

Kesalahan OS 665 dan defragmentasi

Media berputar perlu menjaga blok saling berdekatan untuk mengurangi pergerakan kepala drive dan meningkatkan kinerja. Penyimpanan berbasis solid-state tidak memiliki kepala fisik, yang menghilangkan waktu pencarian. Banyak perangkat solid-state dirancang untuk memungkinkan operasi paralel pada blok yang berbeda. Defragmentasi media berbasis solid-state tidak diperlukan. Aktivitas serial adalah pola I/O terbaik untuk memaksimalkan throughput I/O pada perangkat penyimpanan solid-state.

Catatan

Penyimpanan solid-state mendapatkan manfaat dari fitur trim, perintah tingkat sistem operasi (OS) yang menghapus blok yang dianggap tidak lagi digunakan. Di Windows, gunakan alat Optimize Drives untuk mengatur jadwal mingguan untuk mengoptimalkan drive.

Rekomendasi:

  • Gunakan pengontrol yang sesuai dan didukung baterai yang dirancang untuk mengoptimalkan aktivitas tulis. Pilihan ini dapat memperbaiki performa, mengurangi keausan drive, dan menurunkan tingkat fragmentasi fisik.

  • Pertimbangkan untuk menggunakan sistem file ReFS untuk menghindari batasan atribut NTFS.

  • Pastikan pengaturan pertumbuhan file sesuai.

Untuk informasi selengkapnya tentang pemecahan masalah kesalahan OS 665 karena berkaitan dengan fragmentasi, lihat Kesalahan OS 665 dan 1450 dilaporkan untuk file SQL Server.

Kompresi

Selama drive mempertahankan niat media yang stabil, kompresi dapat memperpanjang masa pakai drive dan dapat berdampak positif pada performa. Namun, beberapa firmware mungkin sudah mengompresi data secara tidak terlihat. Ingatlah untuk menguji skenario penyimpanan baru sebelum menyebarkannya ke lingkungan produksi Anda.

Ringkasan

  • Pertahankan prosedur dan proses pencadangan dan pemulihan bencana yang tepat.
  • Selalu perbarui firmware Anda.
  • Dengarkan baik-baik panduan produsen perangkat keras Anda.

Konfigurasi cache drive

Untuk menggunakan drive dengan SQL Server, nonaktifkan cache drive. Secara default, cache drive diaktifkan. Di Windows Server, gunakan tab Properti Disk>Perangkat Keras>Kebijakan untuk menonaktifkan caching tulisan di tingkat OS.

Jangan mengandalkan pengaturan tingkat OS saja. Beberapa drive mengabaikan pengaturan Windows dan memerlukan utilitas atau pengaturan firmware yang disediakan produsen untuk menonaktifkan penyimpanan sementara penulisan. Konfirmasi melalui perangkat vendor bahwa caching penulisan benar-benar dinonaktifkan.

Catatan

Bahkan dengan penyimpanan cache penulisan dinonaktifkan, firmware drive mungkin memperkenalkan pengoptimalan internal yang menyebabkan penundaan perintah pembuangan. Selalu konfirmasikan status cache yang efektif sebelum penyebaran dengan menggunakan alat pengujian seperti SQLIOSim.

Pertimbangan mengenai cache dan SQLIOSim

Untuk mengonfirmasi jaminan durabilitas transaksional, validasi subsistem I/O Anda dengan menggunakan SQLIOSim sebelum pindah ke produksi. Utilitas ini mensimulasikan aktivitas baca dan tulis asinkron berat ke perangkat data dan perangkat log yang disimulasikan. Untuk informasi selengkapnya, lihat Menggunakan utilitas SQLIOSim untuk mensimulasikan aktivitas SQL Server pada subsistem disk.

Catatan

Pastikan bahwa mekanisme caching alternatif dapat menangani berbagai jenis kegagalan dengan tepat.

Banyak produsen PC memesan drive dengan fitur cache penulisan yang dinonaktifkan. Namun, pengujian menunjukkan bahwa kondisi ini mungkin tidak selalu terjadi, jadi Anda harus selalu mengujinya sepenuhnya. Jika Anda memiliki pertanyaan tentang status cache perangkat penyimpanan Anda, hubungi produsen dan dapatkan alat yang tepat untuk menonaktifkan operasi cache tulis. Di media penyimpanan yang lebih lama, Anda mungkin juga memerlukan pengaturan jumper.

SQL Server memerlukan sistem untuk mendukung pengiriman terjamin ke media yang stabil, seperti yang diuraikan di bawah Persyaratan Program Keandalan I/O SQL Server. Untuk informasi selengkapnya tentang persyaratan input/output untuk Mesin Database SQL Server, lihat Persyaratan Input/Output Disk untuk Mesin Database SQL Server (I/O).

Risiko cache penulisan yang tidak tepat

Saat Anda mengaktifkan penembolokan tulis tanpa perlindungan yang tepat, beberapa subsistem penyimpanan mengakui operasi penulisan sebagai selesai sebelum data tertulis dengan aman ke media penyimpanan yang tahan lama. Jika terjadi kehilangan daya atau kegagalan sistem, kondisi ini dapat mengakibatkan:

  • Kehilangan data, di mana transaksi yang dilakukan tidak pernah disimpan.
  • Kerusakan database karena kegagalan jaminan urutan penulisan.

Important

Nonaktifkan cache tulis untuk data SQL Server dan drive log kecuali Anda memastikan melalui dokumentasi vendor perangkat keras bahwa:

  • Cache didukung baterai atau menggunakan penyimpanan flash persisten.
  • Drive menjamin ketahanan selama pemadaman listrik dan crash sistem.

Perangkat UPS eksternal tidak cukup karena mungkin tidak melindungi dari semua mode kegagalan, seperti kesalahan firmware pengontrol.

Konten terkait

  • Last updated on