Анатомия задания очистки CDC в SQL Server (и как поддерживать его в рабочем состоянии)

Автор: Paul Ou Yang, Anatomy of a SQL Server CDC Cleanup Job (and How to Keep It Healthy)

Система отслеживания изменений данных (Change Data Capture, CDC) замечательна, пока не перестаёт ей быть. При высокой интенсивности изменений задание очистки CDC может начать отставать. Когда это происходит, таблицы изменений (Change Tables, CT) быстро разрастаются, а задание очистки может блокировать задание записи (capture job), что, в свою очередь, может задерживать или останавливать нижестоящие системы-потребители.

Эта статья объясняет: как быстро оценить рост таблиц изменений, как задание очистки на самом деле удаляет строки и три основных «рычага» настройки для поддержания стабильности CDC.

Быстрая проверка работоспособности: приблизительное количество строк в таблицах изменений

Чтобы быстро оценить количество строк в каждой таблице изменений CDC:

SELECT
c.object_id,
t.name,
p.rows
FROM cdc.change_tables c
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.partitions p
ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
ORDER BY c.object_id;

Пример вывода:

object_id     name                   rows
-----------   ---------------------  --------
82099333      dbo_customer_CT         167702
98099390      dbo_district_CT         270010
114099447     dbo_item_CT             0
370100359     dbo_new_order_CT        134634
386100416     dbo_warehouse_CT        135522
562101043     dbo_order_line_CT       2018297
578101100     dbo_stock_CT            1343322
1973582069    dbo_orders_CT           191732
--------------------------------------------
Total                                 4,261,219

Значения по умолчанию для очистки CDC (срок хранения + порог)

Поведение очистки CDC в основном определяется двумя параметрами:

• Срок хранения (Retention) — как долго хранятся строки в таблицах изменений.
• Порог (Threshold) — количество строк, удаляемых за один пакет.

Проверьте текущие настройки:

EXEC sys.sp_cdc_help_jobs;

Пример вывода:

job_type  job_name           retention  threshold
--------  -----------------  ---------  ---------
capture   cdc.tpcc_capture          0          0
cleanup   cdc.tpcc_cleanup       4320       5000

Значения по умолчанию:
Срок хранения = 4320 минут (72 часа / 3 дня)
Порог = 5000 строк за один пакет удаления

Что на самом деле делает задание очистки

Расширенные события (Extended Events) обычно показывают, что задание очистки обрабатывает таблицы изменений последовательно с помощью курсора:

DECLARE #hchange_table CURSOR LOCAL FAST_FORWARD
FOR
SELECT capture_instance, start_lsn
FROM [cdc].[change_tables]
WHERE (@capture_instance IS NULL)
OR (capture_instance = @capture_instance);

Поскольку cdc.change_tables кластеризована по object_id, она стремится удалять из таблиц изменений в порядке object_id. С параметром @p1 = 5000 (порог) вы часто будете видеть такие паттерны:

DELETE TOP (@p1) FROM [cdc].[dbo_customer_CT]    WHERE __$start_lsn @p2 (35 раз)
DELETE TOP (@p1) FROM [cdc].[dbo_district_CT]    WHERE __$start_lsn @p2 (56 раз)
...
DELETE TOP (@p1) FROM [cdc].[dbo_stock_CT]       WHERE __$start_lsn @p2 (323 раза)

Большие таблицы могут доминировать во времени выполнения и не давать очистке когда-либо нагнать отставание.

Три «рычага» настройки

1) Настройка порога (Threshold)

Более высокий порог = удаляет больше за один пакет (часто более эффективно), но может увеличить конфликты блокировок.
Более низкий порог = меньшие порции удалений, но больше циклов и потенциально большее время выполнения.

Пример:

EXEC sys.sp_cdc_change_job
@job_type  = N'cleanup',
@threshold = 2000;

2) Уменьшение срока хранения (Retention)

Уменьшение срока хранения означает, что нужно хранить меньше данных, что облегчает очистку. Но потребители должны успевать забирать изменения в течение этого окна. Пример (2160 минут = 36 часов):

EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention  = 2160;

3) Более частый запуск очистки

Сначала найдите идентификатор расписания:

USE msdb;
GO
SELECT j.name AS job_name,
s.schedule_id,
s.name AS schedule_name
FROM dbo.sysjobs j
JOIN dbo.sysjobschedules js
ON j.job_id = js.job_id
JOIN dbo.sysschedules s
ON js.schedule_id = s.schedule_id
WHERE j.name = N'cdc.tpcc_cleanup';

Затем измените его (например, на каждые 15 минут):

USE msdb;
GO
EXEC dbo.sp_update_schedule
@schedule_id = 171,      -- из запроса выше
@enabled = 1,
@freq_type = 4,          -- ежедневно
@freq_interval = 1,
@freq_subday_type = 4,   -- минуты
@freq_subday_interval = 15,
@active_start_time = 000000; -- полночь

Крайняя мера: усечение таблиц изменений (высокий риск)

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

Безопасная последовательность:

1. Остановите задание записи (capture job).
2. Убедитесь, что потребители забрали оставшиеся изменения.
3. Выполните усечение (TRUNCATE) таблиц изменений.
4. Перезапустите задание записи.

Предупреждения:

• Вы можете создать пробелы в данных для нижестоящих потребителей.
• После усечения потребителям может потребоваться полная перезагрузка/перебазирование (re-baseline).
• Делайте это только с одобрения заинтересованных сторон и имея чёткий план восстановления.