Bagikan melalui

Memantau dan memecahkan masalah pemulihan database yang dipercepat

Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceSQL database di Microsoft Fabric

Artikel ini membantu Anda memantau, mendiagnosis, dan mengatasi masalah dengan pemulihan database yang dipercepat (ADR) di SQL Server 2019 (15.x) dan yang lebih baru, Azure SQL Managed Instance, Azure SQL Database, dan database SQL di Microsoft Fabric.

Periksa ukuran PVS

Gunakan DMV sys.dm_tran_persistent_version_store_stats untuk mengidentifikasi apakah ukuran penyimpanan versi persisten (PVS) lebih besar dari yang diharapkan.

Contoh kueri diagnostik berikut menunjukkan informasi tentang ukuran PVS saat ini, proses pembersihan, dan detail lainnya di semua database di mana ukuran PVS lebih besar dari nol:

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_percent_of_database_size,
       df.total_db_size_kb / 1024. / 1024 AS total_db_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
       pvss.pvs_off_row_page_skipped_low_water_mark,
       pvss.pvs_off_row_page_skipped_min_useful_xts,
       pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.type = 0
            ) AS df
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

Periksa kolom pvs_percent_of_database_size untuk melihat ukuran PVS relatif terhadap ukuran database total. Catat perbedaan antara ukuran PVS yang umum dan baseline yang terlihat selama periode umum aktivitas aplikasi. PVS dianggap besar jika secara signifikan lebih besar dari patokan atau jika mendekati 50% dari ukuran database.

Jika ukuran PVS tidak menurun, gunakan langkah-langkah pemecahan masalah berikut untuk menemukan dan mengatasi alasan ukuran PVS besar.

Tip

Kolom yang disebutkan dalam langkah-langkah pemecahan masalah berikut merujuk ke kolom dalam kumpulan hasil kueri diagnostik di bagian ini.

Ukuran PVS besar mungkin disebabkan oleh salah satu alasan berikut:

Periksa transaksi aktif yang berjalan lama

Transaksi aktif yang berjalan lama dapat mencegah pembersihan PVS dalam database yang mengaktifkan ADR. Periksa waktu mulai transaksi aktif terlama menggunakan kolom oldest_transaction_begin_time. Untuk menemukan transaksi yang berjalan lama, gunakan contoh kueri berikut. Anda dapat mengatur ambang batas untuk durasi transaksi dan jumlah log transaksi yang dihasilkan:

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.transaction_id,
        atr.name,
        sess.login_time,
        dbtr.database_transaction_log_bytes_used,
        CASE WHEN GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time) THEN 'DurationThresholdExceeded'
             WHEN dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes THEN 'LogThresholdExceeded'
             ELSE 'Unknown'
        END
        AS reason
FROM sys.dm_tran_active_transactions AS tr
INNER JOIN sys.dm_tran_session_transactions AS st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.session_id = sess.session_id
INNER JOIN sys.dm_tran_database_transactions AS dbtr
ON tr.transaction_id = dbtr.transaction_id
INNER JOIN sys.dm_tran_active_transactions AS atr
ON atr.transaction_id = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Dengan sesi yang telah diidentifikasi, pertimbangkan untuk menghentikan sesi tersebut, jika diizinkan. Tinjau aplikasi untuk menentukan sifat transaksi yang bermasalah untuk menghindari masalah di masa mendatang.

Untuk informasi lebih lanjut tentang cara memecahkan masalah pada kueri yang berjalan lama, lihat:

Periksa pemindaian rekam jepret aktif yang berjalan lama

Pemindaian rekam jepret aktif yang berlangsung lama dapat menghambat pembersihan PVS pada database yang memiliki ADR diaktifkan. Pernyataan yang menggunakan isolasi rekam jepret READ COMMITTED (RCSI) atau tingkat isolasi SNAPSHOT menerima tanda waktu tingkat instans. Pemindaian snapshot menggunakan tanda waktu untuk menentukan visibilitas baris versi untuk transaksi RCSI atau SNAPSHOT. Setiap pernyataan yang menggunakan RCSI memiliki tanda waktunya sendiri, sedangkan isolasi SNAPSHOT memiliki tanda waktu tingkat transaksi.

