MSSQLSERVER_833

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Managed Instance

Detail

Atribut Value
Nama Produk SQL Server
ID Peristiwa 833
Sumber Kejadian MSSQLSERVER
Komponen SQLEngine
Nama Simbolis BUF_LONG_IO
Teks Pesan SQL Server telah menemukan %d kemunculan permintaan I/O membutuhkan waktu lebih lama dari %d detik untuk diselesaikan pada file [%ls] dalam database [%ls] (%d). Handel file OS adalah 0x%p. Offset I/O panjang terbaru adalah: %#016I64x.

Penjelasan

Pesan ini menunjukkan bahwa SQL Server telah mengeluarkan permintaan baca atau tulis dari disk, dan bahwa permintaan telah memakan waktu lebih dari 15 detik untuk dikembalikan. SQL Server melaporkan kesalahan ini dan menunjukkan masalah dengan subsistem I/O. Sistem manajemen database (DBMS), seperti SQL Server, bergantung pada keandalan waktu operasi input dan output file (I/O). Salah satu item berikut dapat menyebabkan operasi I/O macet atau macet dan berdampak buruk pada respons dan performa SQL Server:

  • Perangkat keras rusak
  • Perangkat keras yang salah dikonfigurasi
  • Pengaturan firmware
  • Memfilter driver
  • Kompresi
  • Bugs
  • Kondisi lain di jalur I/O

Masalah I/O ini dapat menyebabkan perilaku berikut terjadi:

  • Memblokir.
  • Kaitkan ketidakcocokan dan waktu habis.
  • Waktu respons lambat.
  • Peregangan batas sumber daya.
  • Anda mungkin juga melihat gejala lain yang terkait dengan pesan ini, seperti:
    • Waktu tunggu tinggi untuk pageiolatch menunggu.
    • Peringatan atau kesalahan dalam log peristiwa sistem.
    • Indikasi masalah latensi disk di penghitung pemantauan sistem.

Ketika operasi I/O telah tertunda selama 15 detik atau lebih, SQL Server melakukan langkah-langkah berikut:

  1. Mendeteksi bahwa operasi telah tertunda.

  2. Menulis pesan informasi ke log kesalahan SQL Server seperti yang diuraikan di bagian Detail.

    Penjelasan untuk bagian yang berbeda dari pesan informasi ini diberikan dalam tabel berikut:

Teks pesan Deskripsi
<Kemunculan angka> Jumlah permintaan I/O yang tidak menyelesaikan operasi baca atau tulis dalam waktu kurang dari 15 detik.
Informasi file Nama file lengkap, nama database, dan nomor identifikasi database (DBID).
Handel Handel sistem operasi file. Anda dapat menggunakan handel sistem operasi dengan debugger atau utilitas lain untuk membantu melacak permintaan paket permintaan I/O (IRP).
Offset Offset operasi I/O terakhir yang macet atau operasi I/O terakhir yang terhenti. Anda dapat menggunakan offset dengan debugger atau utilitas lain untuk membantu melacak permintaan IRP.

Catatan:
Ketika pesan informasi ditulis ke log kesalahan SQL Server, operasi I/O mungkin tidak lagi macet atau macet.

Kemungkinan penyebab

Pesan informasi menunjukkan bahwa beban saat ini mungkin mengalami salah satu kondisi berikut:

  • Beban kerja melebihi kemampuan jalur I/O baik karena kesalahan konfigurasi subsistem I/O (SAN, NAS, dan terpasang langsung) atau karena kapasitas perangkat keras telah tercapai.
  • Beban kerja melebihi kemampuan sistem saat ini, seperti I/O, CPU, dan HBA.
  • Jalur I/O memiliki perangkat lunak yang tidak berfungsi. Ini bisa menjadi firmware atau masalah driver.
  • Jalur I/O memiliki komponen perangkat keras yang tidak berfungsi.
  • Masalah performa di tingkat sistem operasi.
  • Memfilter intervensi driver dalam proses I/O atau jalur penyimpanan file database. Misalnya, program antivirus.

