Solución de problemas de limpieza automática en el seguimiento de cambios

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

En este artículo se proporcionan formas de solucionar problemas comunes observados en la limpieza automática del seguimiento de cambios.

Síntomas

Por lo general, si la limpieza automática no funciona según lo previsto, puede experimentar uno o varios de los síntomas siguientes:

  • Consumo elevado de almacenamiento por una o varias tablas laterales de seguimiento de cambios o la tabla del sistema syscommittab.
  • Las tablas laterales (tablas internas cuyo nombre comienza por el prefijo change_tracking, por ejemplo, change_tracking_12345) o syscommittab ambos, muestran un número significativo de filas que están fuera del período de retención configurado.
  • La tabla dbo.MSChange_tracking_history tiene entradas con errores de limpieza específicos.
  • El rendimiento de CHANGETABLE se ha degradado con el tiempo.
  • La limpieza automática o la limpieza manual notifican un uso elevado de CPU.

Depuración y mitigación

Para identificar la causa principal de un problema con la limpieza automática en el seguimiento de cambios, siga estos pasos para depurar y mitigar el problema.

Estado de limpieza automática

Compruebe si se ha ejecutado la limpieza automática. Para comprobarlo, consulte la tabla del historial de limpieza en la misma base de datos. Si se ha ejecutado la limpieza, la tabla tiene entradas con las horas de inicio y finalización de la limpieza. Si la limpieza no se ha estado ejecutando, la tabla está vacía o tiene entradas obsoletas. Si la tabla de historial tiene entradas con la etiqueta cleanup errors en la columna comments, se produce un error en la limpieza debido a errores de limpieza en el nivel de tabla.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

La limpieza automática se ejecuta periódicamente con un intervalo predeterminado de 30 minutos. Si la tabla de historial no existe, lo más probable es que la limpieza automática nunca se haya ejecutado. De lo contrario, compruebe los valores de columna start_time y end_time. Si las entradas más recientes no son recientes, es decir, tienen horas o días de antigüedad, es posible que la limpieza automática no se esté ejecutando. Si es así, siga estos pasos para solucionar problemas.

1. La limpieza está desactivada

Compruebe si la limpieza automática está activada para la base de datos. Si no es así, actívela y espere al menos 30 minutos antes de examinar la tabla de historial para ver si hay nuevas entradas. Supervise el progreso de la tabla de historial a partir de entonces.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Un valor distinto de cero en is_auto_cleanup_on indica que la limpieza automática está habilitada. El valor del período de retención controla la duración de los metadatos de seguimiento de cambios que se conservan en el sistema. El valor predeterminado para el período de retención del seguimiento de cambios es de 2 días.

Para habilitar o deshabilitar el seguimiento de cambios, consulte Habilitar y deshabilitar el seguimiento de cambios (SQL Server).

2. La limpieza está activada pero no se ejecuta

Si la limpieza automática está activada, es probable que el subproceso de limpieza automática se detuvo debido a errores inesperados. Actualmente, no es factible reiniciar el subproceso de limpieza automática. Debe iniciar una conmutación por error en un servidor secundario (o reiniciar el servidor si no hay uno secundario) y confirmar que la configuración de limpieza automática está habilitada para la base de datos.

La limpieza automática se ejecuta, pero no progresa

Si una o varias tablas laterales muestran un consumo de almacenamiento significativo o contienen un gran número de registros más allá de la retención configurada, siga los pasos descritos en esta sección, que describen los recursos de una sola tabla lateral. Si es necesario, se pueden repetir los mismos pasos para más tablas.

1. Evaluación del trabajo pendiente de limpieza automática

