Mengelola dan memantau perubahan pengambilan data

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance

Topik ini menjelaskan cara mengelola dan memantau perubahan pengambilan data.

Catatan

Dalam Azure SQL Database, pekerjaan SQL Server Agent penangkapan dan pembersihan digantikan oleh penjadwal tangkapan data perubahan yang memanggil prosedur tersimpan untuk memulai penangkapan dan pembersihan tabel perubahan secara berkala.

Pekerjaan pengambilan

Pekerjaan penangkapan dimulai dengan menjalankan prosedur sp_MScdc_capture_jobtersimpan tanpa parameter . Prosedur tersimpan ini dimulai dengan mengekstrak nilai yang dikonfigurasi untuk maxtrans, , maxscanscontinuous, dan pollinginterval untuk pekerjaan pengambilan dari msdb.dbo.cdc_jobs. Nilai yang dikonfigurasi ini kemudian diteruskan sebagai parameter ke prosedur sp_cdc_scantersimpan . Ini digunakan untuk memanggil sp_replcmds untuk melakukan pemindaian log.

Mengambil Parameter Pekerjaan

Untuk memahami perilaku pekerjaan penangkapan, Anda harus memahami bagaimana parameter yang dapat dikonfigurasi digunakan oleh sp_cdc_scan.

Parameter maxtrans

Parameter maxtrans menentukan jumlah maksimum transaksi yang dapat diproses dalam satu siklus pemindaian log. Jika, selama pemindaian, jumlah transaksi yang akan diproses mencapai batas ini, tidak ada transaksi tambahan yang disertakan dalam pemindaian saat ini. Setelah siklus pemindaian selesai, jumlah transaksi yang diproses akan selalu kurang dari atau sama dengan maxtrans.

Parameter maxscans

Parameter maxscans menentukan jumlah maksimum siklus pemindaian yang dicoba untuk mengosongkan log sebelum mengembalikan (berkelanjutan = 0) atau mengeksekusi waitfor (berkelanjutan = 1).

Parameter berkelanjutan

Parameter continuous mengontrol apakah sp_cdc_scan melepaskan kontrol masuk setelah menguras log atau mengeksekusi jumlah maksimum siklus pemindaian (mode satu bidikan). Ini juga mengontrol apakah sp_cdc_scan terus berjalan sampai secara eksplisit berhenti (mode berkelanjutan).

Mode Satu bidikan

Dalam mode satu bidikan, pekerjaan pengambilan meminta sp_cdc_scan untuk melakukan maxtrans hingga pemindaian untuk mencoba mengosongkan log dan mengembalikan. Setiap transaksi selain maxtrans yang ada dalam log akan diproses dalam pemindaian nanti.

Mode satu bidikan digunakan dalam pengujian terkontrol, di mana volume transaksi yang akan diproses diketahui, dan ada keuntungan dari fakta bahwa pekerjaan ditutup secara otomatis ketika selesai. Mode satu bidikan tidak disarankan untuk penggunaan produksi. Ini karena bergantung pada jadwal pekerjaan untuk mengelola seberapa sering siklus pemindaian dijalankan.

Saat berjalan dalam mode satu bidikan, Anda dapat menghitung batas atas pada throughput yang diharapkan dari pekerjaan pengambilan, yang dinyatakan dalam transaksi per detik dengan menggunakan komputasi berikut:

(maxtrans * maxscans) / number of seconds between scans

Bahkan jika waktu yang diperlukan untuk memindai log dan mengisi tabel perubahan tidak jauh berbeda dari 0, throughput rata-rata pekerjaan tidak dapat melebihi nilai yang diperoleh dengan membagi transaksi maksimum yang diizinkan untuk satu pemindaian dikalikan dengan pemindaian maksimum yang diizinkan dengan jumlah detik yang memisahkan pemrosesan log.

Jika mode satu bidikan digunakan untuk mengatur pemindaian log, jumlah detik antara pemrosesan log harus diatur oleh jadwal pekerjaan. Ketika perilaku semacam ini diinginkan, menjalankan pekerjaan penangkapan dalam mode berkelanjutan adalah cara yang lebih baik untuk mengelola penjadwalan ulang pemindaian log.

Mode berkelanjutan dan interval polling

