Bagikan melalui

Titik pemeriksaan database (SQL Server)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database

Titik pemeriksaan membuat titik bagus yang diketahui dari mana Mesin Database SQL Server dapat mulai menerapkan perubahan yang terkandung dalam log selama pemulihan setelah pematian atau crash yang tidak terduga.

Gambaran Umum

Untuk alasan performa, Mesin Database melakukan modifikasi pada halaman database dalam memori, di cache buffer, dan tidak menulis halaman ini ke disk setelah setiap perubahan. Sebaliknya, Mesin Database secara berkala mengeluarkan titik pemeriksaan pada setiap database. Titik pemeriksaan menulis halaman yang dimodifikasi dalam memori saat ini (dikenal sebagai halaman kotor) dan informasi log transaksi dari memori ke disk, dan juga merekam informasi dalam log transaksi.

Mesin Database mendukung beberapa jenis titik pemeriksaan: otomatis, tidak langsung, manual, dan internal. Tabel berikut ini meringkas jenis titik pemeriksaan.

Nama Antarmuka Transact-SQL Deskripsi
Otomatis EXEC sp_configure 'interval pemulihan', 'detik' Dikeluarkan secara otomatis di latar belakang untuk memenuhi batas waktu atas yang disarankan oleh opsi konfigurasi server interval pemulihan. Titik pemeriksaan otomatis berjalan hingga selesai. Titik pemeriksaan otomatis dibatasi berdasarkan jumlah penulisan yang luar biasa dan apakah Mesin Database mendeteksi peningkatan latensi tulis di atas 50 milidetik.

Untuk informasi selengkapnya, lihat Mengonfigurasi Opsi Konfigurasi Server interval pemulihan.
Langsung UBAH DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MENIT } Dikeluarkan di latar belakang untuk memenuhi waktu pemulihan target yang ditentukan pengguna untuk database tertentu. Dimulai dengan SQL Server 2016 (13.x), nilai defaultnya adalah 1 menit. Defaultnya adalah 0 untuk versi yang lebih lama, yang menunjukkan bahwa database akan menggunakan titik pemeriksaan otomatis, yang frekuensinya tergantung pada pengaturan interval pemulihan instans server.

Untuk informasi selengkapnya, lihat Mengubah Waktu Pemulihan Target Database (SQL Server).
Manual CHECKPOINT [ checkpoint_duration ] Dikeluarkan saat Anda menjalankan perintah Transact-SQL CHECKPOINT. Titik pemeriksaan manual terjadi di database saat ini untuk koneksi Anda. Secara default, titik pemeriksaan manual berjalan hingga selesai. Pembatasan bekerja dengan cara yang sama seperti untuk titik pemeriksaan otomatis. Secara opsional, parameter checkpoint_duration menentukan jumlah waktu yang diminta, dalam hitungan detik, agar titik pemeriksaan selesai.

Untuk informasi selengkapnya, lihat CHECKPOINT (Transact-SQL).
Internal Tidak Dikeluarkan oleh berbagai operasi server seperti pencadangan dan pembuatan rekam jepret database untuk menjamin bahwa gambar disk cocok dengan status log saat ini.

-k Opsi penyiapan tingkat lanjut SQL Server memungkinkan administrator database untuk membatasi perilaku I/O titik pemeriksaan berdasarkan throughput subsistem I/O untuk beberapa jenis titik pemeriksaan. Opsi -k penyiapan berlaku untuk titik pemeriksaan otomatis dan titik pemeriksaan manual dan internal yang tidak diberkati.

Untuk titik pemeriksaan otomatis, manual, dan internal, hanya modifikasi yang dilakukan setelah titik pemeriksaan terbaru yang perlu digulirkan ke depan selama pemulihan database. Ini mengurangi waktu yang diperlukan untuk memulihkan database.

Penting

Transaksi yang berjalan lama dan tidak dilakukan meningkatkan waktu pemulihan untuk semua jenis titik pemeriksaan.

Interaksi opsi TARGET_RECOVERY_TIME dan 'interval pemulihan'

Tabel berikut ini meringkas interaksi antara pengaturan seluruh sp_configure 'recovery interval' server dan pengaturan khusus ALTER DATABASE ... TARGET_RECOVERY_TIME database.

TARGET_RECOVERY_TIME 'interval pemulihan' Jenis titik pemeriksaan yang digunakan
0 0 titik pemeriksaan otomatis yang interval pemulihan targetnya adalah 1 menit.
0 > 0 Titik pemeriksaan otomatis yang interval pemulihan targetnya ditentukan oleh pengaturan opsi yang sp_configure 'recovery interval' ditentukan pengguna.
> 0 Tidak berlaku Titik pemeriksaan tidak langsung yang waktu pemulihan targetnya ditentukan oleh pengaturan TARGET_RECOVERY_TIME, yang dinyatakan dalam hitung detik.

