Titik Pemeriksaan Database (SQL Server)

Topik ini menyediakan gambaran umum titik pemeriksaan database SQL Server. 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 Titik Pemeriksaan

Untuk alasan performa, Mesin Database melakukan modifikasi pada halaman database dalam memori dalam 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 tentang 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 *'`recovery interval`','`seconds`'*	Dikeluarkan secara otomatis di latar belakang untuk memenuhi batas waktu atas yang `recovery interval` disarankan oleh opsi konfigurasi server. Pemeriksaan otomatis berjalan hingga selesai. Titik pemeriksaan otomatis diatur berdasarkan jumlah penulisan yang belum selesai dan apakah Mesin Basis Data mendeteksi peningkatan latensi penulisan yang lebih dari 20 milidetik. Untuk informasi selengkapnya, referensi Opsi Konfigurasi Server Interval Pemulihan.
tidak 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. Waktu pemulihan target default adalah 0, yang menyebabkan heuristik titik pemeriksaan otomatis digunakan pada database. Jika Anda telah menggunakan ALTER DATABASE untuk mengatur TARGET_RECOVERY_TIME ke >0, nilai ini digunakan, daripada interval pemulihan yang ditentukan untuk instans server. Untuk informasi selengkapnya, lihat Mengubah Waktu Pemulihan Target Database (SQL Server).
Buku Petunjuk	TITIK PENGECEKAN [ 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 pos 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).
Internasional	Tidak ada.	Dikeluarkan oleh berbagai operasi server seperti pencadangan dan pembuatan rekam jepret database untuk menjamin bahwa gambar disk cocok dengan status log saat ini.

Nota

-kOpsi 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 untuk titik pemeriksaan manual serta internal yang tidak dibatasi.

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 tidak dilakukan yang berjalan lama meningkatkan waktu pemulihan untuk semua jenis titik pemeriksaan.

Interaksi opsi TARGET_RECOVERY_TIME dan 'interval pemulihan'

Tabel berikut merangkum interaksi antara pengaturan server secara keseluruhan sp_configurerecovery interval dan pengaturan khusus database ALTER DATABASE ... TARGET_RECOVERY_TIME.

WAKTU_PEMULIHAN_TARGET	'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 yang ditentukan pengguna dari opsi interval sp_configurerecovery .
>0	Tidak dapat diterapkan.	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 catatan log mencapai angka perkiraan Mesin Database yang dapat diproses selama waktu yang ditentukan dalam recovery interval opsi konfigurasi server. Di setiap database tanpa waktu pemulihan target yang ditentukan pengguna, Mesin Database menghasilkan titik pemeriksaan otomatis. Frekuensi titik pemeriksaan otomatis tergantung pada recovery interval opsi konfigurasi server tingkat lanjut, 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.

Selain itu, dalam model pemulihan sederhana, titik pemeriksaan otomatis juga diantrekan jika catatan menjadi 70 persen penuh.

Di bawah mode pemulihan sederhana, kecuali ada faktor yang menunda pemotongan, titik pemeriksaan otomatis memotong bagian yang tidak terpakai 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 sebagian besar tergantung pada jumlah I/O acak yang diperlukan untuk mengulangi halaman yang kotor pada saat crash. Ini berarti bahwa recovery interval pengaturan 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 cukup tidak terduga.

Dampak Interval Pemulihan pada Performa Pemulihan

Untuk sistem pemrosesan transaksi online (OLTP) yang menggunakan transaksi singkat, recovery interval adalah faktor utama yang menentukan waktu pemulihan. Namun, recovery interval opsi tidak memengaruhi waktu yang diperlukan untuk membatalkan transaksi yang berjalan lama. Pemulihan database dengan transaksi yang berjalan lama dapat memakan waktu lebih lama dari yang ditentukan dalam recovery interval opsi . 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 recovery interval nilai untuk memulihkan transaksi panjang. Untuk informasi selengkapnya tentang dampak transaksi yang berjalan lama pada waktu pemulihan, lihat Log Transaksi (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 ketika transaksi yang berjalan lama tidak digulung balik.
Jika Anda melihat bahwa titik pemeriksaan yang sering menghambat performa pada database.

Jika Anda memutuskan untuk meningkatkan pengaturan recovery interval, sebaiknya tingkatkan secara bertahap dengan peningkatan kecil dan mengevaluasi efek dari setiap peningkatan bertahap pada kinerja pemulihan. Pendekatan ini penting karena ketika pengaturan recovery interval meningkat, waktu yang dibutuhkan untuk pemulihan database menjadi jauh lebih lama. Misalnya, jika Anda mengubah recovery interval 10, pemulihan membutuhkan waktu sekitar 10 kali lebih lama untuk diselesaikan daripada kapan recovery interval diatur ke nol.

Titik Pemeriksaan Tidak Langsung

Titik pemeriksaan tidak langsung, baru di SQL Server 2012, menyediakan alternatif tingkat database yang dapat dikonfigurasi untuk titik pemeriksaan otomatis. 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:

Beban kerja transaksional online pada database yang dikonfigurasi untuk titik pemeriksaan tidak langsung dapat mengalami penurunan performa. 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 penyempitan 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 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.

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 dilakukan.
Rekam jepret database dibuat, baik secara eksplisit maupun internal untuk DBCC CHECK.
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). Salah satu tindakan menyebabkan titik pemeriksaan di setiap database dalam instans SQL Server.
Menjadikan instans kluster failover SQL Server (FCI) tidak aktif.