Memecahkan masalah pemulihan database yang dipercepat

  • Artikel

Berlaku untuk: SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed Instance

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

Memeriksa penyimpanan versi persisten (PVS)

Manfaatkan DMV sys.dm_tran_persistent_version_store_stats untuk mengidentifikasi apakah ukuran PVS pemulihan database yang dipercepat (ADR) tumbuh lebih besar dari yang diharapkan, lalu untuk menentukan faktor mana yang mencegah pembersihan penyimpanan versi persisten (PVS).

Disertakan dalam contoh skrip berikut adalah kolom sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, yang ditambahkan di SQL Server 2022 (16.x) dan berisi jumlah halaman yang dilewati untuk diklaim kembali karena transaksi terlama yang dibatalkan. Jika pembersih versi lambat atau tidak valid, ini akan mencerminkan berapa banyak halaman yang harus disimpan untuk transaksi yang dibatalkan.

Kueri sampel memperlihatkan semua informasi tentang proses pembersihan dan memperlihatkan ukuran PVS saat ini, transaksi terlama yang dibatalkan, dan detail lainnya:

SELECT
 db_name(pvss.database_id) AS DBName,
 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_pct_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,
 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 -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [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.database_id = DB_ID();

  1. Periksa pvs_pct_of_database_size ukuran, perhatikan perbedaan apa pun dari biasanya, dibandingkan dengan garis besar selama periode aktivitas aplikasi lainnya. PVS dianggap besar jika secara signifikan lebih besar dari garis besar atau jika mendekati 50% dari ukuran database. Gunakan langkah-langkah berikut sebagai bantuan pemecahan masalah untuk PVS yang besar.

  2. Transaksi aktif dan berjalan lama dalam database apa pun di mana ADR diaktifkan dapat mencegah pembersihan PVS. Ambil oldest_active_transaction_id dan periksa apakah transaksi ini telah aktif untuk waktu yang lama dengan mengkueri sys.dm_tran_database_transactions berdasarkan ID transaksi. Periksa transaksi aktif yang berjalan lama dengan kueri seperti sampel di bawah ini, yang menyatakan variabel untuk mengatur ambang batas untuk durasi atau jumlah log:

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.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 transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.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 = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    Dengan sesi yang diidentifikasi, pertimbangkan untuk membunuh sesi, jika diizinkan. Selain itu, tinjau aplikasi untuk menentukan sifat transaksi aktif yang bermasalah.

    Untuk informasi selengkapnya tentang pemecahan masalah kueri yang berjalan lama, lihat:

  1. Pembersihan versi persisten dapat ditahan karena pemindaian rekam jepret aktif yang lama. Pernyataan yang menggunakan isolasi rekam jepret yang diterapkan baca (RCSI) atau tingkat isolasi SNAPSHOT menerima tanda waktu tingkat instans. Pemindaian rekam jepret menggunakan tanda waktu untuk memutuskan visibilitas baris untuk transaksi RCSI atau SNAPSHOT di PVS tempat pemulihan database yang dipercepat diaktifkan. 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 dapat dipromosikan ke transaksi lintas database. Oleh karena itu, pemindaian rekam jepret dapat mencegah pembersihan rekaman di ADR PVS, atau ketika ADR tidak ada, di penyimpanan tempdb versi. Oleh karena pelacakan versi ini, transaksi jangka panjang menggunakan SNAPSHOT atau RCSI dapat menyebabkan PVS ADR menunda pembersihan dalam database dalam instans, menyebabkan PVS ADR tumbuh dalam ukuran.

    Dalam kueri pemecahan masalah asli di bagian atas artikel ini, pvs_off_row_page_skipped_min_useful_xts nilai menunjukkan jumlah halaman yang dilewati untuk diklaim kembali karena pemindaian rekam jepret yang panjang. Jika pvs_off_row_page_skipped_min_useful_xts menunjukkan nilai yang lebih besar dari normal, itu berarti ada pemindaian rekam jepret panjang yang mencegah pembersihan PVS.

    Kueri sampel ini dapat digunakan untuk memutuskan sesi mana yang bermasalah:

    SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;

    Untuk mencegah penundaan pembersihan PVS:

    1. Pertimbangkan untuk membunuh sesi transaksi aktif panjang yang menunda pembersihan PVS, jika memungkinkan. Transaksi jangka panjang dalam database apa pun di mana ADR diaktifkan dapat menunda pembersihan ADR PVS.
    2. Menyetel kueri yang berjalan lama untuk mengurangi durasi kueri dan kunci yang diperlukan. Untuk informasi dan panduan selengkapnya, lihat Memahami dan mengatasi pemblokiran di SQL Server atau Memahami dan mengatasi masalah pemblokiran Azure SQL Database.
    3. Tinjau aplikasi untuk menentukan sifat pemindaian rekam jepret aktif yang bermasalah. Pertimbangkan tingkat isolasi yang berbeda, seperti READ COMMITTED, alih-alih SNAPSHOT atau READ COMMITTED SNAPSHOT untuk kueri jangka panjang yang menunda pembersihan ADR PVS. Masalah ini terjadi lebih sering dengan tingkat isolasi SNAPSHOT.
    4. Masalah ini dapat terjadi di SQL Server, Azure SQL Managed Instance, dan kumpulan elastis Azure SQL Database, tetapi tidak dalam database Azure SQL singleton. Di kumpulan elastis Azure SQL Database, pertimbangkan untuk memindahkan database dari kumpulan elastis yang memiliki kueri jangka panjang menggunakan tingkat isolasi READ COMMIT SNAPSHOT atau SNAPSHOT.

  2. Ketika ukuran PVS tumbuh karena transaksi yang berjalan lama pada replika primer atau sekunder, selidiki kueri yang berjalan lama dan atasi penyempitan. sys.dm_tran_aborted_transactions DMV menunjukkan semua transaksi yang dibatalkan. Untuk informasi selengkapnya, lihat sys.dm_tran_aborted_transactions (Transact-SQL). Kolom nest_aborted menunjukkan bahwa transaksi dilakukan, tetapi ada bagian yang dibatalkan (titik penyimpanan atau transaksi berlapis) yang dapat memblokir proses pembersihan PVS.

  3. Jika database adalah bagian dari grup ketersediaan, periksa secondary_low_water_mark. Ini sama dengan yang dilaporkan low_water_mark_for_ghosts oleh sys.dm_hadr_database_replica_states. Kueri sys.dm_hadr_database_replica_states untuk melihat apakah salah satu replika menahan nilai ini, karena ini juga akan mencegah pembersihan PVS. Pembersihan versi tertahan karena kueri baca pada sekunder yang dapat dibaca. Baik SQL Server lokal maupun Azure SQL DB mendukung sekunder yang dapat dibaca. sys.dm_tran_persistent_version_store_stats Dalam DMV, pvs_off_row_page_skipped_low_water_mark juga dapat memberikan indikasi penundaan replika sekunder. Untuk informasi selengkapnya, lihat sys.dm_tran_persistent_version_store_stats.

    Solusinya sama dengan penahanan pemindaian rekam jepret. Buka sekunder, temukan sesi yang mengeluarkan kueri panjang dan pertimbangkan untuk membunuh sesi, jika diizinkan. Perhatikan bahwa penangguhan sekunder tidak hanya berdampak pada pembersihan versi ADR, tetapi juga dapat mencegah pembersihan catatan hantu.

  4. Periksa min_transaction_timestamp (atau online_index_min_transaction_timestamp jika PVS online menahan) dan berdasarkan pemeriksaan sys.dm_tran_active_snapshot_database_transactions kolom tersebut transaction_sequence_num untuk menemukan sesi yang memiliki transaksi rekam jepret lama yang menahan pembersihan PVS.

  5. Jika tidak ada hal di atas yang berlaku, berarti pembersihan dipegang oleh transaksi yang dibatalkan. Periksa terakhir kali aborted_version_cleaner_last_start_time dan aborted_version_cleaner_last_end_time untuk melihat apakah pembersihan transaksi yang dibatalkan telah selesai. oldest_aborted_transaction_id harus bergerak lebih tinggi setelah pembersihan transaksi yang dibatalkan selesai. oldest_aborted_transaction_id Jika jauh lebih kecil dari oldest_active_transaction_id, dan current_abort_transaction_count memiliki nilai yang lebih besar, ada transaksi lama yang dibatalkan yang mencegah pembersihan PVS. Ke alamat:

    • Jika memungkinkan, hentikan beban kerja untuk membiarkan versi lebih bersih membuat kemajuan.
    • Optimalkan beban kerja untuk mengurangi penggunaan kunci tingkat objek.
    • Tinjau aplikasi untuk melihat masalah pembatalakan transaksi tinggi. Kebuntuan, kunci duplikat, dan pelanggaran batasan lainnya dapat memicu tingkat pembatalan yang tinggi.
    • Jika di SQL Server, nonaktifkan ADR sebagai langkah darurat-saja untuk mengontrol ukuran PVS dan membatalkan nomor transaksi. Lihat Menonaktifkan fitur ADR.

  6. Jika transaksi yang dibatalkan belum berhasil diselesaikan baru-baru ini, periksa log kesalahan untuk masalah pelaporan VersionCleaner pesan.

  7. Pantau log kesalahan SQL Server untuk entri 'PreallocatePVS'. Jika ada entri 'PreallocatePVS', maka ini berarti Anda mungkin perlu meningkatkan kemampuan ADR untuk melakukan pra-alokasi halaman untuk tugas latar belakang karena performa dapat ditingkatkan ketika utas latar belakang ADR melakukan pra-alokasi halaman yang cukup dan persentase alokasi PVS latar depan mendekati 0. Anda dapat menggunakan sp_configure 'ADR Preallocation Factor' untuk meningkatkan jumlah ini. Untuk informasi selengkapnya, lihat opsi konfigurasi server faktor pra-alokasi ADR.

Memulai proses pembersihan PVS secara manual

ADR tidak disarankan untuk lingkungan database dengan jumlah transaksi pembaruan/penghapusan yang tinggi, seperti OLTP volume tinggi, tanpa periode istirahat/pemulihan untuk proses pembersihan PVS untuk mengklaim kembali ruang.

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

EXEC sys.sp_persistent_version_cleanup [database_name];

Contohnya,

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Menangkap kegagalan pembersihan

Dimulai dengan SQL Server 2022 (16.x), SQL Server mencatat perilaku pembersihan ADR PVS di log kesalahan SQL Server. Biasanya ini akan menghasilkan peristiwa log baru yang direkam setiap 10 menit.

Baca juga

Langkah berikutnya