Compartir a través de

Supervisión y solución de problemas de recuperación acelerada de bases 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 le ayuda a supervisar, diagnosticar y resolver 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 la base de datos SQL 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 diagnóstico de ejemplo siguiente se muestra la información sobre el tamaño actual de PVS, los procesos de limpieza y otros detalles de todas las bases de datos en las que el tamaño de PVS es mayor que cero:

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;

Compruebe la columna pvs_percent_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 entre el tamaño típico de PVS y las líneas base que se ven durante períodos típicos de actividad de la aplicación. El PVS se considera grande si es significativamente mayor que la línea base o si está cerca del 50 % del tamaño de la base de datos.

Si el tamaño de PVS no disminuye, siga estos pasos de solución de problemas para buscar y resolver el motivo del tamaño de PVS grande.

Consejo (if the meaning of "Tip" is advice or suggestion)

Las columnas mencionadas en los siguientes pasos de solución de problemas hacen referencia a las columnas del conjunto de resultados de la consulta de diagnóstico de esta sección.

El tamaño grande de PVS puede ser causado por alguna de las siguientes razones:

Comprobación de transacciones activas prolongadas

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 buscar transacciones de larga duración, use la consulta de ejemplo siguiente. Puede establecer umbrales para la duración de la transacción y la cantidad de registro de transacciones generado:

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;

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 problemáticas para evitar el problema en el futuro.

Para obtener más información sobre la solución de problemas de consultas de ejecución prolongada, 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 determinar 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 puede promoverse a 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, incluso cuando ADR no está habilitado, los exámenes de instantáneas pueden impedir 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.

La columna pvs_off_row_page_skipped_min_useful_xts muestra el número de páginas omitidas durante la limpieza debido a un escaneo de instantánea largo. Si esta columna muestra un valor grande, significa que un análisis de instantánea prolongado impide la limpieza de PVS.

Use la consulta de ejemplo siguiente para buscar las sesiones con la transacción de SNAPSHOT o RCSI de ejecución prolongada:

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;

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 ejecución prolongada 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 tengan transacciones de ejecución prolongada mediante aislamiento de SNAPSHOT o RCSI fuera del grupo elástico para evitar el retraso de limpieza de PVS en otras bases de datos del mismo grupo.

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.

Un valor elevado en la columna pvs_off_row_page_skipped_low_water_mark podría ser una indicación de un retraso en la limpieza debido a una consulta que se ejecuta durante mucho tiempo en una réplica secundaria. Además de mantener la limpieza de PVS, una consulta de ejecución prolongada en una réplica secundaria también puede contener limpieza de registros fantasma.

Puede usar las consultas de ejemplo siguientes en la réplica principal para buscar si es posible que las consultas de ejecución prolongada en réplicas secundarias impidan la limpieza de PVS. Si una carga de trabajo de escritura se está ejecutando en la réplica principal, pero el valor de la columna low_water_mark_for_ghosts no aumenta de una ejecución de la consulta de ejemplo a la siguiente, PVS y la limpieza de registros fantasma podrían quedar retenidos por una consulta de larga duración en una réplica secundaria.

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;

Para obtener más información, vea la descripción de la columna low_water_mark_for_ghosts en sys.dm_hadr_database_replica_states.

Conéctese a cada réplica secundaria legible, busque la sesión con la consulta de ejecución prolongada y considere la posibilidad de eliminar la sesión, si se permite. Para obtener más información, consulte Búsqueda de consultas lentas.

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

Compruebe las columnas aborted_version_cleaner_start_time y aborted_version_cleaner_end_time para verificar si se ha completado la limpieza de la última transacción abortada. oldest_aborted_transaction_id debería moverse más arriba después de completarse la limpieza de la transacción anulada. Si oldest_aborted_transaction_id es mucho menor que oldest_active_transaction_id y current_abort_transaction_count tiene un valor mayor, es probable que haya una transacción anulada antigua que está impidiendo la limpieza de PVS.

Para resolver el retraso de limpieza de PVS debido a un gran número de transacciones anuladas, tenga en cuenta lo siguiente:

  • Si usa SQL Server 2022 (16.x), aumente el valor de la configuración del servidor ADR Cleaner Thread Count. Para más información, consulte Configuración del servidor: número de subprocesos del limpiador de ADR.
  • Si es posible, detenga la carga de trabajo para que el limpiador de versiones pueda avanzar.
  • Revise la aplicación para identificar y resolver 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.
  • Optimice la carga de trabajo para reducir los bloqueos incompatibles con los bloqueos de nivel de objeto o de partición IX requeridos por el limpiador pvS. Para obtener más información, consulte Compatibilidad de bloqueo.
  • Si usa SQL Server, deshabilite ADR como paso de solo emergencia para controlar el tamaño de PVS. Consulte Deshabilitar ADR.
  • Si usa SQL Server y si la limpieza de transacciones anulada no se ha completado correctamente recientemente, compruebe el registro de errores de los mensajes que notifican problemas de 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.

Control del tamaño de PVS

Si tiene una carga de trabajo con un gran volumen de instrucciones DML (INSERT, UPDATE, DELETE, MERGE), como en situaciones de OLTP de gran volumen, y observa que el tamaño de PVS es grande, es posible que deba aumentar el valor en la configuración del servidor ADR Cleaner Thread Count para mantener el tamaño de PVS bajo control. Para obtener más información, consulte Configuración del servidor: número de subprocesos del limpiador de ADR, que está disponible a partir de SQL Server 2022 (16.x).

En SQL Server 2019 (15.x) o si aumentar el valor de configuración de ADR Cleaner Thread Count no ayuda a reducir el tamaño de PVS lo suficiente, la carga de trabajo puede requerir un período de descanso o recuperación para que el proceso de limpieza de PVS recupere 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), los mensajes de limpieza de PVS importantes se registran en el registro de errores. Las estadísticas de limpieza también se notifican mediante el tx_mtvc2_sweep_statsevento extendido.

Contenido relacionado