Delen via

Versneld databaseherstel bewaken en problemen oplossen

  • Artikel

van toepassing op: SQL Server 2019 (15.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Dit artikel helpt u bij het bewaken, diagnosticeren en oplossen van problemen met versneld databaseherstel (ADR) in SQL Server 2019 (15.x) en hoger, Azure SQL Managed Instance, Azure SQL Database en SQL Database in Microsoft Fabric.

Onderzoek de grootte van de PVS.

Gebruik de sys.dm_tran_persistent_version_store_stats DMV om te bepalen of de grootte van het permanente versiearchief (PVS) groter is dan verwacht.

In de volgende voorbeelddiagnosequery ziet u de informatie over de huidige PVS-grootte, de opschoonprocessen en andere details in alle databases waar de PVS-grootte groter is dan nul:

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;

Controleer de kolom pvs_percent_of_database_size om de grootte van de PVS ten opzichte van de totale databasegrootte weer te geven. Let op een verschil tussen de typische PVS-grootte en de basislijnen die worden gezien tijdens typische perioden van toepassingsactiviteit. PVS wordt beschouwd als groot als deze aanzienlijk groter is dan de basislijn of als deze dicht bij 50% van de databasegrootte ligt.

Als de grootte van PVS niet afneemt, gebruikt u de volgende stappen voor probleemoplossing om de reden voor grote PVS-grootte te vinden en op te lossen.

Tip

Kolommen die worden vermeld in de volgende stappen voor probleemoplossing, verwijzen naar de kolommen in de resultatenset van de diagnostische query in deze sectie.

Grote PVS-grootte kan worden veroorzaakt door een van de volgende redenen:

Controleren op langlopende actieve transacties

Langlopende actieve transacties kunnen PVS-opschoning voorkomen in databases waarvoor ADR is ingeschakeld. Controleer de begintijd van de oudste actieve transactie met behulp van de kolom oldest_transaction_begin_time. Gebruik de volgende voorbeeldquery om langlopende transacties te vinden. U kunt drempelwaarden instellen voor de transactieduur en het aantal gegenereerde transactielogboeken:

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;

Zodra de sessies zijn geïdentificeerd, overweeg dan de sessie te beëindigen, indien toegestaan. Controleer de toepassing om de aard van de problematische transacties te bepalen om het probleem in de toekomst te voorkomen.

Zie voor meer informatie over het oplossen van langdurige query's:

Controleren op langlopende actieve momentopnamescans

Langdurige actieve momentopnamescans kunnen PVS-opschoning voorkomen in databases waarvoor ADR is ingeschakeld. Instructies die gebruikmaken van READ COMMITTED RCSI (snapshot isolation) of SNAPSHOTisolatieniveaus ontvangen tijdstempels op het niveau van het exemplaar. Een momentopnamescan gebruikt de tijdstempel om de zichtbaarheid van versierijen voor de RCSI of SNAPSHOT transactie te bepalen. Elke instructie die RCSI gebruikt, heeft een eigen tijdstempel, terwijl SNAPSHOT isolatie een tijdstempel op transactieniveau heeft.

Deze tijdstempels voor transacties op exemplaarniveau worden zelfs gebruikt in transacties met één database, omdat elke transactie kan worden gepromoveerd naar een transactie tussen databases. Momentopnamescans kunnen daarom PVS-opschoning in elke database binnen dezelfde database-engine-instantie voorkomen. Ook wanneer ADR niet is ingeschakeld, kunnen momentopnamescans het opschonen van het versiearchief in tempdbvoorkomen. Als gevolg hiervan kan PVS groter worden wanneer langlopende transacties die gebruikmaken van SNAPSHOT of RCSI aanwezig zijn.

In de kolom pvs_off_row_page_skipped_min_useful_xts wordt het aantal pagina's weergegeven dat tijdens het opschonen is overgeslagen vanwege een lange momentopnamescan. Als in deze kolom een grote waarde wordt weergegeven, betekent dit dat een lange momentopnamescan PVS-opschoning verhindert.

Gebruik de volgende voorbeeldquery om de sessies te vinden met de langlopende SNAPSHOT- of RCSI-transactie:

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;

Om vertragingen bij het opschonen van PVS te voorkomen:

  • Overweeg de lange actieve transactiesessie te beëindigen die PVS-opschoning vertraagt, indien mogelijk.
  • Stem lange queries af om de duur te verminderen.
  • Controleer de toepassing om de aard van de problematische actieve momentopnamescan te bepalen. Overweeg een ander isolatieniveau, zoals READ COMMITTED, in plaats van SNAPSHOT of RCSI voor langlopende query's die PVS-opschoning vertragen. Dit probleem treedt vaker op met het SNAPSHOT isolatieniveau.
  • Overweeg om databases met langlopende transacties die SNAPSHOT-isolatie of RCSI gebruiken, uit de elastische pool van Azure SQL Database te verplaatsen, om opschoningsvertragingen van PVS in andere databases binnen dezelfde pool te voorkomen.

Controleer op langlopende queries op secundaire replica's

Als de database secundaire replica's heeft, controleer of het secundaire lage watermerk voortschrijdt.

Een grote waarde in de kolom pvs_off_row_page_skipped_low_water_mark kan een indicatie zijn van een opschoonvertraging vanwege een langlopende query op een secundaire replica. Naast het opschonen van PVS kan een langlopende query op een secundaire replica ook spookopruimingbevatten.

U kunt de volgende voorbeeldquery's op de primaire replica gebruiken om te bepalen of langlopende query's op secundaire replica's pvS-opschoning kunnen verhinderen. Als een schrijfworkload wordt uitgevoerd op de primaire replica, maar de waarde in de kolom low_water_mark_for_ghosts niet toeneemt van de ene uitvoering van de voorbeeldquery naar de volgende, dan kunnen PVS en ghost-opschoning worden vertraagd door een langlopende query op een secundaire replica.

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;

Zie de beschrijving van de kolom low_water_mark_for_ghosts in sys.dm_hadr_database_replica_statesvoor meer informatie.

Maak verbinding met elke leesbare secundaire replica, zoek de sessie met de langlopende query en overweeg de sessie te beëindigen, indien toegestaan. Voor meer informatie, zie Trage query’s vinden.

Controleren op een groot aantal afgebroken transacties

Controleer de kolommen aborted_version_cleaner_start_time en aborted_version_cleaner_end_time om te zien of het opschonen van de laatste afgebroken transactie is voltooid. De oldest_aborted_transaction_id moet hoger worden verplaatst nadat het opschonen van de afgebroken transactie is voltooid. Als de oldest_aborted_transaction_id veel lager is dan oldest_active_transaction_iden de current_abort_transaction_count waarde groot is, is er waarschijnlijk een oude afgebroken transactie die PVS-opschoning verhindert.

Als u de vertraging van PVS-opschoning wilt oplossen vanwege een groot aantal afgebroken transacties, moet u rekening houden met het volgende:

  • Als u SQL Server 2022 (16.x) gebruikt, verhoogt u de waarde van de ADR Cleaner Thread Count-serverconfiguratie. Zie Serverconfiguratie: ADR Cleaner Thread Countvoor meer informatie.
  • Stop indien mogelijk de workload om de versiereiniger voortgang te laten maken.
  • Controleer de applicatie om het probleem met het hoge transactiestoppercentage te identificeren en op te lossen. De aborts kunnen afkomstig zijn van een hoog aantal deadlocks, dubbele sleutels, schendingen van beperkingen of query time-outs.
  • Optimaliseer de werkbelasting om vergrendelingen te verminderen die niet compatibel zijn met het object- of partitieniveau IX vergrendelingen die vereist zijn voor de PVS-schoner. Zie voor meer informatie over vergrendelingscompatibiliteit.
  • Als u SQL Server gebruikt, schakelt u ADR uit als een stap voor alleen noodgevallen om de PVS-grootte te beheren. Zie Schakel ADRuit.
  • Als u SQL Server gebruikt en als het opschonen van de afgebroken transactie niet onlangs is voltooid, controleert u het foutenlogboek voor berichten die VersionCleaner problemen melden.
  • Als de PVS-grootte niet wordt verkleind zoals verwacht, zelfs nadat een opschoonactie is voltooid, controleert u de pvs_off_row_page_skipped_oldest_aborted_xdesid kolom. Grote waarden geven aan dat er nog steeds ruimte wordt gebruikt door rijversies van afgebroken transacties.

PVS-grootte beheren

Als u een workload hebt met een groot aantal DML-instructies (INSERT, UPDATE, DELETE, MERGE), zoals OLTP met een groot volume, en u merkt dat de PVS-grootte groot is, moet u mogelijk de waarde van de ADR Cleaner Thread Count-serverconfiguratie verhogen om de PVS-grootte onder controle te houden. Zie voor meer informatie Serverconfiguratie: ADR Cleaner Thread Count, die beschikbaar is vanaf SQL Server 2022 (16.x).

In SQL Server 2019 (15.x) of als het verhogen van de waarde van ADR Cleaner Thread Count-configuratie de PVS-grootte niet voldoende helpt verminderen, kan de workload een periode van rust/herstel vereisen voor het PVS-opschoonproces om ruimte vrij te maken.

Als u het PVS-opschoonproces handmatig wilt activeren tussen werkbelastingen of tijdens onderhoudsvensters, gebruikt u de door het systeem opgeslagen procedure sys.sp_persistent_version_cleanup.

Bijvoorbeeld:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Het vastleggen van opschoonfouten

Vanaf SQL Server 2022 (16.x) worden belangrijke PVS-opschoonberichten vastgelegd in het foutenlogboek. Opschoonstatistieken worden ook gerapporteerd door de uitgebreide gebeurtenistx_mtvc2_sweep_stats.

Verwante inhoud