SQL Server mencatat waktu memulai permintaan I/O dan merekam waktu I/O selesai. Jika perbedaannya adalah 15 detik atau lebih, kondisi ini terdeteksi. Ini juga berarti bahwa SQL Server bukan penyebab kondisi I/O yang tertunda yang dijelaskan pesan dan laporan ini. Kondisi ini dikenal sebagai I/O yang terhenti. Sebagian besar permintaan disk terjadi dalam kecepatan khas disk. Kecepatan disk umum ini sering dikenal sebagai waktu pencarian disk. Waktu pencarian disk untuk sebagian besar disk standar terjadi dalam 10 milidetik atau kurang. Oleh karena itu, 15 detik adalah waktu yang lama bagi jalur I/O sistem untuk kembali ke SQL Server. Untuk detail selengkapnya, lihat bagian Informasi Selengkapnya.

Tindakan pengguna

Pecahkan masalah kesalahan ini dengan melakukan langkah-langkah berikut:

  1. Periksa log peristiwa sistem untuk pesan kesalahan terkait perangkat keras.
  2. Periksa log khusus perangkat keras jika tersedia. Gunakan metode dan teknik yang diperlukan untuk menentukan penyebab keterlambatan sistem operasi, driver, atau perangkat keras I/O.
  3. Perbarui semua driver perangkat dan firmware atau lakukan diagnostik lain yang terkait dengan subsistem I/O Anda.
  4. Akses disk dapat diperlambat oleh driver filter, misalnya, program antivirus. Untuk meningkatkan kecepatan akses, kecualikan file data SQL Server yang ditentukan dalam pesan kesalahan dari pemindaian virus aktif. Untuk informasi selengkapnya, lihat Cara memilih perangkat lunak antivirus untuk dijalankan di komputer yang menjalankan SQL Server (microsoft.com).
    • Gunakan utilitas baris perintah fltmc.exe untuk mengkueri semua driver filter yang diinstal pada sistem dan untuk memahami fungsi yang dilakukannya pada jalur penyimpanan ke file database.
  5. Gunakan Monitor Performa untuk memeriksa penghitung berikut:
    • Disk Rata-rata Detik/Transfer
    • Panjang Antrean Disk Rata-rata
    • Panjang Antrean Disk Saat Ini
  6. Anda juga dapat menggunakan fasilitas seperti pengelogan Storport ETW untuk mengukur latensi permintaan yang dibuat ke unit disk. Kit pemecahan masalah I/O disk serupa lainnya tersedia sebagai profil bawaan Perekam Performa Windows.
  7. Pantau sys.dm_io_virtual_file_stats dan pilih tingkat penyimpanan dan IOPS yang sesuai untuk throughput penyimpanan Anda.

Untuk panduan terpandu untuk mendiagnosis dan memecahkan masalah performa SQL Server yang terjadi karena masalah I/O, lihat Memecahkan masalah performa SQL Server lambat yang disebabkan oleh masalah I/O.

Informasi selengkapnya

I/O Macet dan I/O Terhenti

I/O Macet

I/O macet didefinisikan sebagai permintaan I/O yang tidak selesai. Sering kali, I/O yang macet menunjukkan IRP yang macet. Untuk mengatasi kondisi I/O yang macet, Anda biasanya harus menghidupkan ulang komputer atau melakukan tindakan serupa. Kondisi I/O yang macet biasanya menunjukkan salah satu masalah berikut:

  • Perangkat keras rusak.
  • Bug dalam komponen jalur I/O.

I/O terhenti

I/O yang terhenti didefinisikan sebagai permintaan I/O yang selesai, atau yang membutuhkan waktu yang berlebihan untuk diselesaikan. Perilaku I/O yang terhenti biasanya terjadi karena salah satu alasan berikut:

  • Konfigurasi perangkat keras.
  • Pengaturan firmware.
  • Masalah driver filter yang memerlukan bantuan dari vendor perangkat keras atau perangkat lunak untuk melacak dan menyelesaikannya.

SQL Server terhenti I/O dan terjebak perekaman dan pelaporan I/O

Dukungan SQL Server menangani banyak kasus setiap tahun yang melibatkan masalah I/O yang macet atau macet. Masalah I/O ini muncul dengan cara yang berbeda. Masalah I/O adalah beberapa yang paling sulit untuk didiagnosis dan di-debug, dan memerlukan waktu dan sumber daya yang signifikan untuk penelusuran kesalahan dari Microsoft dan pelanggan. Pelaporan dan perekaman permintaan I/O dirancang berdasarkan per file. Deteksi dan pelaporan permintaan I/O yang macet dan macet adalah dua tindakan terpisah.

