SSIS 2008 в работе: мигрировали ночной ETL-конвейер и получили меньше боли с памятью

Когда в августе вышел RTM SQL Server 2008, про SSIS мы написали осторожно: разница есть, но делать выводы рано. Прошло несколько месяцев, пайплайны на продуктиве, ночные прогоны накоплены - теперь есть что сказать конкретнее.

Что мигрировали

У одного из клиентов - розничная сеть с несколькими десятками магазинов - ночной ETL грузит данные из нескольких операционных источников в хранилище: продажи, остатки, справочники номенклатуры и контрагентов. Конвейер на SSIS 2005 работал, но с рядом хронических болячек. Главная: задача обогащения строк продаж атрибутами из справочника номенклатуры (Lookup) периодически ложилась с ошибкой нехватки памяти. Справочник большой, в SSIS 2005 Lookup по умолчанию затягивает весь набор в память целиком - и если сервер нагружен параллельными задачами, это заканчивалось плохо.

Мигрировали пакеты через SSDT 2008: открыть, принять апгрейд, проверить. Большинство пакетов прошли без правок, в двух местах пришлось руками поправить типы данных на OLE DB Source - поведение при неявных преобразованиях стало строже, что в целом правильно.

Data Flow Task: разница есть, но не фантастическая

Первое, что замеряли - время прохождения самого тяжёлого пакета, который гоняет несколько миллионов строк через несколько трансформаций. На SSIS 2005 ночной прогон занимал в среднем около полутора часов. На SSIS 2008 - примерно на четверть меньше. Без изменений в логике, просто апгрейд.

Откуда берётся разница - понятно не до конца. Microsoft говорит про переработанный движок Data Flow с лучшим управлением буферами. На практике заметно, что Pipeline меньше «зависает» на пиках нагрузки - CPU не скачет так резко. Это косвенно указывает на то, что буферизация данных между компонентами стала ровнее.

Громких выводов не делаем: один кейс - не статистика. Но четверть времени на ночном прогоне - это конкретные минуты до начала рабочего дня аналитиков.

Lookup с partial cache: это решение реальной проблемы

Вот тут разница принципиальная. В SSIS 2008 у компонента Lookup появились три режима кеширования:

• Full cache - поведение как в 2005: весь справочник в RAM до начала обработки.
• No cache - запрос в БД на каждую строку. Медленно, зато памяти не ест.
• Partial cache - кеш ограниченного размера, промахи идут в БД точечными запросами.

Partial cache - это именно то, чего не хватало в 2005. Мы настроили его на справочнике номенклатуры: выставили размер кеша, который комфортно помещается в доступную память, добавили TTL на записи. Горячие позиции номенклатуры - а их примерно 20% от общего объёма, но они покрывают большую часть строк продаж - остаются в кеше. Редкие позиции идут в БД по запросу.

Результат: OOM на Lookup исчез. За несколько недель работы в продуктиве ни одного падения. Время самой задачи Lookup выросло незначительно по сравнению с full cache - промахи стоят денег, но суммарно это не заметно на фоне общего времени пакета.

Настройка partial cache требует понимания распределения данных: если промахов много, а запросы в БД тяжёлые - можно получить замедление. В нашем случае соотношение справочника к реальной выборке строк продаж хорошее, поэтому режим работает как надо.

Что ещё заметили в процессе

Аннотации на холсте. В RTM-версии SSDT наконец можно добавлять текстовые аннотации прямо на поверхность дизайнера. Звучит как мелочь, но в больших пакетах с десятками задач это снимает необходимость держать отдельный документ «что вот этот кусок делает». Начали аннотировать - и уже через неделю оценили, когда пришлось разбираться в пакете, который писал другой человек.

Логирование стало подробнее. В SSIS 2008 появились новые события в логе Data Flow - в частности, события про буферы и пропускную способность компонентов. Это помогает находить узкие места: видно, какой именно компонент тормозит весь поток, а не просто «пакет долго работает».

Параллельные задачи внутри пакета. Sequence Container с параллельными ветками работал и в 2005, но поведение при ошибке в одной ветке стало предсказуемее. Раньше бывало, что ошибка в одной ветке не всегда корректно останавливала параллельные - приходилось добавлять защитные Event Handler-ы. В 2008 поведение выровнялось, хотя рекомендуем Event Handler-ы всё равно ставить.

Где пока осторожно

Partial cache у Lookup - не серебряная пуля. Если справочник маленький и помещается в память без вопросов, full cache быстрее: нет overhead на промахи и запросы в БД. Частичный кеш имеет смысл именно там, где справочник большой, а рабочий набор ключей заметно меньше полного объёма.

Миграция пакетов с 2005 на 2008 в нашем случае прошла без крупных сюрпризов, но правило одно: тестировать каждый пакет, не полагаться на автоапгрейд как на 100% совместимый перенос.

Если ваш ETL-пайплайн на SSIS 2005 и вопрос про миграцию на 2008 уже назрел - в рамках DWH и BI-проектов разберём конкретную конфигурацию и прикинем, где будет выигрыш, а где стоит быть осторожными.