Проектирование высокопроизводительных пакетов служб SSIS

Процесс проектирования пакетов служб SQL Server Integration Services (SSIS) обычно происходит следующим образом. Сначала добиваются работы компонентов по отдельности или в малых группах, затем внимание переключается на работу компонентов в правильной последовательности. На более поздних этапах количество компонентов увеличивается или настраиваются свойства для устранения ошибок. На последнем этапе могут добавляться абстракции, чтобы использовать переменные и выражения для внесения изменений в пакет в процессе выполнения. Но работа все еще не завершена.

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

Помните, что производительность пакетов служб SSIS зависит от различных факторов. Некоторые из них — чисто внешние, например исходные компьютеры, используемые для извлечения данных, скорость и настройки дисков, сетевые адаптеры, пропускная способность каналов связи и размер памяти сервера, на котором выполняются пакеты. В статье не рассматриваются способы устранения узких мест, вызванных внешними факторами. Вместо этого мы остановимся на конкретных подходах, которых можно придерживаться при построении пакетов. Предполагается, что читатели знакомы с общей архитектурой SSIS и способами разработки пакетов.

Производительность потока управления

Каждый пакет служб SSIS имеет по крайней мере одну задачу в потоке управления. Если в поток управления введено несколько задач, можно направлять их последовательность, добавляя управление очередностью, чтобы соединить задачи от начала до конца. Можно даже сгруппировать задачи в контейнерах. Помимо задачи потока данных, которая будет подробно описана ниже, производительность каждой отдельной задачи зависит от внешних систем, с которыми взаимодействует данная задача. Поэтому внутри пакета единственный способ ускорить обработку потока управления — запускать задачи или контейнеры (известные под общим названием исполняемых объектов) параллельно. На рисунке 1 показан параллелизм в Контейнере A и последовательный поток операций в Контейнере B. В этом примере сами контейнеры также исполняются параллельно.

Рисунок 1. Параллельный запуск исполняемых объектов для ускорения обработки потока управления

MaxConcurrentExecutables — свойство пакета, определяющее количество исполняемых объектов, которые можно запускать параллельно (экран 1). Значение по умолчанию –1. Это значит, что механизм потока управления задействует количество логических процессоров на сервере плюс 2. Например, если пакет с установленным по умолчанию значением свойства исполняется на четырехъядерном сервере, то можно параллельно запускать шесть исполняемых объектов.

Экран 1. Использование свойства MaxConcurrentExecutables для определения количества исполняемых объектов, выполняемых параллельно

В некоторых случаях увеличение значения MaxConcurrentExecutables не приводит к заметным переменам. Однако улучшения возможны, если пакет содержит задачи, ожидающие ответа от внешних систем, и сервер выделен для выполнения пакетов. В этом случае можно повысить параллелизм, увеличив значение MaxConcurrentExecutables. Начните с увеличения значения до количества процессоров плюс 3, затем проверьте пакет в среде Business Intelligence Development Studio (BIDS), чтобы выяснить, увеличивается ли число параллельно выполняемых задач. Если это так, продолжайте увеличивать значение свойства на 1 до тех пор, пока дальнейшее повышение параллелизма не станет невозможным или не будут исчерпаны задачи.

Подробнее о производительности потока данных

Задача потока данных используется для извлечения данных из одного или нескольких источников, изменения структуры или содержимого передаваемых данных (при необходимости) и отправки данных в одно или несколько мест назначения. В приложениях бизнес-аналитики (BI) эта задача используется для операций извлечения, преобразования и загрузки (ETL).

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

Поиск узких мест и назначение базового уровня

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

Создайте таблицу с заголовками столбцов и строк, как в приведенной таблице.
Выполните пакет 10 раз, записывая в столбце A. Это позволит определить время, необходимое для выполнения пакета, когда все три компонента неизменны.
Замените компонент назначения преобразованием «Многоадресная доставка», что позволит успешно завершить задачу без заметного увеличения времени ее выполнения. Выполните пакет всего 10 раз, записывая время в столбце B.
Удалите первое преобразование в потоке данных и подключите источник непосредственно к преобразованию «Многоадресная доставка», чтобы измерить только процесс извлечения данных. Выполните пакет всего 10 раз, записывая время в столбце C.
Вычислите среднее значение для каждого из трех основных измерений.
Получите время обработки компонента преобразования, вычитая усредненное значение в столбце C из усредненного значения в столбце B.
Получите время, необходимое для загрузки в место назначения, вычитая усредненное значение в столбце B из усредненного значения в столбце A.

Таблица. Пример результатов тестирования задачи передачи данных

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

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

Устранение узких мест исходных компонентов

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

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

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

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

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

