Change Data Capture в SQL Server 2008: ночной ETL с 4 часов до 20 минут

Примерно четыре месяца назад мы заканчивали у клиента оптимизацию SSIS-пакетов - параллелизация, буферы, инкрементальная загрузка по полю LastModified. Всё это описывали в мае. Казалось, история закрыта. Но недавно тот же клиент позвонил снова: ночная ETL-задача снова ползёт, теперь уже за три-четыре часа, и они хотят понять, что делать дальше.

Проблема оказалась не в SSIS и не в параллелизме, а в том, что инкрементальная загрузка «по LastModified» имеет фундаментальный изъян применительно к ERP-транзакциям.

Почему LastModified не работает для транзакций ERP

В ERP-системе клиента транзакции - это не просто строки с датой модификации. Проводки могут быть исправлены задним числом, могут создаваться пакетами с разными timestamp-ами, у некоторых таблиц поле LastModified вообще не обновляется триггерами - обновляет только ERP-приложение, и только когда ему вздумается. В итоге получается, что ночной ETL берёт «все записи за последние N дней» - и этот N приходится постепенно увеличивать, потому что иначе что-то пропускается. Уже дошли до семи дней, а это значит каждую ночь гнать недельный объём транзакционной базы заново.

Мы посмотрели на SQL Server 2008 и вспомнили, что там появился Change Data Capture.

Что такое CDC и почему оно лучше подходит

CDC - это встроенный механизм SQL Server, который читает транзакционный лог и фиксирует все изменения в отдельных capture-таблицах. Не опрашивает таблицы, не полагается на поля дат - именно читает лог. Это означает, что он видит абсолютно все INSERT, UPDATE и DELETE, включая те, что пришли задним числом или без изменения LastModified.

Capture-таблица хранит: тип операции (1 - delete, 2 - insert, 3 - update before, 4 - update after), LSN транзакции, время фиксации и полный snapshot строки. ETL-пакет берёт не «всё за последние N дней», а «всё начиная с LSN, который мы обработали в прошлый раз». Это честная инкрементальная загрузка без догадок.

Как включали и что настраивали

Для начала убедились, что recovery model у исходной базы ERP выставлена в FULL - CDC требует полный лог. Это уже было так, поскольку у клиента и без того настроен бэкап лога каждые 15 минут.

Дальше три шага:

Включение CDC на уровне базы. Хранимая процедура sys.sp_cdc_enable_db, никаких аргументов. Создаёт служебную схему cdc и несколько системных таблиц.

Включение CDC на конкретных таблицах. Для каждой таблицы-источника вызов sys.sp_cdc_enable_table с указанием схемы, имени таблицы и capture instance. Можно указать конкретный список столбцов - это полезно, если таблица широкая, а в хранилище нужна только часть колонок. Мы включили CDC на восьми таблицах ERP: документы, проводки, контрагенты, номенклатура и ещё несколько.

Агент SQL Server. CDC работает через два job-а агента, которые создаются автоматически: capture (читает лог и пишет в capture-таблицы) и cleanup (чистит старые записи по retention). Retention выставили на 3 суток - достаточно, чтобы ETL мог отстать на выходные и не потерять данные.

Переписывали ETL под новую логику

Основная работа была именно здесь. Вместо SELECT с фильтром по дате теперь используем табличные функции CDC:

• cdc.fn_cdc_get_all_changes_<capture_instance> - возвращает все изменения между двумя LSN
• cdc.fn_cdc_get_net_changes_<capture_instance> - только итоговое состояние строки после всех изменений в диапазоне

Для таблицы фактов (проводки) нам нужна детальная история - взяли get_all_changes. Для справочников (контрагенты, номенклатура) достаточно итогового состояния - взяли get_net_changes.

В SSIS-пакете добавили шаг: в начале загрузки читаем последний обработанный LSN из служебной таблицы в хранилище, в конце - пишем туда текущий максимальный LSN. Между этими двумя точками - вся загрузка.

Обработку DELETE пришлось продумать отдельно: в хранилище мы не удаляем строки, а помечаем их флагом is_deleted и датой. Это важно для сохранения истории и корректной работы отчётности за прошлые периоды.

Что получилось

Первый прогон CDC-загрузки на тестовой среде занял около получаса - за счёт того, что capture-таблицы накопили суточный объём изменений, пока мы настраивали. Последующие ночные прогоны - стабильно 18-22 минуты. Для сравнения, до внедрения CDC с окном в семь дней загрузка занимала 3,5-4 часа.

Важный момент: производительность CDC не бесплатна. Capture job создаёт дополнительную нагрузку на SQL Server за счёт чтения лога - примерно 5-8% CPU в часы пик по нашим наблюдениям на этой инсталляции. Для нагруженной OLTP-базы это может быть чувствительно, нужно мерить под реальной нагрузкой.

Ещё одна особенность: capture-таблицы растут. Retention в три дня при активной ERP съедает несколько гигабайт. Этот объём надо закладывать в планирование дискового пространства.

CDC vs. триггеры vs. timestamp

Нас несколько раз спрашивали, почему CDC, а не триггеры на изменение или polling по timestamp. Коротко:

Триггеры надёжнее CDC в части гарантии «каждое изменение», но они работают в транзакции приложения и замедляют её. На нагруженной ERP это неприемлемо - клиент уже обжигался.

Timestamp/LastModified мы попробовали и описали выше, почему не подошло: слишком много допущений о том, что поле обновляется корректно.

CDC работает на уровне лога, не трогает производительность транзакций, гарантирует полноту. Платим за это объёмом capture-таблиц и нагрузкой capture job-а.

Для этого конкретного клиента выбор очевиден. Для другого расклада - не факт.

Подробнее о том, как мы строим хранилища данных и аналитику.