Rekaman

Ada dua saat ketika tindakan rekaman terjadi di SQL Server. Yang pertama adalah ketika operasi I/O selesai. Momen kedua adalah ketika penulis malas berjalan. Ketika penulis malas berjalan, ia memeriksa semua data yang tertunda dan permintaan I/O file log yang tertunda. Jika permintaan I/O melebihi ambang batas 15 detik, operasi rekaman akan terjadi.

Pelaporan

Pelaporan terjadi dalam interval yang terpisah lima menit atau lebih. Pelaporan terjadi ketika permintaan I/O berikutnya dibuat pada file. Jika tindakan rekaman telah terjadi dan lima menit atau lebih telah berlalu sejak laporan terakhir terjadi, pesan informasi yang disebutkan di bagian Detail ditulis ke log kesalahan SQL Server.

Ambang batas 15 detik tidak dapat disesuaikan. Namun, Anda dapat menonaktifkan deteksi I/O yang macet atau macet dengan menggunakan bendera pelacakan 830, meskipun kami tidak menyarankan untuk melakukan ini.

Anda dapat menonaktifkan deteksi untuk I/O yang macet dan macet dengan menggunakan bendera pelacakan 830. Untuk mengaktifkan bendera ini setiap kali SQL Server dimulai, gunakan parameter startup -T830. Untuk menonaktifkan deteksi instans SQL Server yang sedang berjalan, gunakan pernyataan berikut:

    dbcc traceon(830, -1)

Pengaturan ini hanya efektif untuk masa pakai proses SQL Server.

Catatan

Permintaan I/O yang menjadi macet atau macet hanya dilaporkan satu kali. Misalnya, jika pesan melaporkan bahwa 10 permintaan I/O terhenti, 10 laporan tersebut tidak akan terjadi lagi. Jika pesan berikutnya melaporkan bahwa 15 permintaan I/O terhenti, itu berarti bahwa 15 permintaan I/O baru telah terhenti.

Melacak paket permintaan I/O (IRP)

SQL Server menggunakan panggilan Microsoft Windows API standar untuk membaca dan menulis data. Misalnya, SQL Server menggunakan fungsi berikut:

  • WriteFile
  • ReadFile
  • WriteFileScatter
  • ReadFileGather

Permintaan baca atau tulis ditangani oleh Windows sebagai paket permintaan I/O (IRP). Untuk menentukan status IRP, gunakan kedua fitur berikut::

Kami menyarankan agar Anda memeriksa pembaruan yang tersedia untuk item berikut:

  • The BIOS
  • Firmware
  • Komponen jalur I/O lainnya

Hubungi vendor perangkat keras Anda sebelum Anda melakukan tindakan penelusuran kesalahan tambahan. Sesi debug kemungkinan akan melibatkan komponen driver, firmware, atau driver filter pihak ketiga.

Performa sistem dan tindakan rencana kueri

Secara keseluruhan, performa sistem dapat memainkan peran utama dalam pemrosesan I/O. Anda harus mempertimbangkan kesehatan umum sistem saat menyelidiki laporan operasi I/O yang macet atau macet. Beban yang berlebihan dapat menyebabkan sistem keseluruhan menjadi lambat, termasuk pemrosesan I/O. Perilaku sistem ketika masalah terjadi dapat menjadi faktor kunci dalam menentukan akar penyebab masalah. Misalnya, jika penggunaan CPU meningkat atau tetap tinggi saat masalah terjadi, mungkin menunjukkan bahwa proses sistem menggunakan begitu banyak CPU sehingga proses lain terpengaruh.

Penghitung kinerja

Untuk memantau performa I/O, periksa penghitung kinerja berikut untuk informasi jalur I/O tertentu:

  • Disk Rata-rata Detik/Transfer
  • Panjang Antrean Disk Rata-rata
  • Panjang Antrean Disk Saat Ini