Используйте режим Command вместо режима Table или View для реляционного источника. В источнике OLE DB можно извлекать данные из таблицы или представления, либо получить данные с помощью команды SQL. Чтобы получить данные из таблицы или представления в режиме Table или View, конвейер потока данных выдает команду с использованием функции OPENROWSET. В режиме SQL Command используется более быстрая хранимая процедура sp_executesql.

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

Уязвимые места преобразования

Производительность при преобразованиях в потоке данных зависит от эффективного использования буферов. Высокопроизводительные преобразования, известные как синхронные преобразования (рисунок 2), обрабатывают данные в существующем буфере, избегая лишних операций копирования данных из одного буфера в другой. Действия с данными при синхронных преобразованиях могут выполняться по месту (например, преобразование «Производный столбец») или с добавлением нового столбца (например, преобразование «Аудит»). Но и эта группа преобразований состоит из двух подгрупп: потоковые преобразования, самые быстрые из всех преобразований, и строковые преобразования, когда операции выполняются строка за строкой и потому занимают больше времени.

Рисунок 2. Распределение преобразования на синхронные и асинхронные группы

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

И все же переход от одного буфера к другому выполняется быстрее, чем операции ввода-вывода, необходимые для хранения данных на диске между преобразованиями. По этой причине производительность служб SSIS, как правило, очень хорошая. Проблема возникает, когда механизму потока данных приходится обрабатывать большие объемы данных и не хватает памяти для организации буферов. Когда памяти не хватает, механизм потока данных перекачивает данные на диск, и производительность потока данных падает.

Существует два типа асинхронных преобразований — частично блокирующие преобразования и блокирующие преобразования, как показано на рисунке 2. У частично блокирующих преобразований несколько входов, но один выход. После того как преобразование записывает строку в новый буфер, она доступна для следующего преобразования даже когда частично блокирующее преобразование продолжает обработку оставшихся строк. Блокирующее преобразование, наоборот, должно прочитать все строки в конвейере перед записью строк в новый буфер, что препятствует запуску компонентов, расположенных следующих стадиях потока. Следовательно, блокирующие преобразования с большой вероятностью приведут к снижению производительности в потоке данных, если задействованы большие объемы данных.

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

Выполняйте преобразования в исходном запросе. При использовании реляционного источника выполнение преобразований в исходном запросе позволяет выгодно задействовать возможности ядра базы данных, освобождая ресурсы на сервере выполнения пакетов, если он отделен от исходного сервера. Преобразование типов данных, очистка (например, с использованием функций ISNULL или TRIM), статистическая обработка и сортировка — типичные операции, которые можно выполнять в исходном запросе. Этот подход применим только к реляционным источникам. Чтобы воспользоваться им, необходимо назначить свойству IsSorted выхода исходного компонента значение True. Также следует настроить свойства SortKeyPosition для каждого выходного столбца с применением расширенного редактора для исходного компонента.

Отделите операции статистической обработки. Если нужно выполнять различные статистические операции (каждая из которых связана с отдельным местом назначения), но нельзя выполнить статистическую обработку в исходном запросе, можно создать отдельное преобразование «Статистическая обработка» для каждого места назначения вместо создания нескольких выходов для преобразования. Таким образом на последующих стадиях потока обработку агрегатов с меньшим количеством строк можно продолжать, не дожидаясь завершения агрегатов с большим количеством строк. Кроме того, можно установить свойство AutoExtendFactor для независимой настройки памяти для каждого преобразования «Статистическая обработка».

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

Сократите количество строк. Если у потока данных нет реляционного источника, использовать для устранения строк предложение WHERE в исходном запросе не удастся. Но сокращение количества строк — все же важная часть процесса оптимизации производительности, поэтому введите преобразование «Условное разбиение» сразу после исходного компонента для фильтрации данных на как можно более ранней стадии потока данных.

Оптимизируйте кэш для преобразований «Уточняющий запрос». Без использования кэша преобразование «Уточняющий запрос» выполняется медленно, поэтому лучше размещать все строки в памяти в режиме полного кэширования. Но обработка в конвейере заблокирована, пока кэш не загружен. Поэтому следуйте рекомендациям по извлечению исходных данных: сократите число столбцов и строк, чтобы управлять использованием памяти, и применяйте команду SQL для ускоренного получения данных.

Заранее загружайте кэш для преобразований «Уточняющий запрос». Владельцы SQL Server 2008 и более новых версий могут использовать «Преобразование кэша» для предварительной загрузки кэша в отдельный поток данных. Можно даже применять отдельный пакет для загрузки кэша как специального типа необработанного файла, существующего до тех пор, пока он не будет удален. Этот файл кэша загружается в память гораздо быстрее, чем файлы, загружаемые непосредственно из источника OLE DB.