Titik pemeriksaan otomatis

Titik pemeriksaan otomatis terjadi setiap kali jumlah rekaman log mencapai angka yang dapat diproses mesin database selama waktu yang ditentukan dalam opsi konfigurasi server interval pemulihan. Untuk informasi selengkapnya, lihat Mengonfigurasi Opsi Konfigurasi Server interval pemulihan.

Di setiap database tanpa waktu pemulihan target yang ditentukan pengguna, Mesin Database menghasilkan titik pemeriksaan otomatis. Frekuensi tergantung pada opsi konfigurasi server tingkat lanjut interval pemulihan, yang menentukan waktu maksimum yang harus digunakan instans server tertentu untuk memulihkan database selama mulai ulang sistem. Mesin Database memperkirakan jumlah maksimum rekaman log yang dapat diproses dalam interval pemulihan. Saat database yang menggunakan titik pemeriksaan otomatis mencapai jumlah maksimum rekaman log ini, Mesin Database mengeluarkan titik pemeriksaan pada database.

Interval waktu antara titik pemeriksaan otomatis bisa sangat bervariasi. Database dengan beban kerja transaksi substansial akan memiliki titik pemeriksaan yang lebih sering daripada database yang digunakan terutama untuk operasi baca-saja. Di bawah model pemulihan sederhana, titik pemeriksaan otomatis juga diantrekan jika log menjadi 70 persen penuh.

Di bawah model pemulihan sederhana, kecuali beberapa faktor menunda pemotongan log, titik pemeriksaan otomatis memotong bagian yang tidak digunakan dari log transaksi. Sebaliknya, di bawah model pemulihan penuh dan dicatat secara massal, setelah rantai cadangan log dibuat, titik pemeriksaan otomatis tidak menyebabkan pemotongan log. Untuk informasi selengkapnya, lihat Log Transaksi (SQL Server).

Setelah crash sistem, lamanya waktu yang diperlukan untuk memulihkan database tertentu sangat tergantung pada jumlah I/O acak yang diperlukan untuk mengulangi halaman yang kotor pada saat crash. Ini berarti bahwa pengaturan interval pemulihan tidak dapat diandalkan. Ini tidak dapat menentukan durasi pemulihan yang akurat. Selain itu, ketika titik pemeriksaan otomatis sedang berlangsung, aktivitas I/O umum untuk data meningkat secara signifikan dan tidak terduga.

Efek interval pemulihan pada performa pemulihan

Untuk sistem pemrosesan transaksi online (OLTP) menggunakan transaksi singkat, interval pemulihan adalah faktor utama yang menentukan waktu pemulihan. Namun, opsi interval pemulihan tidak memengaruhi waktu yang diperlukan untuk membatalkan transaksi yang berjalan lama. Pemulihan database dengan transaksi yang berjalan lama dapat memakan waktu lebih lama dari waktu yang ditentukan dalam pengaturan interval pemulihan.

Misalnya, jika transaksi yang berjalan lama membutuhkan waktu dua jam untuk melakukan pembaruan sebelum instans server dinonaktifkan, pemulihan aktual membutuhkan waktu jauh lebih lama daripada nilai interval pemulihan untuk memulihkan transaksi panjang. Untuk informasi selengkapnya tentang dampak transaksi yang berjalan lama pada waktu pemulihan, lihat Log Transaksi (SQL Server). Untuk informasi selengkapnya tentang proses pemulihan, lihat Gambaran Umum Pemulihan dan Pemulihan (SQL Server).

Biasanya, nilai default memberikan performa pemulihan yang optimal. Namun, mengubah interval pemulihan dapat meningkatkan performa dalam keadaan berikut:

  • Jika pemulihan secara rutin membutuhkan waktu lebih dari 1 menit secara signifikan saat transaksi jangka panjang tidak digulung balik.

  • Jika Anda melihat bahwa titik pemeriksaan yang sering menghambat performa pada database.

Jika Anda memutuskan untuk meningkatkan pengaturan interval pemulihan, sebaiknya tingkatkan secara bertahap dengan kenaikan kecil dan mengevaluasi efek dari setiap peningkatan bertahap pada performa pemulihan. Pendekatan ini penting karena seiring dengan meningkatnya pengaturan interval pemulihan, pemulihan database membutuhkan waktu yang lebih lama untuk diselesaikan. Misalnya, jika Anda mengubah interval pemulihan menjadi 10 menit, pemulihan membutuhkan waktu sekitar 10 kali lebih lama untuk diselesaikan daripada saat interval pemulihan diatur ke 1 menit.

