Memecahkan masalah pembersihan otomatis pelacakan perubahan

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Artikel ini menyediakan cara untuk memecahkan masalah umum yang diamati dalam pembersihan otomatis pelacakan perubahan.

Gejala

Umumnya, jika pembersihan otomatis tidak berfungsi seperti yang diharapkan, Anda dapat melihat satu atau beberapa gejala berikut:

• Konsumsi penyimpanan tinggi oleh satu atau beberapa tabel sisi pelacakan perubahan atau syscommittab tabel sistem.
• Tabel samping (tabel internal yang namanya dimulai dengan awalan change_tracking, misalnya, change_tracking_12345) atau syscommittab keduanya, memperlihatkan jumlah baris signifikan yang berada di luar periode retensi yang dikonfigurasi.
• dbo.MSChange_tracking_history tabel memiliki entri dengan kesalahan pembersihan tertentu.
• CHANGETABLE penurunan performa dari waktu ke waktu.
• Pembersihan otomatis atau pembersihan manual melaporkan penggunaan CPU yang tinggi.

Penelusuran kesalahan dan mitigasi

Untuk mengidentifikasi akar penyebab masalah dengan pembersihan otomatis pelacakan perubahan, gunakan langkah-langkah berikut untuk men-debug dan mengurangi masalah.

Status pembersihan otomatis

Periksa apakah pembersihan otomatis telah berjalan. Untuk memeriksa hal ini, kueri tabel riwayat pembersihan dalam database yang sama. Jika pembersihan telah berjalan, tabel memiliki entri dengan waktu mulai dan berakhir pembersihan. Jika pembersihan belum berjalan, tabel kosong atau memiliki entri kedaluarsa. Jika tabel riwayat memiliki entri dengan tag cleanup errors di kolom comments, maka pembersihan gagal karena kesalahan pembersihan tingkat tabel.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

Pembersihan otomatis berjalan secara berkala dengan interval default 30 menit. Jika tabel riwayat tidak ada, kemungkinan besar, pembersihan otomatis tidak pernah berjalan. Jika tidak, periksa start_time nilai kolom dan end_time . Jika entri terbaru tidak terbaru, yaitu, mereka berjam-jam atau berumur hari, maka pembersihan otomatis mungkin tidak berjalan. Jika demikian, gunakan langkah-langkah berikut untuk memecahkan masalah.

1. Pembersihan dimatikan

Periksa apakah pembersihan otomatis diaktifkan untuk database. Jika tidak, aktifkan dan tunggu setidaknya 30 menit sebelum melihat tabel riwayat untuk entri baru. Pantau kemajuan dalam tabel riwayat setelahnya.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Nilai bukan nol di is_auto_cleanup_on menunjukkan bahwa pembersihan otomatis diaktifkan. Nilai periode retensi mengontrol durasi metadata pelacakan perubahan yang dipertahankan dalam sistem. Nilai default untuk periode retensi pelacakan perubahan adalah 2 hari.

Untuk mengaktifkan atau menonaktifkan pelacakan perubahan, lihat Mengaktifkan dan Menonaktifkan Pelacakan Perubahan (SQL Server).

2. Pembersihan diaktifkan tetapi tidak berjalan

Jika pembersihan otomatis aktif, utas pembersihan otomatis kemungkinan berhenti karena kesalahan yang tidak terduga. Saat ini, memulai ulang utas pembersihan otomatis tidak layak. Anda harus memulai failover ke server sekunder (atau memulai ulang server tanpa adanya sekunder), dan mengonfirmasi bahwa pengaturan pembersihan otomatis diaktifkan untuk database.

Pembersihan otomatis berjalan tetapi tidak membuat kemajuan

Jika satu atau beberapa tabel sisi menunjukkan konsumsi penyimpanan yang signifikan, atau berisi sejumlah besar rekaman di luar retensi yang dikonfigurasi, ikuti langkah-langkah di bagian ini, yang menjelaskan perbaikan untuk tabel sisi tunggal. Langkah yang sama dapat diulang untuk lebih banyak tabel jika perlu.

1. Menilai backlog pembersihan otomatis

Identifikasi tabel samping yang memiliki backlog besar rekaman kedaluwarsa, yang memerlukan mitigasi untuk dilakukan pada mereka. Jalankan kueri berikut untuk mengidentifikasi tabel samping dengan jumlah rekaman besar yang kedaluwarsa. Ingatlah untuk mengganti nilai dalam contoh skrip seperti yang ditunjukkan.

1. Dapatkan versi pembersihan yang tidak valid:

   SELECT * FROM sys.change_tracking_tables;
   

   Nilai cleanup_version dari baris yang dikembalikan mewakili versi pembersihan yang tidak valid.