Dalam mode berkelanjutan, permintaan pekerjaan pengambilan yang sp_cdc_scan berjalan terus menerus. Ini memungkinkan prosedur tersimpan mengelola perulangan tunggunya sendiri dengan menyediakan tidak hanya untuk maxtrans dan maxscans tetapi juga nilai untuk jumlah detik antara pemrosesan log (interval polling). Berjalan dalam mode ini, pekerjaan pengambilan tetap aktif, menjalankan WAITFOR antara pemindaian log.

Catatan

Ketika nilai interval polling lebih besar dari 0, batas atas throughput yang sama untuk pekerjaan satu bidikan berulang juga berlaku untuk operasi pekerjaan dalam mode berkelanjutan. Artinya, (maxtrans * maxscans) dibagi dengan interval polling bukan nol akan menempatkan batas atas pada jumlah rata-rata transaksi yang dapat diproses oleh pekerjaan penangkapan.

Mengambil Kustomisasi Pekerjaan

Untuk pekerjaan pengambilan, Anda dapat menerapkan logika tambahan untuk menentukan apakah pemindaian baru segera dimulai atau apakah tidur diberlakukan sebelum memulai pemindaian baru alih-alih mengandalkan interval polling tetap. Pilihannya bisa didasarkan hanya pada waktu dalam sehari, mungkin memberlakukan tidur yang sangat lama selama waktu aktivitas puncak, dan bahkan pindah ke interval polling 0 pada penutupan hari ketika penting untuk menyelesaikan pemrosesan hari dan mempersiapkan eksekusi malam hari. Kemajuan proses pengambilan juga dapat dipantau untuk menentukan kapan semua transaksi yang dilakukan pada pertengahan malam telah dipindai dan disimpan dalam tabel perubahan. Ini memungkinkan pekerjaan penangkapan berakhir, untuk dimulai ulang oleh restart harian terjadwal. Dengan mengganti panggilan sp_cdc_scan langkah pekerjaan yang dikirimkan dengan panggilan ke pembungkus tertulis pengguna untuk sp_cdc_scan, perilaku yang sangat disesuaikan dapat diperoleh dengan sedikit upaya tambahan.

Pekerjaan pembersihan

Bagian ini menyediakan informasi tentang cara kerja pekerjaan pembersihan pengambilan data perubahan.

Struktur Pekerjaan Pembersihan

Mengubah pengambilan data menggunakan strategi pembersihan berbasis retensi untuk mengelola ukuran tabel perubahan. Dalam SQL Server dan Azure SQL Managed Instance, mekanisme pembersihan terdiri dari pekerjaan SQL Transact SQL Server Agent yang dibuat saat tabel database pertama diaktifkan. Satu pekerjaan pembersihan menangani pembersihan untuk semua tabel perubahan database dan menerapkan nilai retensi yang sama untuk semua instans pengambilan yang ditentukan.

Pekerjaan pembersihan dimulai dengan menjalankan prosedur sp_MScdc_cleanup_jobtersimpan tanpa parameter . Prosedur tersimpan ini dimulai dengan mengekstrak nilai retensi dan ambang batas yang dikonfigurasi untuk pekerjaan pembersihan dari msdb.dbo.cdc_jobs. Nilai retensi digunakan untuk menghitung marka air rendah baru untuk tabel perubahan. Jumlah menit yang ditentukan dikurangi dari nilai maksimum tran_end_time dari cdc.lsn_time_mapping tabel untuk mendapatkan tanda air rendah baru yang dinyatakan sebagai nilai tanggalwaktu. Tabel CDC.lsn_time_mapping kemudian digunakan untuk mengonversi nilai tanggalwaktu ini ke nilai terkait lsn . Jika waktu penerapan yang sama dibagikan oleh beberapa entri dalam tabel, lsn yang sesuai dengan entri yang memiliki yang terkecil lsn dipilih sebagai marka air rendah baru. Nilai ini lsn diteruskan ke sp_cdc_cleanup_change_tables untuk menghapus entri tabel perubahan dari tabel perubahan database.

Catatan

Keuntungan menggunakan waktu penerapan transaksi terbaru sebagai dasar untuk menghitung marka air rendah baru adalah memungkinkan perubahan tetap dalam tabel perubahan untuk waktu yang ditentukan. Ini terjadi bahkan ketika proses pengambilan berjalan di belakang. Semua entri yang memiliki waktu penerapan yang sama dengan marka air rendah saat ini terus diwakili dalam tabel perubahan dengan memilih yang terkecil lsn yang memiliki waktu penerapan bersama untuk marka air rendah aktual.