Misalnya, waktu Rata-rata Disk Sec/Transfer di komputer yang menjalankan SQL Server biasanya kurang dari 15 milidetik. Jika nilai Average Disk Sec/Transfer naik, itu menunjukkan bahwa subsistem I/O tidak secara optimal mengikuti permintaan I/O.

Berhati-hatilah saat menggunakan penghitung kinerja karena SQL Server memanfaatkan sepenuhnya kemampuan I/O asinkron yang sangat mendorong panjang antrean disk. Oleh karena itu, panjang antrean disk yang lebih panjang saja tidak menunjukkan masalah.

Di Windows System Monitor, Anda dapat meninjau penghitung "Disk Fisik: Byte Disk/detik" untuk setiap disk yang terpengaruh dan membandingkan laju aktivitas dengan penghitung "Proses: Byte Data IO/Detik" dan "Proses: IO Byte Lain/detik" untuk setiap proses. Anda melakukan ini untuk mengidentifikasi apakah serangkaian proses tertentu menghasilkan permintaan I/O yang berlebihan. Berbagai penghitung terkait I/O lainnya dalam objek Proses mengungkapkan informasi yang lebih terperinci. Jika Anda menentukan bahwa instans SQL Server bertanggung jawab atas beban I/O yang berlebihan di server, lihat bagian berikutnya tentang Indeks dan Paralelisme. Untuk diskusi terperinci tentang mendeteksi dan mengatasi hambatan I/O, lihat Memecahkan masalah performa SQL Server lambat yang disebabkan oleh masalah I/O.

Indeks dan paralelisme

Sering kali, ledakan I/O terjadi karena indeks hilang. Perilaku ini dapat sangat mendorong jalur I/O. Pass yang menggunakan Index Turning Wizard (ITW) dapat membantu mengatasi tekanan I/O pada sistem. Jika kueri mendapat manfaat dari indeks alih-alih pemindaian tabel, atau mungkin jika menggunakan pengurutan atau hash, sistem bisa mendapatkan keuntungan berikut:

  • Pengurangan dilakukan dalam I/O fisik yang diperlukan untuk menyelesaikan tindakan yang secara langsung membuat manfaat performa untuk kueri.
  • Lebih sedikit halaman dalam cache data harus diserahkan. Oleh karena itu, halaman yang berada di cache data tetap relevan dengan kueri aktif.
  • Pengurutan dan hash digunakan karena indeks mungkin hilang atau karena statistik sudah kedaluarsa. Anda dapat mengurangi penggunaan tempdb dan ketidakcocokan dengan menambahkan satu atau beberapa indeks.
  • Pengurangan dilakukan dalam sumber daya, operasi paralel, atau keduanya. Karena SQL Server tidak menjamin eksekusi kueri paralel, dan beban pada sistem dipertimbangkan, yang terbaik adalah mengoptimalkan semua kueri untuk eksekusi serial. Untuk mengoptimalkan kueri, buka Penganalisis Kueri dan atur nilai sp_configure dari tingkat maksimum opsi paralelisme ke 1. Jika semua kueri disetel untuk segera berjalan sebagai operasi serial, eksekusi paralel sering kali hanyalah hasil yang lebih baik. Namun, eksekusi paralel sering dipilih karena jumlah datanya besar. Untuk indeks yang hilang, pengurutan besar mungkin harus terjadi. Beberapa pekerja yang melakukan operasi pengurutan akan membuat respons yang lebih cepat. Namun, tindakan ini dapat secara dramatis meningkatkan tekanan pada sistem. Permintaan baca besar dari banyak pekerja dapat menyebabkan ledakan I/O bersama dengan peningkatan penggunaan CPU. Kueri sering dapat disetel untuk berjalan lebih cepat dan menggunakan lebih sedikit sumber daya jika indeks ditambahkan atau jika tindakan penyetelan lain terjadi.

Contoh praktis dari Dukungan SQL Server

Contoh berikut telah ditangani oleh Dukungan SQL Server dan Dukungan Eskalasi Windows. Contoh-contoh ini dimaksudkan untuk memberikan bingkai referensi dan membantu menetapkan harapan Anda tentang situasi I/O yang macet dan macet. Mereka juga menyediakan kerangka kerja untuk memahami bagaimana sistem dapat terpengaruh atau dapat merespons. Tidak ada perangkat keras atau set driver tertentu yang menimbulkan risiko tertentu atau peningkatan risiko dibandingkan yang lain. Semua sistem sama dalam hal ini.