2. Jalankan kueri Transact-SQL (T-SQL) dinamis berikut, yang menghasilkan kueri untuk mendapatkan jumlah baris tabel samping yang kedaluwarsa. Ganti nilai <invalid_version> dalam kueri dengan nilai yang diperoleh di langkah sebelumnya.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. Salin tataan hasil dari kueri sebelumnya, dan hapus UNION kata kunci dari baris terakhir. Jika Anda menjalankan kueri T-SQL yang dihasilkan melalui koneksi admin khusus (DAC), kueri memberikan jumlah baris yang kedaluwarsa dari semua tabel sisi. Bergantung pada ukuran sys.syscommittab tabel dan jumlah tabel samping, kueri ini mungkin membutuhkan waktu lama untuk diselesaikan.

   Penting

   Langkah ini diperlukan untuk bergerak maju dengan langkah-langkah mitigasi. Jika kueri sebelumnya gagal dijalankan, identifikasi jumlah baris yang kedaluwarsa untuk tabel sisi individual menggunakan kueri yang diberikan berikutnya.

Lakukan langkah-langkah mitigasi berikut untuk tabel samping, yang memiliki penurunan urutan jumlah baris yang kedaluwarsa, hingga jumlah baris yang kedaluwarsa turun ke status yang dapat dikelola agar pembersihan otomatis mengejar ketinggalan.

Setelah Anda mengidentifikasi tabel samping dengan jumlah rekaman besar yang kedaluwarsa, kumpulkan informasi tentang latensi pernyataan penghapusan tabel samping dan tingkat penghapusan per detik selama beberapa jam terakhir. Selanjutnya, perkirakan waktu yang diperlukan untuk membersihkan tabel samping dengan mempertimbangkan jumlah baris kedaluarsa dan latensi penghapusan.

Gunakan cuplikan kode T-SQL berikut dengan mengganti templat parameter dengan nilai yang sesuai.

• Kueri laju pembersihan per detik:

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  Anda juga dapat menggunakan granularitas menit atau jam untuk fungsi tersebut DATEDIFF .