Ketika pembersihan dilakukan, marka air rendah untuk semua instans tangkapan awalnya diperbarui dalam satu transaksi. Kemudian mencoba menghapus entri usang dari tabel perubahan dan tabel cdc.lsn_time_mapping. Nilai ambang batas yang dapat dikonfigurasi membatasi berapa banyak entri yang dihapus dalam pernyataan tunggal apa pun. Kegagalan untuk melakukan penghapusan pada tabel individual apa pun tidak akan mencegah operasi dicoba pada tabel yang tersisa.

Kustomisasi pekerjaan pembersihan

Untuk pekerjaan pembersihan, kemungkinan untuk penyesuaian ada dalam strategi yang digunakan untuk menentukan entri tabel perubahan mana yang akan dibuang. Satu-satunya strategi yang didukung dalam pekerjaan pembersihan yang disampaikan adalah strategi berbasis waktu. Dalam situasi itu, marka air rendah baru dihitung dengan mengurangi periode retensi yang diizinkan dari waktu penerapan transaksi terakhir yang diproses. Karena prosedur pembersihan yang mendasar didasarkan pada lsn alih-alih waktu, sejumlah strategi dapat digunakan untuk menentukan yang terkecil lsn untuk disimpan dalam tabel perubahan. Hanya beberapa di antaranya yang benar-benar berbasis waktu. Pengetahuan tentang klien, misalnya, dapat digunakan untuk memberikan failsafe jika proses hilir yang memerlukan akses ke tabel perubahan tidak dapat berjalan. Selain itu, meskipun strategi default menerapkan hal yang sama lsn untuk membersihkan semua tabel perubahan database, prosedur pembersihan yang mendasar, juga dapat dipanggil untuk membersihkan di tingkat instans tangkapan.

Catatan

Di Azure SQL Database, penjadwal tangkapan data perubahan secara berkala memanggil prosedur tersimpan untuk menangkap dan membersihkan tabel perubahan. Dengan demikian, penyesuaian proses penangkapan dan pembersihan di Azure SQL Database saat ini tidak dimungkinkan. Meskipun penjadwal menjalankan prosedur tersimpan secara otomatis, dimungkinkan juga untuk memulainya secara manual oleh pengguna.

Memantau proses

Memantau proses pengambilan data perubahan memungkinkan Anda menentukan apakah perubahan ditulis dengan benar dan dengan latensi yang wajar pada tabel perubahan. Pemantauan juga dapat membantu Anda mengidentifikasi kesalahan apa pun yang mungkin terjadi. SQL Server menyertakan dua tampilan manajemen dinamis untuk membantu Anda memantau perubahan pengambilan data: sys.dm_cdc_log_scan_sessions dan sys.dm_cdc_errors.

Identifikasi Sesi dengan Tataan Hasil Kosong

Setiap baris dalam sys.dm_cdc_log_scan_sessions mewakili sesi pemindaian log (kecuali baris dengan ID 0). Sesi pemindaian log setara dengan satu eksekusi sp_cdc_scan. Selama sesi, pemindaian dapat mengembalikan perubahan atau mengembalikan hasil kosong. Jika tataan hasil kosong, kolom empty_scan_count di sys.dm_cdc_log_scan_sessions diatur ke 1. Jika ada tataan hasil kosong berturut-turut, seperti jika pekerjaan pengambilan berjalan terus menerus, empty_scan_count di baris terakhir yang ada akan bertahap. Misalnya, jika sys.dm_cdc_log_scan_sessions sudah berisi 10 baris untuk pemindaian yang mengembalikan perubahan dan ada lima hasil kosong dalam satu baris, tampilan berisi 11 baris. Baris terakhir memiliki nilai 5 di kolom empty_scan_count. Untuk menentukan sesi yang memiliki pemindaian kosong, jalankan kueri berikut:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Tentukan Latensi

sys.dm_cdc_log_scan_sessions Tampilan manajemen menyertakan kolom yang merekam latensi untuk setiap sesi pengambilan. Latensi didefinisikan sebagai waktu yang berlalu antara transaksi yang dilakukan pada tabel sumber dan transaksi terakhir yang ditangkap yang dilakukan pada tabel perubahan. Kolom latensi diisi hanya untuk sesi aktif. Untuk sesi dengan nilai yang lebih besar dari 0 di kolom empty_scan_count, kolom latensi diatur ke 0. Kueri berikut mengembalikan latensi rata-rata untuk sesi terbaru:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Anda dapat menggunakan data latensi untuk menentukan seberapa cepat atau lambat proses pengambilan memproses transaksi. Data ini paling berguna ketika proses penangkapan berjalan terus menerus. Jika proses pengambilan berjalan sesuai jadwal, latensi bisa tinggi karena jeda antara transaksi yang dilakukan pada tabel sumber dan proses penangkapan berjalan pada waktu yang dijadwalkan.

