Share via

Mengelola dan memantau perubahan pengambilan data

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Managed Instance

Topik ini menjelaskan cara mengelola dan memantau perubahan pengambilan data untuk SQL Server dan Azure SQL Managed Instance.

Untuk Azure SQL Database, yang menggunakan mekanisme pekerjaan yang berbeda, lihat CDC dengan Azure SQL Database.

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 penangkapan 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.

Parameter pekerjaan pengambilan

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 menguras log sebelum mengembalikan (berkelanjutan = 0) atau menjalankan waitfor (berkelanjutan = 1).

parameter continuous

Parameter continuous mengontrol apakah sp_cdc_scan melepaskan kontrol 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 hingga maxtrans 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 penangkapan, 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 menjadwalkan ulang pemindaian log.

Mode berkelanjutan dan interval polling

Dalam mode berkelanjutan, permintaan pekerjaan penangkapan 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). Dalam mode berkelanjutan, pekerjaan pengambilan tetap aktif, menjalankan WAITFOR antara pemindaian log.

Catatan

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

Menangkap penyesuaian 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 sehari, mungkin memberlakukan tidur yang sangat panjang selama waktu aktivitas puncak, dan bahkan pindah ke interval polling 0 pada penutupan hari ketika penting untuk menyelesaikan hari-hari pemrosesan dan mempersiapkan diri untuk eksekusi malam hari. Kemajuan proses pengambilan juga dapat dipantau untuk menentukan kapan semua transaksi yang dilakukan pada tengah malam telah dipindai dan disimpan dalam tabel perubahan. Ini memungkinkan pekerjaan penangkapan berakhir, untuk dimulai ulang oleh restart harian terjadwal. Untuk menyesuaikan perilaku, Anda dapat mengganti langkah pekerjaan yang memanggil dengan panggilan ke pembungkus sp_cdc_scan tertulis pengguna untuk sp_cdc_scan.

Pekerjaan pembersihan

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

Struktur pekerjaan pembersihan

Mengubah tangkapan data menggunakan strategi pembersihan berbasis retensi untuk mengelola ukuran tabel perubahan. Di SQL Server dan Azure SQL Managed Instance, mekanisme pembersihan terdiri dari pekerjaan SQL Server Agent Transact-SQL 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 yang sesuai 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 baru-baru ini sebagai dasar untuk menghitung marka air rendah baru adalah memungkinkan perubahan tetap dalam tabel perubahan untuk waktu yang ditentukan. Ini terjadi bahkan ketika proses penangkapan 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 pengambilan 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 dikirimkan 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 dari ini yang benar-benar berbasis waktu. Pengetahuan tentang klien, misalnya, dapat digunakan untuk menyediakan 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.

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.

Mengidentifikasi 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 kumpulan hasil kosong berturut-turut, seperti jika pekerjaan penangkapan 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 berturut-turut, 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

Tampilan sys.dm_cdc_log_scan_sessions 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 penangkapan berjalan sesuai jadwal, latensi bisa tinggi karena jeda antara transaksi yang dilakukan pada tabel sumber dan proses pengambilan 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 sys.dm_cdc_log_scan_sessions tampilan dan tampilan sys.dm_cdc_errors secara berkala untuk analisis nanti. Prosedur berikut menyiapkan pengumpul data untuk mengumpulkan data sampel dari sys.dm_cdc_log_scan_sessions tampilan manajemen.

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 data historis dari sesi pemindaian log. Data ini dapat digunakan untuk menganalisis latensi, throughput, dan ukuran 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 tangkapan. Bergantung pada jumlah data yang terlibat, langkah ini mungkin memakan waktu atau menyebabkan penggunaan log transaksi tinggi untuk database CDC yang diaktifkan.

Konten terkait