Identifique las tablas laterales que tienen una gran acumulación de registros caducados, que necesitan mitigación. Ejecute las consultas a continuación para identificar las tablas laterales con un gran número de registros caducados. Recuerde reemplazar los valores de los scripts de ejemplo como se muestra.

  1. Obtenga la versión de limpieza no válida:

    SELECT * FROM sys.change_tracking_tables;

    El valor cleanup_version de las filas devueltas representa la versión de limpieza no válida.

  2. Ejecute la consulta dinámica de Transact-SQL (T-SQL) a continuación, que genera la consulta para obtener el recuento de filas expirado de las tablas laterales. Reemplace el valor de <invalid_version> en la consulta por el valor obtenido en el paso anterior.

    SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

  3. Copie el conjunto de resultados de la consulta anterior y elimine la palabra clave UNION de la última fila. Si ejecuta la consulta T-SQL generada a través de una conexión de administrador dedicada (DAC), la consulta proporciona los recuentos de filas expirados de todas las tablas laterales. Según el tamaño de la tabla sys.syscommittab y el número de tablas laterales, esta consulta puede tardar mucho tiempo en completarse.

    Importante

    Este paso es necesario para avanzar con los pasos de mitigación. Si la consulta anterior no se ejecuta, identifique los recuentos de filas expiradas de las tablas laterales individuales utilizando las consultas que se indican a continuación.

Siga los pasos de mitigación a continuación para las tablas laterales, teniendo el orden decreciente de los recuentos de filas expiradas, hasta que los recuentos de filas expiradas bajen a un estado manejable para que la limpieza automática se ponga al día.

Una vez que identifique las tablas laterales con un gran número de registros caducados, recopile información sobre la latencia de las instrucciones de eliminación de la tabla lateral y la tasa de eliminación por segundo en las últimas horas. A continuación, calcule el tiempo necesario para limpiar la tabla lateral teniendo en cuenta tanto el número de filas obsoletas como la latencia de eliminación.

Utilice el fragmento de código de T-SQL a continuación sustituyendo las plantillas de parámetro por los valores adecuados.

  • Consulte la tasa de limpieza por segundo:

    SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

    También puede usar granularidad de minuto o hora para la función DATEDIFF.

  • Busque el recuento de filas obsoletas en la tabla lateral. Esta consulta le ayuda a encontrar el número de filas pendientes de limpieza.

    Los valores <internal_table_name> y <cleanup_version> de la tabla de usuario se encuentran en la salida devuelta en la sección anterior. Con esta información, ejecute el siguiente código T-SQL a través de una conexión de administrador dedicada (DAC):

    SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);

    Esta consulta puede tardar un tiempo en finalizar. En los casos en los que se agota el tiempo de espera de la consulta, calcule las filas obsoletas mediante la búsqueda de la diferencia entre las filas totales y las filas activas, es decir, las filas que se van a limpiar.

  • Para buscar el número total de filas en la tabla lateral, ejecute la consulta siguiente:

    SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

  • Para buscar el número de filas activas en la tabla lateral, ejecute la consulta siguiente:

    SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);

    Puede calcular el tiempo estimado para limpiar la tabla mediante la tasa de limpieza y el recuento de filas obsoletas. Tenga en cuenta la siguiente fórmula:

    Tiempo para limpiar en minutos = (recuento de filas obsoletas) / (tasa de limpieza en minutos)

    Si el tiempo para completar la limpieza de tablas es aceptable, supervise el progreso y deje que la limpieza automática continúe su trabajo. Si no es así, continúe con los pasos siguientes para investigar en más profundidad.

2. Comprobación de conflictos de bloqueo de tabla

Determine si la limpieza no progresa debido a conflictos de escalación de extensión de bloqueo de tabla, lo que impide que la limpieza adquiera objetivos de forma coherente en la tabla lateral para eliminar filas.

Para confirmar un conflicto de bloqueo, ejecute el código T-SQL a continuación. Esta consulta captura los registros de la tabla problemática para determinar si hay varias entradas que indican conflictos de bloqueo. Unos pocos conflictos esporádicos repartidos a lo largo de un periodo no deberían requerir los pasos de mitigación siguientes. Los conflictos deben ser recurrentes.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Si la tabla de historial tiene varias entradas en las columnas comments con el valor Cleanup error: Lock request time out period exceeded, es una indicación clara de que se ha producido un error en varios intentos de limpieza debido a conflictos de bloqueo o tiempos de espera de bloqueo en sucesión. Considere la posibilidad de usar las siguientes correcciones:

  • Deshabilite y habilite el seguimiento de cambios en la tabla problemática. Esto hace que se purguen todos los metadatos de seguimiento mantenidos para la tabla. Los datos de la tabla permanecen intactos. Este es el recurso más rápido.

  • Si la opción anterior no es posible, continúe con la limpieza manual en la tabla habilitando la marca de seguimiento 8284 de la siguiente manera:

    DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';