Ukuran penting lainnya dari efisiensi proses penangkapan adalah throughput. Ini adalah jumlah rata-rata perintah per detik yang diproses selama setiap sesi. Untuk menentukan throughput sesi, bagi nilai di kolom command_count dengan nilai dalam kolom durasi. Kueri berikut mengembalikan throughput rata-rata untuk sesi terbaru:

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Menggunakan Pengumpul Data untuk Mengumpulkan Data Pengambilan Sampel

Pengumpul data SQL Server memungkinkan Anda mengumpulkan rekam jepret data dari tabel atau tampilan manajemen dinamis apa pun dan membangun gudang data performa. Saat mengubah tangkapan data diaktifkan pada database, berguna untuk mengambil rekam jepret tampilan sys.dm_cdc_log_scan_sessions dan tampilan sys.dm_cdc_errors secara berkala untuk analisis nanti. Prosedur berikut menyiapkan pengumpul data untuk mengumpulkan data sampel dari tampilan manajemen sys.dm_cdc_log_scan_sessions.

Mengonfigurasi Pengumpulan Data

1. Aktifkan pengumpul data dan konfigurasikan gudang data manajemen. Untuk informasi selengkapnya, lihat Mengelola Pengumpulan Data.

2. Jalankan kode berikut untuk membuat pengumpul kustom untuk mengubah pengambilan data.

   USE msdb;  
   
   DECLARE @schedule_uid uniqueidentifier;  
   
   -- Collect and upload data every 5 minutes  
   SELECT @schedule_uid = (  
   SELECT schedule_uid from sysschedules_localserver_view
   WHERE name = N'CollectorSchedule_Every_5min')  
   
   DECLARE @collection_set_id int;  
   
   EXEC dbo.sp_syscollector_create_collection_set  
   @name = N' CDC Performance Data Collector',  
   @schedule_uid = @schedule_uid,
   @collection_mode = 0,
   @days_until_expiration = 30,
   @description = N'This collection set collects CDC metadata',  
   @collection_set_id = @collection_set_id output;  
   
   -- Create a collection item using statistics from
   -- the change data capture dynamic management view.  
   DECLARE @parameters xml;  
   DECLARE @collection_item_id int;  
   
   SELECT @parameters = CONVERT(xml,
       N'<TSQLQueryCollector>  
           <Query>  
             <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
             <OutputTable>cdc_log_scan_data</OutputTable>  
           </Query>  
         </TSQLQueryCollector>');  
   
   EXEC dbo.sp_syscollector_create_collection_item  
   @collection_set_id = @collection_set_id,  
   @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
   @name = ' CDC Performance Data Collector',  
   @frequency = 5,
   @parameters = @parameters,  
   @collection_item_id = @collection_item_id output;
   
   GO  
   

3. Di SQL Server Management Studio, perluas Manajemen, lalu perluas Pengumpulan Data. Klik kanan Pengumpul Data Performa CDC, lalu klik Mulai Kumpulan Kumpulan Data.

4. Di gudang data yang Anda konfigurasi di langkah 1, temukan tabel custom_snapshots.cdc_log_scan_data. Tabel ini menyediakan rekam jepret historis data dari sesi pemindaian log. Data ini dapat digunakan untuk menganalisis latensi, throughput, dan pengukuran performa lainnya dari waktu ke waktu.

Mode peningkatan skrip

Saat Anda menerapkan pembaruan kumulatif atau paket layanan ke instans, saat menghidupkan ulang, instans dapat masuk dalam mode Peningkatan Skrip. Dalam mode ini, SQL Server dapat menjalankan langkah untuk menganalisis dan meningkatkan tabel CDC internal, yang dapat mengakibatkan pembuatan ulang objek seperti indeks pada tabel pengambilan. Bergantung pada jumlah data yang terlibat, langkah ini mungkin memakan waktu atau menyebabkan penggunaan log transaksi tinggi untuk database CDC yang diaktifkan.

Lihat juga

• Lacak Perubahan Data (SQL Server)
• Tentang perubahan pengambilan data (SQL Server)
• Mengaktifkan dan Menonaktifkan perubahan pengambilan data (SQL Server)
• Bekerja dengan Ubah Data (SQL Server)