Contoh 1: Penulisan log yang macet selama 45 detik

Upaya untuk menulis file log SQL Server secara berkala macet selama sekitar 45 detik. Penulisan log tidak selesai tepat waktu. Perilaku ini menciptakan kondisi pemblokiran yang menyebabkan waktu habis klien 30 detik.

Aplikasi mengirimkan penerapan ke SQL Server, dan penerapan macet saat penulisan log tertunda. Perilaku ini menyebabkan kueri terus menahan kunci dan memblokir permintaan masuk dari klien lain. Kemudian, klien lain mulai kehabisan waktu. Ini memperparah masalah karena aplikasi tidak mengembalikan transaksi terbuka saat waktu kueri habis terjadi. Ini menciptakan ratusan transaksi terbuka yang memegang kunci. Oleh karena itu, situasi pemblokiran yang parah terjadi.

Untuk informasi selengkapnya tentang penanganan dan pemblokiran transaksi, lihat artikel Pangkalan Pengetahuan Microsoft berikut ini: 224453 Memahami dan mengatasi masalah pemblokiran SQL Server

Layanan aplikasi situs web dengan menggunakan pengumpulan koneksi. Ketika lebih banyak koneksi diblokir, situs web membuat lebih banyak koneksi. Koneksi ini menjadi diblokir, dan siklus berlanjut.

Penulisan log membutuhkan waktu sekitar 45 detik untuk diselesaikan. Namun, pada saat ini, ratusan koneksi dicadangkan. Masalah pemblokiran menyebabkan beberapa menit waktu pemulihan untuk SQL Server dan aplikasi. Dikombinasikan dengan masalah aplikasi, kondisi I/O yang terhenti memiliki efek yang sangat negatif pada sistem.

Resolusi

Masalah ini dilacak ke permintaan I/O yang macet di driver Adaptor Bus Host (HBA). Komputer memiliki beberapa kartu HBA dengan dukungan failover. Ketika satu HBA berada di belakang atau tidak berkomunikasi dengan Storage Area Network (SAN), nilai batas waktu "coba lagi sebelum failover" dikonfigurasi menjadi 45 detik. Ketika waktu habis melebihi, permintaan I/O dirutekan ke HBA kedua. HBA kedua menangani permintaan dan dengan cepat selesai. Untuk membantu mencegah kondisi kios seperti itu, produsen perangkat keras merekomendasikan pengaturan "coba lagi sebelum failover" lima detik.

Contoh 2: Memfilter intervensi driver

Banyak program perangkat lunak antivirus dan produk cadangan menggunakan driver filter I/O. Driver filter I/O ini menjadi bagian dari tumpukan permintaan I/O dan memiliki akses ke permintaan IRP. Layanan Dukungan Produk Microsoft telah melihat berbagai masalah dari bug yang membuat kondisi I/O yang macet atau kondisi I/O yang macet dalam implementasi driver filter.

Salah satu kondisi tersebut adalah driver filter untuk pemrosesan cadangan yang memungkinkan pencadangan file yang terbuka ketika pencadangan terjadi. Administrator sistem telah menyertakan direktori file data SQL Server dalam pilihan cadangan file. Ketika pencadangan terjadi, cadangan mencoba mengumpulkan gambar file yang benar pada saat pencadangan dimulai. Melakukan ini menunda permintaan I/O. Permintaan I/O hanya diizinkan untuk menyelesaikan satu per satu saat perangkat lunak menanganinya.

Ketika pencadangan dimulai, performa SQL Server turun secara dramatis karena I/Os SQL Server dipaksa untuk menyelesaikan satu per satu. Logika satu per satu sedemikian rupa sehingga operasi I/O tidak dapat dilakukan secara asinkron, yang menggabungkan masalah. Oleh karena itu, ketika SQL Server mengharapkan untuk memposting permintaan I/O dan melanjutkan, pekerja terjebak dalam panggilan baca atau tulis hingga permintaan I/O selesai. Tindakan driver filter secara efektif menonaktifkan tugas pemrosesan seperti SQL Server read-ahead. Selain itu, bug lain dalam driver filter meninggalkan tindakan satu per satu dalam proses, bahkan ketika pencadangan selesai. Satu-satunya cara untuk memulihkan performa SQL Server adalah dengan menghidupkan ulang SQL Server sehingga handel file dirilis dan diberikan kembali tanpa interaksi driver filter.