Titik pemeriksaan tidak langsung

Titik pemeriksaan tidak langsung, yang diperkenalkan di SQL Server 2012 (11.x), menyediakan alternatif tingkat database yang dapat dikonfigurasi untuk titik pemeriksaan otomatis. Ini dapat dikonfigurasi dengan menentukan opsi konfigurasi database waktu pemulihan target. Untuk informasi selengkapnya, lihat Mengubah Waktu Pemulihan Target Database (SQL Server). Jika terjadi crash sistem, titik pemeriksaan tidak langsung memberikan waktu pemulihan yang berpotensi lebih cepat dan lebih dapat diprediksi daripada titik pemeriksaan otomatis.

Titik pemeriksaan tidak langsung menawarkan keuntungan berikut:

  • Titik pemeriksaan tidak langsung memastikan bahwa jumlah halaman kotor berada di bawah ambang batas tertentu sehingga pemulihan database selesai dalam waktu pemulihan target.

    Opsi konfigurasi interval pemulihan menggunakan jumlah transaksi untuk menentukan waktu pemulihan, dibandingkan dengan titik pemeriksaan tidak langsung yang menggunakan jumlah halaman kotor. Ketika titik pemeriksaan tidak langsung diaktifkan pada database yang menerima sejumlah besar operasi DML, penulis latar belakang dapat mulai secara agresif membersihkan buffer kotor ke disk untuk memastikan bahwa waktu yang diperlukan untuk melakukan pemulihan berada dalam kumpulan waktu pemulihan target database. Ini dapat menyebabkan aktivitas I/O tambahan pada sistem tertentu, yang dapat berkontribusi pada hambatan performa jika subsistem disk beroperasi di atas atau mendekati ambang I/O.

  • Titik pemeriksaan tidak langsung memungkinkan Anda mengontrol waktu pemulihan database dengan andal dengan memperhitungkan biaya I/O acak selama REDO. Ini memungkinkan instans server untuk tetap berada dalam batas batas atas pada waktu pemulihan untuk database tertentu (kecuali ketika transaksi yang berjalan lama menyebabkan waktu UNDO yang berlebihan).

  • Titik pemeriksaan tidak langsung mengurangi lonjakan I/O terkait titik pemeriksaan dengan terus menulis halaman kotor ke disk di latar belakang.

Namun, beban kerja transaksional online pada database yang dikonfigurasi untuk titik pemeriksaan tidak langsung dapat mengalami penurunan performa. Ini karena penulis latar belakang yang digunakan oleh titik pemeriksaan tidak langsung terkadang meningkatkan total beban tulis untuk instans server.

Penting

Titik pemeriksaan tidak langsung adalah perilaku default untuk database baru yang dibuat di SQL Server 2016 (13.x), termasuk model database dan tempdb .

Database yang ditingkatkan di tempat, atau dipulihkan dari versi SQL Server sebelumnya, akan menggunakan perilaku titik pemeriksaan otomatis sebelumnya kecuali diubah secara eksplisit untuk menggunakan titik pemeriksaan tidak langsung.

Peningkatan skalabilitas titik pemeriksaan tidak langsung

Sebelum SQL Server 2019 (15.x), Anda mungkin mengalami kesalahan penjadwal yang tidak menghasilkan ketika ada database yang menghasilkan sejumlah besar halaman kotor, seperti tempdb. SQL Server 2019 (15.x) memperkenalkan peningkatan skalabilitas untuk titik pemeriksaan tidak langsung, yang akan membantu menghindari kesalahan ini pada database yang memiliki beban kerja berat UPDATE/INSERT .

Titik pemeriksaan internal

Titik Pemeriksaan Internal dihasilkan oleh berbagai komponen server untuk menjamin bahwa gambar disk cocok dengan status log saat ini. Titik pemeriksaan internal dihasilkan sebagai respons terhadap peristiwa berikut:

  • File database telah ditambahkan atau dihapus dengan menggunakan ALTER DATABASE.

  • Pencadangan database diambil.

  • Rekam jepret database dibuat, baik secara eksplisit maupun internal untuk DBCC CHECKDB.

  • Aktivitas yang mengharuskan penonaktifan database dilakukan. Misalnya, AUTO_CLOSE AKTIF dan koneksi pengguna terakhir ke database ditutup, atau perubahan opsi database dilakukan yang memerlukan mulai ulang database.

  • Instans SQL Server dihentikan dengan menghentikan layanan SQL Server (MSSQLSERVER). Tindakan ini menyebabkan titik pemeriksaan di setiap database dalam instans SQL Server.

  • Membawa instans kluster failover SQL Server (FCI) offline.

Langkah berikutnya

Lihat juga