Устранение неполадок автоматической очистки отслеживания изменений

Статья
01/04/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье приведены способы устранения распространенных проблем, наблюдаемых при автоматической очистке отслеживания изменений.

Симптомы

Как правило, если автоматическая очистка не работает должным образом, вы можете увидеть один или несколько следующих симптомов:

Высокий объем потребления хранилища одной или несколькими таблицами отслеживания изменений или системной таблицей syscommittab .
Параллельные таблицы (внутренние таблицы, имена которых начинаются с префикса change_tracking, например change_tracking_12345) или syscommittab обоих, отображают значительное количество строк, которые находятся за пределами настроенного периода хранения.
dbo.MSChange_tracking_history Таблица содержит записи с определенными ошибками очистки.
CHANGETABLE производительность снизилась с течением времени.
Автоматическая очистка или ручная очистка сообщает о высокой загрузке ЦП.

Отладка и устранение рисков

Чтобы определить первопричину проблемы с автоматической очисткой отслеживания изменений, выполните следующие действия для отладки и устранения проблемы.

Состояние автоматической очистки

Проверьте, запущена ли автоматическая очистка. Чтобы проверить это, запросите таблицу журнала очистки в той же базе данных. Если очистка запущена, таблица содержит записи с временем начала и окончания очистки. Если очистка не выполнялась, таблица пуста или имеет устаревшие записи. Если в таблице журнала есть записи с тегом cleanup errors в столбце comments, очистка завершается ошибкой из-за ошибок очистки на уровне таблицы.

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

Автоматическая очистка периодически выполняется с интервалом по умолчанию в 30 минут. Если таблица журнала не существует, скорее всего, автоматическая очистка никогда не выполняется. В противном случае проверьте start_time значения и end_time столбцы. Если последние записи не последние, то есть они часы или дни, автоматическое очистка может не выполняться. Если это так, выполните следующие действия, чтобы устранить неполадки.

1. Очистка отключена

Проверьте, включена ли автоматическая очистка базы данных. Если нет, включите ее и подождите по крайней мере 30 минут, прежде чем искать в таблице журнала новые записи. Отслеживайте ход выполнения в таблице журнала после этого.

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

Значениеis_auto_cleanup_on, отличное от нуля, указывает, что включена автоматическая очистка. Значение периода хранения определяет длительность хранения метаданных отслеживания изменений в системе. Значение по умолчанию для периода хранения отслеживания изменений составляет 2 дня.

Сведения о включении или отключении отслеживания изменений см. в разделе "Включение и отключение Отслеживание изменений" (SQL Server).

2. Очистка включена, но не запущена

Если автоматическая очистка включена, поток автоматической очистки, вероятно, остановлен из-за непредвиденных ошибок. В настоящее время перезагрузка потока автоматической очистки невозможна. Необходимо инициировать отработку отказа на дополнительный сервер (или перезапустить сервер в отсутствие вторичного) и убедиться, что параметр автоматической очистки включен для базы данных.

Автоматическая очистка выполняется, но не выполняется

Если одна или несколько параллельных таблиц показывают значительное потребление хранилища или содержат большое количество записей за пределами настроенного хранения, выполните действия, описанные в этом разделе, в котором описаны средства защиты для одной боковой таблицы. При необходимости можно повторить те же действия для дополнительных таблиц.

1. Оценка невыполненной работы автоматической очистки

Определите параллельные таблицы, имеющие большую невыполненную работу записей с истекшим сроком действия, которые должны быть выполнены для их устранения. Выполните следующие запросы, чтобы определить количество записей с истекшим сроком действия. Не забудьте заменить значения в примерах скриптов, как показано ниже.

Получите недопустимую версию очистки:
```
SELECT * FROM sys.change_tracking_tables;
```
Значение cleanup_version возвращаемых строк представляет недопустимую версию очистки.
Выполните следующий динамический запрос Transact-SQL (T-SQL), который создает запрос, чтобы получить количество строк с истекшим сроком действия побочных таблиц. Замените значение в запросе значением <invalid_version> , полученным на предыдущем шаге.
```
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;
```
Скопируйте результирующий набор из предыдущего запроса и удалите ключевое слово из последней UNION строки. При выполнении созданного запроса T-SQL через выделенное подключение администратора (DAC) запрос дает количество строк с истекшим сроком действия всех параллельных таблиц. В зависимости от размера sys.syscommittab таблицы и количества параллельных таблиц этот запрос может занять много времени.

