Solución de problemas de la recuperación acelerada de la base de datos

  • Artículo

Se aplica a: SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed Instance

Este artículo ayuda a los administradores a diagnosticar problemas con la recuperación acelerada de la base de datos (ADR) en SQL Server 2019 (15.x) y versiones posteriores, Azure SQL Managed Instance y Azure SQL Database.

Examen del almacén de versiones persistentes (PVS)

Aproveche la DMV sys.dm_tran_persistent_version_store_stats para identificar si el tamaño del PVS de recuperación acelerada de la base de datos (ADR) crece más allá de lo esperado y, luego, para determinar el factor que impide la limpieza del almacén de versiones persistentes (PVS).

La columna sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, incluida en el siguiente script de ejemplo, se agregó en SQL Server 2022 (16.x) y contiene el número de páginas omitidas para la reclamación debido a transacciones anuladas más antiguas. Si el limpiador de versiones es lento o se invalida, esto reflejará el número de páginas que se deben conservar para las transacciones anuladas.

En la consulta de ejemplo, se muestra toda la información sobre los procesos de limpieza y el tamaño actual del PVS, la transacción anulada más antigua y otros detalles:

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. Revise el tamaño de pvs_pct_of_database_size, observe cualquier diferencia con respecto al tamaño típico en comparación con las bases de referencia durante otros períodos de actividad de la aplicación. El PVS se considera grande si es considerablemente mayor que la línea de base o si está cerca del 50 % del tamaño de la base de datos. Los pasos siguientes pueden ayudarlo a solucionar problemas con un PVS de gran tamaño.

  2. Las transacciones activas y de larga duración en cualquier base de datos en la que ADR esté habilitada pueden impedir la limpieza del PVS. Recupere oldest_active_transaction_id y compruebe si esta transacción ha estado activa durante un tiempo prolongado mediante una consulta a sys.dm_tran_database_transactions en función del identificador de la transacción. Revise si hay transacciones activas de larga duración con una consulta como el ejemplo que se muestra a continuación, en el que se declaran variables a fin de establecer umbrales para la duración o la cantidad de registros:

    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 );

    Una vez que identifique las sesiones, considere la posibilidad de terminar la sesión, si se permite. Además, revise la aplicación para determinar la naturaleza de las transacciones activas problemáticas.

    Para obtener más información sobre cómo solucionar problemas de consultas de larga duración, consulte:

  1. Es posible que se impida la limpieza de las versiones persistentes debido a exámenes de instantáneas activos de larga duración. Las instrucciones que usan el aislamiento de instantánea de lectura confirmada (RCSI) o los niveles de aislamiento SNAPSHOT reciben marcas de tiempo de nivel de instancia. Un examen de instantáneas usa la marca de tiempo para decidir la visibilidad de fila de la transacción RCSI o SNAPSHOT en el PVS donde está habilitada la recuperación acelerada de la base de datos. Cada instrucción que usa RCSI tiene su propia marca de tiempo, mientras que el aislamiento SNAPSHOT tiene una marca de tiempo de nivel de transacción. Estas marcas de tiempo de transacción de nivel de instancia se usan incluso en transacciones de base de datos únicas, ya que la transacción se puede promover a una transacción entre bases de datos. Por lo tanto, los exámenes de instantáneas pueden evitar la limpieza de registros en el PVS de ADR o cuando ADR no está presente, en el almacén de versiones tempdb. Por lo tanto, debido a este seguimiento de versiones, las transacciones de larga duración que usan SNAPSHOT o RCSI pueden hacer que el PVS de ADR retrase la limpieza en la base de datos de la instancia, lo que hace que el PVS de ADR crezca en tamaño.

    En la consulta original para la solución de problemas al principio de este artículo, el valor pvs_off_row_page_skipped_min_useful_xts muestra la cantidad de páginas que se omiten para la recuperación debido a un examen de instantánea de larga duración. Si pvs_off_row_page_skipped_min_useful_xts muestra un valor mayor que el normal, significa que hay un examen de instantánea de larga duración que impide la limpieza del PVS.

    Esta consulta de ejemplo se puede usar para determinar cuál es la sesión problemática:

    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;

    Para evitar retrasos en la limpieza de PVS:

    1. Considere la posibilidad de eliminar la sesión de transacción activa prolongada que retrasa la limpieza de PVS, si es posible. Las transacciones de larga duración en cualquier base de datos en la que ADR esté habilitado pueden retrasar la limpieza de PVS de ADR.
    2. Ajuste las consultas de larga duración para reducir la duración de la consulta y los bloqueos necesarios. Para obtener más información, consulte Descripción y resolución de problemas de bloqueo en SQL Server o Descripción y resolución de problemas de bloqueo de Azure SQL Database.
    3. Revise la aplicación para determinar la naturaleza del examen de instantánea activo problemático. Considere un nivel de aislamiento diferente, como READ COMMITTED, en lugar de SNAPSHOT o READ COMMITTED SNAPSHOT para consultas de larga duración que retrasan la limpieza de PVS de ADR. Este problema se produce con más frecuencia con el nivel de aislamiento SNAPSHOT.
    4. Este problema puede producirse en SQL Server, Azure SQL Managed Instance y grupos elásticos de Azure SQL Database, pero no en bases de datos de Azure SQL singleton. En los grupos elásticos de Azure SQL Database, considere la posibilidad de mover bases de datos del grupo elástico que tienen consultas de larga duración mediante niveles de aislamiento READ COMMIT SNAPSHOT o SNAPSHOT.

  2. Si el tamaño del PVS crece debido a transacciones de larga duración en réplicas principales o secundarias, investigue las consultas de larga duración y solucione el cuello de botella. En la DMV sys.dm_tran_aborted_transactions, se muestran todas las transacciones anuladas. Para más información, consulte sys.dm_tran_aborted_transactions (Transact-SQL). En la columna nest_aborted, se indica que se confirmó la transacción, pero que hay partes anuladas (puntos de retorno o transacciones anidadas) que pueden bloquear el proceso de limpieza del almacén de versiones persistentes.

  3. Si la base de datos forma parte de un grupo de disponibilidad, compruebe secondary_low_water_mark. Es el mismo que el low_water_mark_for_ghosts indicado por sys.dm_hadr_database_replica_states. Consulte sys.dm_hadr_database_replica_states para ver si una de las réplicas está reteniendo este valor atrás, ya que esto también evitará la limpieza del PVS. Es posible que se impida la limpieza de las versiones debido a consultas de lectura en réplicas secundarias legibles. Tanto SQL Server en el entorno local como Azure SQL DB admiten las réplicas secundarias legibles. En la DMV sys.dm_tran_persistent_version_store_stats, pvs_off_row_page_skipped_low_water_mark también puede proporcionar indicaciones de un retraso en la réplica secundaria. Para más información, consulte sys.dm_tran_persistent_version_store_stats.

    La solución es la misma que para la demora debido al examen de instantánea. Vaya a las réplicas secundarias, busque la sesión que emite la consulta de larga duración y considere la posibilidad de terminar la sesión, si se permite. Tenga en cuenta que la retención en una réplica secundaria no solo afectará la limpieza de las versiones de ADR, sino que también impedirá la limpieza de los registros fantasmas.

  4. Compruebe min_transaction_timestamp (o online_index_min_transaction_timestamp si el PVS en línea está en mantenimiento) y, en función de eso, compruebe sys.dm_tran_active_snapshot_database_transactions para que la columna transaction_sequence_num encuentre la sesión que tiene la transacción de instantáneas antigua que mantiene la limpieza del PVS.

  5. Si no se aplica ninguno de los anteriores, significa que la limpieza se mantiene mediante transacciones anuladas. Compruebe por última vez aborted_version_cleaner_last_start_time y aborted_version_cleaner_last_end_time para ver si se ha completado la limpieza de la transacción anulada. oldest_aborted_transaction_id debería moverse más arriba después de completarse la limpieza de la transacción anulada. Si el tamaño de oldest_aborted_transaction_id es mucho más pequeño que el de oldest_active_transaction_id y current_abort_transaction_count tiene un valor alto, hay una transacción anulada antigua que impide la limpieza del PVS. Haga lo siguiente:

    • Si es posible, detenga la carga de trabajo para permitir que continúe la limpieza de las versiones.
    • Optimice la carga de trabajo para reducir el uso de bloqueos en el nivel de objeto.
    • Revise la aplicación en busca de problemas con una gran cantidad de anulaciones de transacciones. El interbloqueo, la clave duplicada y otras infracciones de restricción pueden desencadenar una tasa alta de anulación.
    • Si usa SQL Server, deshabilite la recuperación acelerada de la base de datos como un paso solo de emergencia para controlar tanto el tamaño del PVS como la cantidad de transacciones anuladas. Consulte Para deshabilitar la característica ADR.

  6. Si la transacción anulada no se completó correctamente, consulte en el registro de errores si hay mensajes que notifican problemas de VersionCleaner.

  7. Supervise el registro de errores de SQL Server para ver si hay entradas "PreallocatePVS". Si es así, esto significa que es posible que tenga que aumentar la capacidad de la ADR para asignar previamente páginas para tareas en segundo plano, ya que el rendimiento se puede mejorar cuando el subproceso en segundo plano de la ADR asigna previamente páginas suficientes y el porcentaje de asignaciones del PVS en primer plano es cercano a 0. Puede usar sp_configure 'ADR Preallocation Factor' para aumentar esta cantidad. Para más información, consulte Opción de configuración del factor de asignación previa de ADR.

Inicio manual del proceso de limpieza del PVS

ADR no se recomienda para entornos de base de datos con un alto recuento de transacciones de actualizaciones o eliminaciones, como OLTP de gran volumen, sin un período de reposo/recuperación para que el proceso de limpieza de PVS reclame espacio.

Para activar el proceso de limpieza del almacén de versiones persistentes manualmente entre cargas de trabajo o durante las ventanas de mantenimiento, use el procedimiento almacenado del sistema sys.sp_persistent_version_cleanup.

EXEC sys.sp_persistent_version_cleanup [database_name];

Por ejemplo,

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Captura de errores de limpieza

A partir de SQL Server 2022 (16.x), SQL Server registra el comportamiento de limpieza de PVS de ADR en el registro de errores de SQL Server. Por lo general, esto daría lugar a un evento de registro nuevo registrado cada 10 minutos.

Consulte también

Pasos siguientes