Tanda waktu transaksi tingkat instans ini digunakan bahkan dalam transaksi database tunggal, karena transaksi apa pun dapat dipromosikan ke transaksi lintas database. Oleh karena itu, pemindaian rekam jepret dapat mencegah pembersihan PVS dalam database apa pun pada instans mesin database yang sama. Demikian pula, bahkan ketika ADR tidak diaktifkan, pemindaian rekam jepret dapat mencegah pembersihan penyimpanan versi di tempdb. Akibatnya, PVS mungkin bertambah ukuran ketika ada transaksi jangka panjang yang menggunakan SNAPSHOT atau RCSI.

Kolom pvs_off_row_page_skipped_min_useful_xts menunjukkan jumlah halaman yang dilewati selama pembersihan karena pemindaian cuplikan yang panjang. Jika kolom ini menunjukkan nilai besar, itu berarti bahwa pemindaian snapshot yang lama sedang menghalangi pembersihan PVS.

Gunakan contoh kueri berikut untuk menemukan sesi dengan transaksi SNAPSHOT atau RCSI yang berjalan lama:

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

Untuk mencegah penundaan pembersihan PVS:

  • Pertimbangkan untuk menghentikan sesi transaksi aktif yang berlangsung lama yang menunda pembersihan PVS, jika memungkinkan.
  • Mengoptimalkan kueri yang berjalan lama untuk mengurangi waktu kueri.
  • Tinjau aplikasi untuk menentukan sifat pemindaian rekam jepret aktif yang bermasalah. Pertimbangkan tingkat isolasi yang berbeda, seperti READ COMMITTED, alih-alih SNAPSHOT atau RCSI untuk kueri jangka panjang yang menunda pembersihan PVS. Masalah ini terjadi lebih sering dengan tingkat isolasi SNAPSHOT.
  • Di kumpulan elastis Azure SQL Database, pertimbangkan untuk memindahkan database yang memiliki transaksi jangka panjang menggunakan isolasi SNAPSHOT atau RCSI keluar dari kumpulan elastis untuk menghindari keterlambatan pembersihan PVS dalam database lain di kumpulan yang sama.

Periksa kueri yang memakan waktu lama pada replika sekunder

Jika database memiliki replika sekunder, periksa apakah ambang batas rendah sekunder berkembang.

Nilai yang besar dalam kolom pvs_off_row_page_skipped_low_water_mark mungkin merupakan indikasi penundaan dalam proses pembersihan akibat kueri yang berjalan lama pada replika sekunder. Selain menahan pembersihan PVS, kueri yang berjalan lama pada replika sekunder juga dapat menahan pembersihan data sisa (ghost cleanup) .

Anda dapat menggunakan contoh kueri berikut pada replika utama untuk mengetahui apakah kueri yang berjalan lama pada replika sekunder dapat menghalangi pembersihan PVS. Jika beban kerja tulis berjalan pada replika utama, tetapi nilai di kolom low_water_mark_for_ghosts tidak meningkat dari satu eksekusi kueri contoh ke eksekusi berikutnya, maka pembersihan PVS dan hantu mungkin terhambat oleh kueri yang berjalan lama pada replika sekunder.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

Untuk informasi selengkapnya, lihat deskripsi kolom low_water_mark_for_ghosts di sys.dm_hadr_database_replica_states.

Sambungkan ke setiap replika sekunder yang dapat dibaca, cari sesi dengan kueri yang berjalan lama dan pertimbangkan untuk menghentikan sesi tersebut, jika diizinkan. Untuk informasi selengkapnya, lihat Temukan kueri lambat.

Periksa sejumlah besar transaksi yang dibatalkan

Periksa kolom aborted_version_cleaner_start_time dan aborted_version_cleaner_end_time untuk melihat apakah pembersihan transaksi terakhir yang dibatalkan telah selesai. oldest_aborted_transaction_id harus bergerak lebih tinggi setelah pembersihan transaksi yang dibatalkan selesai. Jika oldest_aborted_transaction_id jauh lebih rendah dari oldest_active_transaction_id, dan nilai current_abort_transaction_count besar, kemungkinan ada transaksi lama yang dibatalkan yang mencegah pembersihan PVS.