Замените преобразование «Медленно изменяющееся измерение» (SCD) преобразованиями «Соединение слиянием» и «Условное разбиение». Преобразование SCD известно как чрезвычайно медленное при больших измерениях, так как приходится просматривать строку за строкой, проверяя, не существует ли в целевом измерении строка измерения из конвейера. Можно воспользоваться преобразованием «Уточняющий запрос», но память и время, необходимые для загрузки кэша, могут все же оказаться узким местом, снижающим производительность. Другой вариант — использовать преобразование «Соединение слиянием» с предложением LEFT JOIN, чтобы сопоставить сортированные записи источника слева с сортированными записями измерения справа. Затем добавляется преобразование «Условное разбиение» для оценки столбцов, введенных из источника измерения. Если эти столбцы имеют значение null, совпадения нет и строка представляет собой новую запись. Если обнаружено совпадение, обработка типа 1 отделяется от типа 2 с использованием преобразования «Условное разбиение».

Используйте задачу «Выполнение SQL» вместо преобразования «Команда OLE DB». Еще один широко распространенный медленный компонент — преобразование «Команда OLE DB», которое выполняет операции построчно. Если цель преобразования «Команда OLE DB» — обновить таблицу значением, полученным из конвейера, следует быстрее загрузить данные конвейера в промежуточную таблицу, а затем использовать задачу «Выполнение SQL» в потоке управления для выполнения операции UPDATE на основе наборов.

Устранение узких мест компонентов назначения

Иногда производительность пакета снижается из-за компонента назначения. Его производительность может зависеть от внешних факторов, но в то же время полезно усовершенствовать структуру потока данных.

Оптимизируйте назначение «OLE DB». Если в качестве целевого объекта для метода доступа к данным используется таблица или представление, можно выбрать режим быстрой загрузки. В этом режиме выполняется массовая вставка, более быстрая, чем выполняемая в противном случае построчная вставка. Кроме того, производительность повышается благодаря блокировке таблицы (Table Lock). Если в таблицу с кластеризованным индексом необходимо вставить много строк, механизм потока данных должен сначала сортировать все строки в конвейере, а затем выполнить вставку. Производительность повысится, если указать количество строк на пакет, что уменьшает объем данных, сортируемых за один прием. Другой вариант — удалить индекс перед загрузкой данных в место назначения и перестроить его после загрузки.

Используйте назначение «SQL Server» вместо назначения «OLE DB». Если местом назначения является база данных SQL Server, можно получить выигрыш в производительности до 25%, так как механизм конвейера может обойти сетевой уровень при загрузке данных. Но пакет должен выполняться на том же сервере, на котором расположена целевая база данных, и все типы данных в конвейере должны соответствовать типам данных в целевой таблице.

Указывайте типы данных явно. Назначение «OLE DB» может преобразовать типы данных с использованием преобразования «Конвертация данных», но при этом страдает производительность. Можно не добавлять преобразование «Конвертация данных» к потоку данных, выполнив явное приведение типа данных в исходном запросе для реляционного источника или вручную изменив тип данных для каждого столбца в диспетчере соединений с плоскими файлами для источника «Плоский файл».

Оценка эффективности буфера

После устранения узких мест, влияющих на производительность, следующий шаг — оценить эффективность размещения службами SSIS данных в буферах. Для этого необходимо активировать событие записи в журнале BufferSizeTuning, а затем выполнить пакет и проанализировать журнал. Это позволит увидеть, сколько строк записано в буфер. Можно сравнить это значение со свойством DefaultBufferMaxRows, значение которого по умолчанию — 10 000 строк, как показано на экране 2. Если в буфере содержится 1000 строк вместо 10 000, можно изменить DefaultBufferSize для увеличения буфера, чтобы в нем было больше строк. Или же можно удалить столбцы либо уменьшить ширину столбцов, изменив типы данных, чтобы в буфере поместилось больше строк.

Экран 2. Проверка значения свойства DefaultBufferMaxRows

Еще одно свойство, которое можно изменить, чтобы скорректировать производительность, — EngineThreads. Значение этого свойства по умолчанию — 10 в SQL Server 2008 и более новых версиях, но всего 5 в SQL Server 2005. Если в сервере имеется несколько процессоров, это значение можно увеличить. Можно тестировать пакет, постепенно увеличивая это значение, чтобы выяснить, как дополнительные потоки отражаются на производительности.

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

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

Стасия Миснер (smisner@datainspirations.com) — редактор SQL Server Pro, имеет звание SQL Server MVP, консультант, тренер, лектор, автор, специализируется на решениях по бизнес-аналитике с 1999 года