Внимание

Этот шаг необходим для продвижения вперед с помощью шагов по устранению рисков. Если предыдущий запрос не выполняется, определите количество строк с истекшим сроком действия для отдельных параллельных таблиц с помощью запросов, заданных далее.

Выполните следующие действия по устранению рисков для параллельных таблиц с уменьшением порядка просроченных строк до тех пор, пока истекший срок действия не будет завершен до управляемого состояния автоматической очистки.

После определения параллельных таблиц с большим числом записей с истекшим сроком действия соберите сведения о задержке инструкций удаления боковой таблицы и скорости удаления в секунду за последние несколько часов. Затем оцените время, необходимое для очистки боковой таблицы, учитывая количество устаревших строк и задержку удаления.

Используйте следующий фрагмент кода T-SQL, заменив шаблоны параметров соответствующими значениями.

Запрос скорости очистки в секунду:

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;

Для функции можно также использовать детализацию DATEDIFF минуты или часа.

Найдите число устаревших строк во вспомогательной таблице. Этот запрос поможет найти количество строк, ожидающих очистки.

<cleanup_version> Таблица <internal_table_name> пользователя содержит выходные данные, возвращаемые в предыдущем разделе. Используя эти сведения, выполните следующий код T-SQL через выделенное подключение администратора (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>
);
```
Этот запрос может занять некоторое время. В случаях, когда время ожидания запроса истекло, вычислите устаревшие строки, найдя разницу между общими строками и активными строками, которые необходимо очистить.
Найдите общее количество строк в боковой таблице, выполнив следующий запрос:
```
SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;
```
Найдите количество активных строк в боковой таблице, выполнив следующий запрос:
```
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>);
```
Вы можете вычислить предполагаемое время очистки таблицы с помощью скорости очистки и количества устаревших строк. Рассмотрим следующую формулу:

Время очистки в минутах = (число устаревших строк) / (скорость очистки в минутах)

Если время завершения очистки таблицы приемлемо, отслеживайте ход выполнения и позволяйте автоматической очистке продолжать работу. Если нет, выполните следующие действия для дальнейшего детализации.

2. Проверка конфликтов блокировки таблицы

Определите, не выполняется ли очистка из-за конфликтов эскалации блокировки таблицы, которые последовательно очищаются от получения блокировок на боковой таблице для удаления строк.

Чтобы подтвердить конфликт блокировки, выполните следующий код T-SQL. Этот запрос получает записи для проблемной таблицы, чтобы определить наличие нескольких записей, указывающих на конфликты блокировки. Некоторые спорадические конфликты, распространяемые в течение определенного периода, не должны претендовать на шаги по устранению последствий. Конфликты должны повторяться.

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

Если таблица журнала содержит несколько записей в comments столбцах со значением Cleanup error: Lock request time out period exceeded, это четкое указание на то, что несколько попыток очистки завершилось сбоем из-за конфликтов блокировки или времени ожидания блокировки в последовательности. Рассмотрим следующие средства защиты:

Выключите и включите отслеживание изменений в проблемной таблице. Это очистит все метаданные отслеживания, используемые для таблицы. Данные таблицы не будут затронуты. Это самое быстрое решение.
Если предыдущий параметр недоступна, перейдите к выполнению ручной очистки таблицы, включив флаг трассировки 8284 следующим образом:
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. Проверьте другие причины

Еще одна возможная причина задержки очистки заключается в замедлении инструкций удаления. Чтобы определить, если да, проверьте значение hardened_cleanup_version. Это значение можно получить с помощью выделенного подключения администратора (DAC) к базе данных, в рамках которого рассматривается.

Найдите затверденную версию очистки, выполнив следующий запрос:

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

Найдите версию очистки, выполнив следующий запрос:

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

Если hardened_cleanup_version и cleanup_version значения равны, пропустите этот раздел и перейдите к следующему разделу.

Если оба значения отличаются, это означает, что одна или несколько побочных таблиц столкнулись с ошибками. Самый быстрый способ устранения рисков — отключить и включить отслеживание изменений в проблемной таблице. Это очистит все метаданные отслеживания, используемые для таблицы. Данные в таблице остаются нетронутыми.

Если предыдущий параметр недоступна, выполните ручную очистку таблицы.

Устранение неполадок syscommittab

В этом разделе рассматриваются действия по отладке и устранению проблем с syscommittab системной таблицей, если она использует много места в хранилище или имеет большой невыполненной работы устаревших строк.

Очистка системной syscommittab таблицы зависит от очистки боковой таблицы. Только после очистки syscommittab всех боковых таблиц можно очистить. Убедитесь, что все шаги в запусках автоматической очистки, но не выполняются .

Чтобы явно вызвать syscommittab очистку, используйте хранимую процедуру sys.sp_flush_commit_table_on_demand .

Примечание.

Хранимая sys.sp_flush_commit_table_on_demand процедура может занять некоторое время, если она удаляет большую невыполненную работу строк.

Как показано в примере раздела из статьи sys.sp_flush_commit_table_on_demand , эта хранимая процедура возвращает значение safe_cleanup_version()и количество удаленных строк. Если возвращаемое значение, 0и если изоляция моментальных снимков включена, очистка может не удалять ничего из syscommittab.

Если срок хранения больше одного дня, его следует безопасно повторно запустить sys.sp_flush_commit_table_on_demand хранимую процедуру после включения флага трассировки 8239 глобально. Использование этого флага трассировки при отключении изоляции моментальных снимков всегда безопасно, но в некоторых случаях это может не потребоваться.

Высокая загрузка ЦП во время очистки

Проблема, описанная в этом разделе, может появиться в более ранних версиях SQL Server. Если в базе данных есть большое количество отслеживаемых изменений таблиц, а автоматическая очистка или ручная очистка приводит к высокой загрузке ЦП. Эта проблема также может быть вызвана из-за таблицы журнала, которая упоминалась кратко в предыдущих разделах.

Используйте следующий код T-SQL, чтобы проверить количество строк в таблице журнала:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Если количество строк достаточно большое, попробуйте добавить следующий индекс, если он отсутствует. Чтобы добавить индекс, используйте следующий код T-SQL:

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

Выполнение очистки чаще 30 минут

Определенные таблицы могут столкнуться с высоким уровнем изменений, и вы можете обнаружить, что задание автозаполнения не может очистить побочные таблицы и syscommittab в течение 30-минутного интервала. Если это происходит, можно запустить задание очистки вручную с повышенной частотой, чтобы упростить процесс.

Для SQL Server и Управляемый экземпляр SQL Azure создайте фоновое задание с более sp_flush_CT_internal_table_on_demand коротким внутренним значением, чем 30 минут по умолчанию. Для База данных SQL Azure можно использовать Azure Logic Apps для планирования этих заданий.

Следующий код T-SQL можно использовать для создания задания для очистки параллельных таблиц для отслеживания изменений:

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

Поделиться через

Устранение неполадок автоматической очистки отслеживания изменений

Симптомы

Отладка и устранение рисков

Состояние автоматической очистки

1. Очистка отключена

2. Очистка включена, но не запущена

Автоматическая очистка выполняется, но не выполняется

1. Оценка невыполненной работы автоматической очистки

2. Проверка конфликтов блокировки таблицы

3. Проверьте другие причины

Устранение неполадок syscommittab

Высокая загрузка ЦП во время очистки

Выполнение очистки чаще 30 минут

Обратная связь

Дополнительные ресурсы

Поделиться через

Устранение неполадок автоматической очистки отслеживания изменений

Симптомы

Отладка и устранение рисков

Состояние автоматической очистки

1. Очистка отключена

2. Очистка включена, но не запущена

Автоматическая очистка выполняется, но не выполняется

1. Оценка невыполненной работы автоматической очистки

2. Проверка конфликтов блокировки таблицы

3. Проверьте другие причины

Устранение неполадок syscommittab

Высокая загрузка ЦП во время очистки

Выполнение очистки чаще 30 минут

Связанный контент

Обратная связь

Дополнительные ресурсы