Resolusi

Untuk mengatasi masalah ini, file data SQL Server dihapus dari proses pencadangan file. Produsen perangkat lunak telah memperbaiki masalah yang meninggalkan file dalam mode "satu per satu".

Contoh 3: Kesalahan tersembunyi

Banyak sistem kelas atas memiliki jalur I/O multisaluran untuk menangani penyeimbangan beban atau aktivitas serupa. Dukungan Produk Microsoft telah menemukan masalah dengan perangkat lunak penyeimbang beban di mana permintaan I/O gagal, tetapi perangkat lunak tidak menangani kondisi kesalahan dengan benar. Perangkat lunak dapat mencoba percobaan ulang tak terbatas. Operasi I/O macet, dan SQL Server tidak dapat menyelesaikan tindakan yang ditentukan. Sama seperti kondisi penulisan log yang dijelaskan sebelumnya, banyak perilaku sistem yang buruk dapat terjadi setelah kondisi seperti itu mengikis sistem.

Resolusi

Untuk mengatasi masalah ini, mulai ulang SQL Server. Namun, terkadang Anda perlu memulai ulang sistem operasi untuk memulihkan pemrosesan. Kami juga menyarankan agar Anda mendapatkan pembaruan perangkat lunak dari vendor I/O.

Contoh 4: Penyimpanan jarak jauh, pencerminan, dan penggerak raid

Banyak sistem menggunakan pencerminan atau mengadopsi langkah serupa untuk mencegah kehilangan data. Beberapa sistem yang menggunakan pencerminan berbasis perangkat lunak, dan beberapa berbasis perangkat keras. Situasi yang biasanya ditemukan oleh Dukungan Microsoft untuk sistem ini adalah peningkatan latensi.

Peningkatan waktu I/O keseluruhan terjadi ketika I/O harus selesai sebelum dianggap selesai. Untuk penginstalan cermin jarak jauh, percobaan ulang jaringan dapat terlibat. Ketika kegagalan drive terjadi, dan sistem raid sedang dibangun kembali, pola I/O juga dapat terganggu.

Resolusi

Pengaturan konfigurasi yang ketat diperlukan untuk mengurangi latensi ke cermin atau untuk menyerbu operasi pembangunan kembali.

Contoh 5: Pemadatan

Microsoft tidak mendukung file data SQL Server dan file log pada drive terkompresi. Pemadatan NTFS tidak aman untuk SQL Server karena pemadatan NTFS memutus protokol Write Ahead Logging (WAL). Kompresi NTFS juga memerlukan peningkatan pemrosesan untuk setiap operasi I/O. Pemadatan menciptakan "satu per satu" seperti perilaku yang menyebabkan masalah performa yang parah terjadi.

Resolusi

Untuk mengatasi masalah ini, hapus kompresi data dan file log.

Untuk informasi selengkapnya, lihat Dukungan untuk database pada volume terkompresi.

Titik data tambahan

PAGEIOLATCH_* dan writelog menunggu dalam tampilan manajemen dinamis (DMV) sys.dm_os_wait_stats adalah indikator utama untuk menyelidiki performa jalur I/O. Jika Anda melihat penantian PAGEIOLATCH yang signifikan, itu berarti bahwa SQL Server menunggu pada subsistem I/O. Sejumlah penantian PAGEIOLATCH adalah perilaku yang khas dan diharapkan. Namun, jika waktu tunggu PAGEIOLATCH rata-rata secara konsisten lebih besar dari 10 milidetik, Anda harus menyelidiki mengapa subsistem I/O berada di bawah tekanan. Untuk informasi selengkapnya, lihat dokumen berikut:

Referensi

SQL Server mengharuskan sistem 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 dan output untuk mesin database SQL Server, kunjungi Persyaratan Input/Output Mesin Database.

Untuk informasi selengkapnya tentang kesalahan I/O, lihat Dasar-Dasar I/O Microsoft SQL Server, Bab 2.