Sdílet prostřednictvím

Monitorování a řešení potíží s akcelerovaným obnovením databáze

  • Článek

platí pro: SQL Server 2019 (15.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Tento článek vám pomůže monitorovat, diagnostikovat a řešit problémy s zrychleným obnovením databáze (ADR) v SQL Serveru 2019 (15.x) a novějším, službě Azure SQL Managed Instance, Azure SQL Database a databází SQL v Microsoft Fabric.

Prozkoumejte velikost systému PVS

Pomocí sys.dm_tran_persistent_version_store_stats DMV zjistěte, jestli je velikost trvalého úložiště verzí větší než se čekalo.

Následující ukázkový diagnostický dotaz ukazuje informace o aktuální velikosti PVS, procesech vyčištění a dalších podrobnostech ve všech databázích, kde je velikost PVS větší než nula:

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;

Zkontrolujte sloupec pvs_percent_of_database_size a podívejte se na velikost PVS vzhledem k celkové velikosti databáze. Všimněte si rozdílu mezi typickou velikostí PVS a výchozími hodnotami, které se objevují během obvyklých období aktivity aplikace. PVS se považuje za velký, pokud je výrazně větší než referenční hodnota nebo je blízko 50% velikosti databáze.

Pokud se velikost PVS nezmenšuje, vyhledejte a vyřešte příčinu velké velikosti PVS pomocí následujících kroků pro řešení potíží.

Tip

Sloupce uvedené v následujících krocích řešení potíží odkazují na sloupce v sadě výsledků diagnostického dotazu v této části.

Velká velikost PVS může být způsobena některým z následujících důvodů:

Kontrola dlouhotrvajících aktivních transakcí

Dlouhotrvající aktivní transakce můžou zabránit vyčištění PVS v databázích, které mají povolenou službu ADR. Pomocí sloupce oldest_transaction_begin_time zkontrolujte počáteční čas nejstarší aktivní transakce. K vyhledání dlouhotrvajících transakcí použijte následující ukázkový dotaz. Můžete nastavit prahové hodnoty pro dobu trvání transakce a množství vygenerovaného transakčního protokolu:

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;

S identifikovanými relacemi zvažte ukončení těchto relací, pokud je to povoleno. Zkontrolujte aplikaci a zjistěte povahu problematických transakcí, abyste se vyhnuli problému v budoucnu.

Další informace o řešení potíží s dlouhotrvajícími dotazy najdete v tématu:

Kontrola dlouhotrvajících aktivních skenů snímků

Dlouhodobé aktivní snímkové prohledávání může zabránit procesu čištění PVS v databázích s aktivovaným ADR. Příkazy používající READ COMMITTED izolaci snímků (RCSI) nebo SNAPSHOTúrovně izolace přijímají časové razítka na úrovni instance. Snímací kontrola používá časové razítko k určení viditelnosti řádku verze pro transakci RCSI nebo SNAPSHOT. Každý příkaz používající RCSI má vlastní časové razítko, zatímco SNAPSHOT izolace má časové razítko na úrovni transakce.

Tato časová razítka transakcí na úrovni instance se používají i v transakcích s jednou databází, protože každá transakce může být povýšena na transakci mezi databázemi. Skenování snímků může proto zabránit vyčištění PVS v jakékoli databázi na stejné instanci databázového systému. Podobně i v případě, že není zapnuté ADR, můžou snímkové kontroly zabránit vyčištění úložiště verzí v tempdb. V důsledku toho může PVS zvětšit velikost, pokud jsou přítomny dlouhotrvající transakce, které používají SNAPSHOT nebo RCSI.

Sloupec pvs_off_row_page_skipped_min_useful_xts zobrazuje počet stránek vynechaných během čištění kvůli dlouhé kontrole snímků. Pokud tento sloupec zobrazuje velkou hodnotu, znamená to, že dlouhý snímek brání vyčištění PVS.

Pomocí následujícího ukázkového dotazu vyhledejte sezení s dlouhotrvajícími transakcemi SNAPSHOT nebo RCSI.

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;

Jak zabránit zpoždění čištění PVS:

  • Pokud je to možné, zvažte ukončení dlouhé seance aktivní transakce, která zpožďuje pročištění PVS.
  • Vylaďte dlouhotrvající dotazy, aby se zkrátila doba trvání dotazu.
  • Zkontrolujte aplikaci a zjistěte povahu problematické aktivní kontroly snímků. Zvažte jinou úroveň izolace, například READ COMMITTEDmísto SNAPSHOT nebo RCSI, pro dlouhotrvající dotazy, které způsobují zpoždění při vyčišťování PVS. K tomuto problému dochází častěji s úrovní izolace SNAPSHOT.
  • V elastických fondech Azure SQL Database zvažte přesun databází s dlouhotrvajícími transakcemi používající SNAPSHOT izolaci nebo RCSI z daného elastického fondu, aby se předešlo zpoždění při čištění PVS v jiných databázích ve stejném fondu.

Kontrola dlouhotrvajících dotazů na sekundárních replikách

Pokud databáze obsahuje sekundární repliky, zkontrolujte, zda se sekundární dolní hranice posouvá.

Velká hodnota ve sloupci pvs_off_row_page_skipped_low_water_mark může naznačovat zpoždění při vyčištění kvůli dlouhotrvajícímu dotazu na sekundární replice. Kromě zdržení čištění PVS může dlouhotrvající dotaz na sekundární replice také zpozdit čištění duchů.

Pomocí následujících ukázkových dotazů na primární repliku můžete zjistit, jestli dlouhotrvající dotazy na sekundární replice můžou bránit vyčištění PVS. Pokud je úloha zápisu spuštěná na primární replice, ale hodnota ve sloupci low_water_mark_for_ghosts se nezvyšuje z jednoho spuštění ukázkového dotazu na další, může být PVS a stínové čištění zdrženo dlouho běžícím dotazem na sekundární replice.

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;

Další informace najdete v popisu sloupce low_water_mark_for_ghosts v sys.dm_hadr_database_replica_states.

Připojte se ke každé čitelné sekundární replice, vyhledejte relaci s dlouhotrvajícím dotazem a zvažte ukončení relace, pokud je tato relace povolená. Další informace najdete v tématu Vyhledání pomalých dotazů.

Kontrola velkého počtu přerušených transakcí

Zkontrolujte sloupce aborted_version_cleaner_start_time a aborted_version_cleaner_end_time a zkontrolujte, jestli se dokončilo poslední přerušené vyčištění transakce. oldest_aborted_transaction_id by se mělo posunout výše, jakmile dojde k dokončení vyčištění neúspěšné transakce. Pokud je oldest_aborted_transaction_id mnohem nižší než oldest_active_transaction_ida hodnota current_abort_transaction_count je velká, pravděpodobně dojde k přerušení staré transakce, která brání vyčištění PVS.

Pokud chcete vyřešit zpoždění čištění PVS kvůli velkému počtu přerušených transakcí, zvažte následující:

  • Pokud používáte SQL Server 2022 (16.x), zvyšte hodnotu konfigurace ADR Cleaner Thread Count serveru. Další informace, naleznete v tématu Konfigurace serveru: ADR Cleaner Thread Count.
  • Pokud je to možné, zastavte zatížení a umožněte čisticímu systému verzí pokročit.
  • Zkontrolujte aplikaci a zjistěte a vyřešte problém s vysokou četností přerušení transakcí. Přerušení může pocházet z vysoké míry zablokování, duplicitních klíčů, porušení omezení nebo vypršení časového limitu dotazu.
  • Optimalizujte pracovní zátěž a snižte zámky, které nejsou kompatibilní s objektovou úrovní nebo úrovní oddílu IX, jež vyžaduje čistič PVS. Další informace naleznete v tématu Kompatibilita zámků.
  • Pokud používáte SQL Server, zakažte ADR jako krok jen pro nouzové situace, abyste mohli řídit velikost PVS. Viz Zakázat ADR.
  • Pokud používáte SQL Server a v nedávné době se nepovedlo úspěšně dokončit vyčištění transakce, zkontrolujte v protokolu chyb zprávy, které hlásí problémy VersionCleaner.
  • Pokud se velikost PVS nezmenší podle očekávání i po dokončení čištění, zkontrolujte sloupec pvs_off_row_page_skipped_oldest_aborted_xdesid. Velké hodnoty naznačují, že prostor je nadále využíván pro verze řádků ze zrušených transakcí.

Řídit velikost PVS

Pokud máte úlohu s velkým objemem příkazů DML (INSERT, UPDATE, DELETE, MERGE), jako je OLTP s velkým objemem a zjistíte, že velikost PVS je velká, možná budete muset zvýšit hodnotu konfigurace serveru ADR Cleaner Thread Count, aby byla velikost PVS pod kontrolou. Další informace naleznete v tématu Konfigurace serveru: ADR Cleaner Thread Count, která je k dispozici od SQL Serveru 2022 (16.x).

V SYSTÉMU SQL Server 2019 (15.x) nebo pokud zvýšení hodnoty konfigurace ADR Cleaner Thread Count nepomůže dostatečně snížit velikost PVS, může úloha vyžadovat pro proces vyčištění PVS dobu odpočinku a obnovení, aby se uvolnilo místo.

Chcete-li aktivovat proces čištění PVS ručně mezi úlohami, nebo během údržby, použijte systémovou uloženou proceduru sys.sp_persistent_version_cleanup.

Například:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Aktivní transakce může zabránit spuštění procesu čištění PVS. Pokud k tomu dojde, relace, která spouští uloženou proceduru sys.sp_persistent_version_cleanup, čeká s typem čekání PVS_CLEANUP_LOCK. Můžete počkat na dokončení transakce, nebo můžete zvážit ukončení blokující relace s aktivní transakcí, pokud je to možné.

Zachytit selhání při čištění

Od verze SQL Server 2022 (16.x) se do protokolu chyb zaznamenávají významné zprávy o čištění PVS. Statistiky čištění jsou hlášeny také prostřednictvím rozšířené události tx_mtvc2_sweep_stats.

Související obsah