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

Поиск и устранение проблем с автоматической очисткой данных об изменениях

  • Статья

Область применения: 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 в возвращаемых строках представляет недопустимую версию очистки.

  2. Выполните следующий динамический запрос Transact-SQL (T-SQL), который создает запрос для получения количества строк с истекшим сроком во вспомогательных таблицах. Замените значение в запросе значением <invalid_version> , полученным на предыдущем шаге.

    SELECT 'SELECT ''' + QUOTENAME(name) + ''', COUNT_BIG(*) 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. Скопируйте результирующий набор из предыдущего запроса и удалите ключевое слово из последней 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 можно также использовать детализацию по минутам или часам.

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

    <internal_table_name> и <cleanup_version> для таблицы пользователя включены в выходные данные, возвращенные в предыдущем разделе. Используя эти сведения, выполните следующий код T-SQL через выделенное подключение администратора (DAC):

    SELECT '<internal_table_name>',
    COUNT_BIG(*)
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_BIG(*) 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;

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