Untuk mengatasi penundaan pembersihan PVS karena sejumlah besar transaksi yang dibatalkan, pertimbangkan hal berikut:

  • Jika Anda menggunakan SQL Server 2022 (16.x), tingkatkan nilai konfigurasi server ADR Cleaner Thread Count. Untuk informasi selengkapnya, lihat Konfigurasi Server: Jumlah Utas Pembersih ADR.
  • Jika memungkinkan, hentikan beban kerja untuk membiarkan pembersih versi membuat kemajuan.
  • Tinjau aplikasi untuk mengidentifikasi dan menyelesaikan masalah tingkat pembatalakan transaksi yang tinggi. Pembatalan mungkin berasal dari tingkat kebuntuan yang tinggi, kunci duplikat, pelanggaran batasan, atau batas waktu kueri.
  • Optimalkan beban kerja untuk mengurangi kunci yang tidak kompatibel dengan kunci IX tingkat objek atau tingkat partisi yang diperlukan oleh pembersih PVS. Untuk informasi selengkapnya, lihat Kompatibilitas Kunci.
  • Jika menggunakan SQL Server, nonaktifkan ADR hanya untuk keadaan darurat guna mengontrol ukuran PVS. Lihat Nonaktifkan ADR.
  • Jika Anda menggunakan SQL Server dan pembersihan transaksi yang dibatalkan belum selesai dengan sukses baru-baru ini, periksa log error untuk pesan yang melaporkan masalah VersionCleaner.
  • Jika ukuran PVS tidak berkurang seperti yang diharapkan bahkan setelah proses pembersihan selesai, periksa kolom pvs_off_row_page_skipped_oldest_aborted_xdesid tersebut. Nilai yang besar menunjukkan bahwa ruang masih digunakan oleh versi baris dari transaksi-transaksi yang dibatalkan.

Mengontrol ukuran PVS

Jika Anda memiliki beban kerja dengan volume pernyataan DML yang tinggi (INSERT, UPDATE, DELETE, MERGE), seperti OLTP volume tinggi, dan mengamati bahwa ukuran PVS besar, Anda mungkin perlu meningkatkan nilai konfigurasi server ADR Cleaner Thread Count untuk menjaga ukuran PVS tetap terkendali. Untuk informasi selengkapnya, lihat Konfigurasi Server: Jumlah Utas Pembersih ADR, yang tersedia mulai dari SQL Server 2022 (16.x).

Di SQL Server 2019 (15.x), atau jika meningkatkan nilai konfigurasi ADR Cleaner Thread Count tidak membantu mengurangi ukuran PVS dengan cukup, beban kerja mungkin memerlukan periode istirahat/pemulihan untuk proses pembersihan PVS untuk mengklaim kembali ruang.

Untuk mengaktifkan pembersihan PVS secara manual di antara beban kerja atau selama jendela pemeliharaan, gunakan prosedur tersimpan sistem sys.sp_persistent_version_cleanup.

Contohnya:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Transaksi aktif mungkin mencegah proses pembersihan PVS dimulai. Jika ini terjadi, sesi yang menjalankan prosedur tersimpan sys.sp_persistent_version_cleanup menunggu dengan jenis tunggu PVS_CLEANUP_LOCK. Anda dapat menunggu transaksi selesai, atau Anda dapat mempertimbangkan untuk membunuh sesi pemblokir dengan transaksi aktif, jika memungkinkan.

Menangkap kegagalan pembersihan

Dimulai dengan SQL Server 2022 (16.x), pesan pembersihan PVS penting dicatat dalam log kesalahan. Statistik pembersihan juga dilaporkan oleh tx_mtvc2_sweep_statsextended event.

Masalah yang diketahui

  • Dalam SQL Server 2025 (17.x), ketika ADR dalam tempdb diaktifkan dan tabel sementara dibuat dan dihapus (atau dipangkas) pada tingkat yang tinggi, throughput beban kerja mungkin berkurang secara substansial karena kontensi kait pada tabel sistem sys.sysseobjvalues. Masalah ini sedang diselidiki. Perbaikan direncanakan untuk rilis selanjutnya.

Konten terkait

  • Last updated on