• Temukan jumlah baris kedaluwarsa di tabel samping. Kueri ini membantu Anda menemukan jumlah baris yang tertunda untuk dibersihkan.

  <internal_table_name> dan <cleanup_version> untuk tabel pengguna berada dalam output yang dikembalikan di bagian sebelumnya. Dengan menggunakan informasi ini, jalankan kode T-SQL berikut melalui koneksi admin khusus (DAC):

  SELECT '<internal_table_name>',
      COUNT(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  Kueri ini bisa memakan waktu untuk menyelesaikannya. Dalam kasus di mana waktu kueri habis, hitung baris kedaluarsa dengan menemukan perbedaan antara total baris dan baris aktif yaitu, baris yang akan dibersihkan.

• Temukan jumlah total baris dalam tabel samping dengan menjalankan kueri berikut:

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• Temukan jumlah baris aktif di tabel samping dengan menjalankan kueri berikut:

  SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  Anda dapat menghitung perkiraan waktu untuk membersihkan tabel menggunakan tingkat pembersihan dan jumlah baris kedaluarsa. Pertimbangkan rumus berikut:

  Waktu untuk membersihkan dalam hitungan menit = (jumlah baris basi) / (laju pembersihan dalam menit)

  Jika waktu untuk menyelesaikan pembersihan tabel dapat diterima, pantau kemajuan dan biarkan pembersihan otomatis melanjutkan pekerjaannya. Jika tidak, lanjutkan dengan langkah berikutnya untuk menelusuri lebih lanjut.

2. Periksa konflik kunci tabel

Tentukan apakah pembersihan tidak mengalami kemajuan karena konflik eskalasi kunci tabel, yang pembersihan kelaparan secara konsisten dari memperoleh kunci pada tabel samping untuk menghapus baris.

Untuk mengonfirmasi konflik kunci, jalankan kode T-SQL berikut. Kueri ini mengambil rekaman untuk tabel yang bermasalah untuk menentukan apakah ada beberapa entri yang menunjukkan konflik kunci. Beberapa konflik sporadis yang tersebar selama periode tidak boleh memenuhi syarat untuk langkah-langkah mitigasi yang sedang berlangsung. Konflik harus berulang.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Jika tabel riwayat memiliki beberapa entri dalam comments kolom dengan nilai Cleanup error: Lock request time out period exceeded, ini adalah indikasi yang jelas bahwa beberapa upaya pembersihan gagal karena konflik kunci atau batas waktu penguncian berturut-turut. Pertimbangkan solusi berikut:

• Nonaktifkan dan aktifkan pelacakan perubahan pada tabel yang bermasalah. Ini menyebabkan semua metadata pelacakan dipertahankan agar tabel dibersihkan. Data tabel tetap utuh. Ini adalah obat tercepat.

• Jika opsi sebelumnya tidak memungkinkan, lanjutkan untuk menjalankan pembersihan manual pada tabel dengan mengaktifkan bendera pelacakan 8284 sebagai berikut:

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3. Periksa penyebab lain

Kemungkinan penyebab lain dari keterlambatan pembersihan adalah lambatnya pernyataan penghapusan. Untuk menentukan apakah demikian, periksa nilai hardened_cleanup_version. Nilai ini dapat diambil melalui koneksi admin khusus (DAC) ke database yang sedang dipertimbangkan.

Temukan versi pembersihan yang diperkeras dengan menjalankan kueri berikut:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Temukan versi pembersihan dengan menjalankan kueri berikut:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Jika hardened_cleanup_version nilai dan cleanup_version sama, lewati bagian ini dan lanjutkan ke bagian berikutnya.

Jika kedua nilai berbeda, itu berarti satu atau beberapa tabel sisi mengalami kesalahan. Mitigasi tercepat adalah menonaktifkan & mengaktifkan pelacakan perubahan pada tabel yang bermasalah. Ini menyebabkan semua metadata pelacakan dipertahankan agar tabel dibersihkan. Data dalam tabel tetap utuh.

Jika opsi sebelumnya tidak dimungkinkan, jalankan pembersihan manual pada tabel.

Memecahkan masalah syscommittab

Bagian ini mencakup langkah-langkah untuk men-debug dan mengurangi masalah dengan syscommittab tabel sistem, jika menggunakan banyak ruang penyimpanan, atau jika memiliki backlog besar baris kedaluarsa.

Pembersihan syscommittab tabel sistem tergantung pada pembersihan tabel samping. Hanya setelah semua tabel samping dibersihkan, syscommittab dapat dihapus menyeluruh. Pastikan semua langkah dalam bagian Pembersihan otomatis berjalan tetapi tidak membuat kemajuan dilakukan.

Untuk secara eksplisit memanggil pembersihan syscommittab , gunakan prosedur tersimpan sys.sp_flush_commit_table_on_demand .

Catatan

Prosedur sys.sp_flush_commit_table_on_demand tersimpan dapat memakan waktu jika menghapus backlog baris yang besar.

Seperti yang ditunjukkan di bagian contoh dari artikel sys.sp_flush_commit_table_on_demand , prosedur tersimpan safe_cleanup_version()ini mengembalikan nilai , dan jumlah baris yang dihapus. Jika nilai yang dikembalikan tampaknya 0, dan jika isolasi rekam jepret diaktifkan, pembersihan mungkin tidak menghapus apa pun dari syscommittab.

Jika periode retensi lebih besar dari satu hari, harus aman untuk menjalankan sys.sp_flush_commit_table_on_demand kembali prosedur tersimpan setelah mengaktifkan Bendera Pelacakan 8239 secara global. Menggunakan bendera pelacakan ini ketika isolasi rekam jepret nonaktif selalu aman tetapi dalam beberapa kasus, mungkin tidak diperlukan.

Pemanfaatan CPU tinggi selama pembersihan

Masalah yang dijelaskan di bagian ini mungkin terlihat pada versi SQL Server yang lebih lama. Jika ada sejumlah besar tabel yang dilacak perubahan dalam database, dan pembersihan otomatis atau pembersihan manual menyebabkan pemanfaatan CPU yang tinggi. Masalah ini juga dapat disebabkan karena tabel riwayat, yang disebutkan secara singkat di bagian sebelumnya.

Gunakan kode T-SQL berikut untuk memeriksa jumlah baris dalam tabel riwayat:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Jika jumlah baris cukup besar, coba tambahkan indeks berikut jika tidak ada. Gunakan kode T-SQL berikut untuk menambahkan indeks:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Menjalankan pembersihan lebih sering dari 30 menit

Tabel tertentu dapat mengalami laju perubahan yang tinggi, dan Anda mungkin menemukan bahwa pekerjaan autocleanup tidak dapat membersihkan tabel samping dan syscommittab dalam interval 30 menit. Jika ini terjadi, Anda dapat menjalankan pekerjaan pembersihan manual dengan frekuensi yang ditingkatkan untuk memfasilitasi proses.

Untuk SQL Server dan Azure SQL Managed Instance, buat pekerjaan latar belakang menggunakan sp_flush_CT_internal_table_on_demand dengan internal yang lebih pendek dari default 30 menit. Untuk Azure SQL Database, Azure Logic Apps dapat digunakan untuk menjadwalkan pekerjaan ini.

Kode T-SQL berikut dapat digunakan untuk membuat pekerjaan untuk membantu membersihkan tabel samping untuk pelacakan perubahan:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Konten terkait

• Tentang Pelacakan Perubahan (SQL Server)
• Fungsi Pelacakan Perubahan (Transact-SQL)
• Prosedur tersimpan Pelacakan Perubahan (Transact-SQL)
• Tabel Pelacakan Perubahan (Transact-SQL)
• Menampilkan data dan informasi ruang log untuk database
• Memecahkan masalah penggunaan CPU tinggi di Server SQL