Compartir vía

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

  • Artículo

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores de Azure SQL Database Azure SQL database Instancia administradaSQL Databaseen Microsoft Fabric

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

Examinar el tamaño del PVS

Utilice la DMV sys.dm_tran_persistent_version_store_stats para identificar si el tamaño del almacén de versiones persistente (PVS) es mayor de lo esperado.

En la consulta de ejemplo siguiente se muestra la información sobre el tamaño actual de PVS, los procesos de limpieza y otros detalles:

SELECT  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_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
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();

Compruebe la columna pvs_pct_of_database_size para ver el tamaño del PVS en relación con el tamaño total de la base de datos. Tenga en cuenta cualquier diferencia respecto al tamaño típico de PVS en comparación con las líneas base que se ven durante otros períodos de actividad de la aplicación. PVS se considera grande si es significativamente mayor que la línea base o si está cerca de 50% del tamaño de la base de datos. Siga estos pasos de solución de problemas para encontrar el motivo del gran tamaño de PVS.

Si el tamaño de PVS es mayor de lo esperado, verifique lo siguiente:

Comprobación de transacciones activas de larga duración

Las transacciones activas prolongadas pueden impedir la limpieza de PVS en bases de datos que tienen habilitado ADR. Compruebe la hora de inicio de la transacción activa más antigua mediante la columna oldest_transaction_begin_time. Para obtener más información sobre las transacciones de ejecución prolongada, use la siguiente consulta de ejemplo. Puede establecer umbrales para la duración de la transacción y la cantidad de registro de transacciones generado:

DECLARE @LongTxThreshold int = 1800;  /* 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 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 GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
        OR
        dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Con las sesiones identificadas, considere la posibilidad de cerrar las sesiones, si se permite. Revise la aplicación para determinar la naturaleza de las transacciones activas problemáticas para evitar el problema en el futuro.

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

Verificar los exámenes de instantáneas activas de larga duración

Los análisis de instantáneas activas de larga duración pueden impedir la limpieza de PVS en bases de datos que tienen habilitado ADR. Las instrucciones que usan READ COMMITTED aislamiento de instantáneas (RCSI) o SNAPSHOTniveles de aislamiento reciben marcas de tiempo a nivel de instancia. Un examen de instantáneas usa la marca de tiempo para decidir la visibilidad de las filas de versión para la transacción RCSI o SNAPSHOT. Cada instrucción que usa RCSI tiene su propia marca de tiempo, mientras tanto, 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 cualquier transacción podría solicitar una transacción entre bases de datos. Por lo tanto, los exámenes de instantáneas pueden evitar la limpieza de PVS en cualquier base de datos dentro de la misma instancia del motor de base de datos. Del mismo modo, cuando ADR no está habilitado, los análisis de instantáneas pueden prevenir la limpieza del almacén de versiones en tempdb. Como resultado, PVS puede crecer en tamaño cuando hay transacciones de larga duración que usan SNAPSHOT o RCSI.

En la consulta de solución de problemas al principio de este artículo, la columna pvs_off_row_page_skipped_min_useful_xts muestra el número de páginas omitidas para la recuperación debido a un análisis largo de instantáneas. Si esta columna muestra un valor mayor de lo normal, significa que un escaneo prolongado de instantáneas impide la limpieza de PVS.

Utilice la siguiente consulta de ejemplo para buscar la sesión con la transacción SNAPSHOT o RCSI de ejecución prolongada:

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:

  • Considere la posibilidad de eliminar la sesión de transacción activa prolongada que retrasa la limpieza de PVS, si es posible.
  • Ajuste las consultas de ejecución prolongada para reducir la duración de la consulta.
  • 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 RCSI para consultas de larga duración que retrasan la limpieza de PVS. Este problema se produce con más frecuencia con el nivel de aislamiento SNAPSHOT.
  • En los grupos elásticos de Azure SQL Database, considere la posibilidad de mover bases de datos que tienen transacciones de ejecución prolongada mediante el aislamiento de SNAPSHOT o RCSI fuera del grupo elástico.

Comprobación de consultas de ejecución prolongada en réplicas secundarias

Si la base de datos tiene réplicas secundarias, compruebe si el umbral bajo secundario avanza.

Ejecute las siguientes vistas de administración dinámica en la réplica principal para identificar consultas que se ejecutan durante mucho tiempo en la réplica secundaria, que puedan estar impidiendo la limpieza de PVS:

En la DMV sys.dm_tran_persistent_version_store_stats, las columnas de pvs_off_row_page_skipped_low_water_mark también pueden dar una indicación de un retraso de limpieza debido a una consulta de ejecución prolongada en una réplica secundaria.

Conéctese a una réplica secundaria, busque la sesión que ejecuta la consulta larga y considere la posibilidad de eliminar la sesión, si se permite. La consulta de ejecución prolongada en la réplica secundaria puede contener la limpieza de PVS, así como evitar una limpieza fantasma.

Comprobación de un gran número de transacciones anuladas

Si ninguno de los escenarios anteriores se aplica a las cargas de trabajo, es probable que la limpieza se retrase debido a un gran número de transacciones anuladas. Compruebe las columnas aborted_version_cleaner_last_start_time y aborted_version_cleaner_last_end_time para ver si se ha completado la última limpieza de transacciones anuladas. oldest_aborted_transaction_id debería moverse más arriba después de completarse la limpieza de la transacción anulada. Si el oldest_aborted_transaction_id es mucho menor que oldest_active_transaction_idy current_abort_transaction_count tiene un valor mayor, es probable que haya una transacción abortada antigua que está impidiendo la limpieza de PVS.

Para abordar un gran número de transacciones anuladas, tenga en cuenta lo siguiente:

  • Si es posible, detenga la carga de trabajo para que el limpiador de versiones pueda avanzar.
  • Optimice la carga de trabajo para reducir los bloqueos de nivel de objeto.
  • Revise la aplicación para identificar el problema de tasa de anulación de transacciones elevada. Las anulaciones pueden provenir de una alta tasa de interbloqueos, claves duplicadas, infracciones de restricción o tiempos de espera de consulta.
  • Si usa SQL Server, deshabilite ADR como paso de solo emergencia para controlar el tamaño de PVS. Consulte Deshabilitar ADR.
  • Si la limpieza de transacciones anuladas no se ha completado correctamente recientemente, compruebe el registro de errores para mensajes que informan de problemas VersionCleaner.
  • Si, incluso después de finalizar una limpieza, el tamaño de PVS no se reduce como se esperaba, verifique la columna pvs_off_row_page_skipped_oldest_aborted_xdesid. Los valores grandes indican que el espacio sigue siendo utilizado por versiones de fila de transacciones anuladas.

Inicio manual del proceso de limpieza del PVS

Si tiene una carga de trabajo con un alto volumen de instrucciones DML (INSERT, UPDATE, DELETE, MERGE), como en el caso de OLTP de alto volumen, es posible que se requiera un periodo de reposo o recuperación para que el proceso de limpieza de PVS pueda recuperar 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.

Por ejemplo:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Captura de errores de limpieza

A partir de SQL Server 2022 (16.x), el comportamiento de limpieza de PVS se registra en el registro de errores. Normalmente, esto da como resultado un nuevo evento de registro registrado cada 10 minutos. Las estadísticas de limpieza también se notifican mediante el tx_mtvc2_sweep_statsevento extendido.

Contenido relacionado