3. Comprobación de otras causas

Otra posible causa de atraso de limpieza es la lentitud de las instrucciones de eliminación. Para determinar si es así, compruebe el valor de hardened_cleanup_version. Este valor se puede recuperar a través de una conexión de administrador dedicada (DAC) a la base de datos en cuestión.

Busque la versión de limpieza protegida ejecutando la consulta siguiente:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Busque la versión de limpieza ejecutando la consulta siguiente:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Si los valores hardened_cleanup_version y cleanup_version son iguales, omita esta sección y continúe con la sección siguiente.

Si ambos valores son diferentes, significa que se han producido errores en una o varias tablas laterales. La mitigación más rápida es deshabilitar y habilitar el seguimiento de cambios en la tabla problemática. Esto hace que se purguen todos los metadatos de seguimiento mantenidos para la tabla. Los datos en la tabla permanecen intactos.

Si la opción anterior no es posible, ejecute la limpieza manual en la tabla.

Solución de problemas de syscommittab

En esta sección se describen los pasos para depurar y mitigar problemas con la tabla del sistema syscommittab, si usa una gran cantidad de espacio de almacenamiento, o si tiene un gran trabajo pendiente de filas obsoletas.

La limpieza de la tabla del sistema syscommittab depende de la limpieza de la tabla lateral. Solo después de limpiar todas las tablas laterales, se puede purgar syscommittab. Asegúrese de que se realizan todos los pasos de la sección La limpieza automática se ejecuta, pero no progresa.

Para invocar explícitamente la limpieza syscommittab, use el procedimiento almacenado sys.sp_flush_commit_table_on_demand.

Nota:

El procedimiento almacenado sys.sp_flush_commit_table_on_demand puede tardar tiempo si se elimina un trabajo pendiente grande de filas.

Como se muestra en la sección de ejemplo del artículo sys.sp_flush_commit_table_on_demand, este procedimiento almacenado devuelve el valor de safe_cleanup_version() y el número de filas eliminadas. Si el valor devuelto parece ser 0 y si está activado el aislamiento de instantáneas, es posible que la limpieza no elimine nada de syscommittab.

Si el período de retención es mayor de un día, es seguro volver a ejecutar el procedimiento almacenado sys.sp_flush_commit_table_on_demand después de habilitar la marca de seguimiento 8239 globalmente. El uso de esta marca de seguimiento cuando el aislamiento de instantánea está desactivado siempre es seguro, pero en algunos casos, es posible que no sea necesario.

Uso elevado de CPU durante la limpieza

El problema descrito en esta sección podría experimentarse en versiones anteriores de SQL Server. Si hay un número grande de tablas de seguimiento de cambios en una base de datos y la limpieza automática o la limpieza manual provoca un uso elevado de la CPU. Este problema también puede deberse a la tabla de historial, que se mencionó brevemente en secciones anteriores.

Use el código T-SQL siguiente para comprobar el número de filas de la tabla de historial:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Si el número de filas es lo suficientemente grande, intente agregar el siguiente índice si está ausente. Utilice el siguiente código T-SQL para añadir el índice:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Ejecución de la limpieza con más frecuencia de 30 minutos

Las tablas específicas pueden experimentar una alta tasa de cambios y es posible que el trabajo de limpieza automática no pueda limpiar las tablas laterales y syscommittab dentro del intervalo de 30 minutos. Si esto ocurre, puede ejecutar un trabajo de limpieza manual con mayor frecuencia para facilitar el proceso.

Para SQL Server y Azure SQL Managed Instance, cree un trabajo en segundo plano utilizando sp_flush_CT_internal_table_on_demand con un interno más corto que el predeterminado de 30 minutos. Para Azure SQL Database, Azure Logic Apps se puede usar para programar estos trabajos.

El siguiente código T-SQL se puede utilizar para crear un trabajo que ayude a limpiar las tablas laterales para el seguimiento de cambios:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Contenido relacionado