Как заставить мой процесс SSIS потреблять больше ресурсов и работать быстрее?

Question

У меня есть ежедневный процесс ETL в SSIS, который формирует мое хранилище, чтобы мы могли предоставлять ежедневные отчеты.

У меня есть два сервера — один для SSIS и другой для базы данных SQL Server. Сервер SSIS (SSIS-Server01) — это 8-процессорный, 32 ГБ ОЗУ блок. База данных SQL Server (DB-Server) — это еще один 8-процессорный, 32 ГБ ОЗУ блок. Оба — это виртуальные машины VMWare.

В упрощенном виде SSIS считывает 17 миллионов строк (около 9 ГБ) из одной таблицы на сервере БД, преобразует их в 408 млн строк, выполняет несколько поисков и массу вычислений, а затем объединяет их обратно в 8 млн строк, которые каждый раз записываются в совершенно новую таблицу на том же сервере БД (затем эта таблица будет перемещена в раздел для предоставления ежедневных отчетов).

У меня есть цикл, который обрабатывает данные за 18 месяцев за раз — в общей сложности за 10 лет данных. Я выбрал 18 месяцев на основе своих наблюдений за использованием оперативной памяти на сервере SSIS — за 18 месяцев он потребляет 27 ГБ оперативной памяти. Если больше, SSIS начинает буферизировать данные на диске, и производительность резко падает.

Вот мой поток данныхhttp://img207.imageshack.us/img207/4105/dataflow.jpg

Я используюСбалансированный дистрибьютор данных Microsoftдля отправки данных по 8 параллельным путям для максимального использования ресурсов. Я делаю объединение перед началом работы над своими агрегациями.

Вот график диспетчера задач с сервера SSIS

Вот еще один график, показывающий 8 отдельных процессоров.

Как вы можете видеть на этих изображениях, использование памяти медленно увеличивается до примерно 27G по мере чтения и обработки все большего количества строк. Однако использование ЦП постоянно и составляет около 40%.

Второй график показывает, что мы используем только 4 (иногда 5) процессора из 8.

Я пытаюсь ускорить процесс (он использует всего 40% доступных ресурсов ЦП).

Как сделать этот процесс более эффективным (минимум времени, максимум ресурсов)?

Answer 1

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

Как Вы уже отметили, Вам следует работать над параллелизмом, а не обрабатывать больше данных (месяцев) в одном потоке данных. Вы уже заставили преобразования работать параллельно, но источник и назначение (а также агрегация) не работают параллельно! Так что читайте до конца и помните, что Вам следует заставить их работать параллельно, чтобы использовать мощность вашего процессора. И не забывайте, что Выограничено памятью (невозможно объединить бесконечное количество месяцев в один пакет), поэтому способ («масштабирование») — получить часть данных, обработать ее и поместить в целевую базу данных как можно скорее. Это требует устранения общих компонентов (один источник, одно объединение всех), поскольку каждый фрагмент данных ограничен скоростью этих общих компонентов.

Оптимизация, связанная с источником:

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

Оптимизация, связанная с преобразованиями:

• Вам действительно нужно сделать Union All перед Aggregate? Если нет, взгляните на Destination related optimization относительно нескольких пунктов назначения
• НаборКлючи, KeyScale и AutoExtendFactorдля компонента Aggregate, чтобы избежать повторного хеширования - если эти свойства установлены неправильно, то вы увидите предупреждение во время выполнения пакета; обратите внимание, что прогнозировать оптимальные значения проще для пакетов с фиксированным количеством месяцев, чем для бесконечного количества (как в вашем случае 18 и повышение)
• Рассмотрите возможность агрегации и (от)сводки в SQL Server вместо того, чтобы делать это в пакете SSIS — SQL Server превосходит Integration Services в этих задачах; конечно, логика преобразований может быть такова, что запрещает агрегацию до выполнения некоторых преобразований в пакете.
• если вы можете агрегировать (и сводить/отменять сведение) (например) ежемесячные данные в базе данных, попробуйте сделать это в исходном запросе или в целевой базе данных с помощью SQL; в зависимости от вашей среды, запись в отдельную таблицу в целевой базе данных, построение индекса, SELECT INTO с агрегацией с помощью SQL может быть быстрее, чем выполнение этого в пакете; обратите внимание, что распараллеливание таких действий окажет большую нагрузку на ваше хранилище
• В конце у вас есть многоадресная передача; я не знаю, сколько строк туда попадает, но рассмотрите следующее: запись в место назначения справа (на снимке экрана), затем заполнение записей в место назначения слева в SQL-запросе (чтобы исключить вторую агрегацию и освободить ресурсы — SQL Server, вероятно, сделает это гораздо быстрее).

Оптимизация, связанная с местом назначения:

• использоватьНазначение SQL-сервераесли возможно (пакет должен быть запущен на том же сервере, что и база данных, а целевой базой данных должен быть SQL Server); обратите внимание, что требуется точное совпадение типов данных столбцов (конвейер -> столбцы таблицы)
• рассмотреть возможность установкиМодель восстановленияв Simple в вашей целевой базе данных (хранилище данных)
• распараллелить назначения - вместо объединения всех + агрегата + назначения используйте отдельные агрегаты и отдельные назначения (в одну и ту же таблицу); здесь Вам следует рассмотретьразбиение на разделывашу целевую таблицу и размещение разделов в отдельных файловых группах; если вы обрабатываете данные по месяцам, создайте разделы по месяцам и используйте переключение разделов

Кажется, я так и не понял, в какую сторону двигаться с параллелизмом. Вы можете попробовать:

• размещение нескольких источников в одном потоке данных требует копирования и вставки логики преобразования и назначения для каждого источника
• параллельное выполнение нескольких потоков данных, где каждый поток данных обрабатывает только один месяц
• запуск нескольких пакетов параллельно, где каждый пакет имеет один поток данных, который обрабатывает только один месяц; и один главный пакет для управления выполнением каждого (месячного) пакета - это предпочтительный способ, поскольку вы, вероятно, будете запускать пакет только в течение одного месяца после того, как вы перейдете в производственную среду
• или то же самое, что и предыдущее, но с Balanced Data Distributor и Union All и Aggregate

Прежде чем что-либо делать, вам, возможно, захочется провести быстрый тест: возьмите свой оригинальный пакет, измените его на использование 1 месяца, сделайте точную копию, которая обрабатывает другой месяц, и запустите эти пакеты параллельно. Сравните это с вашим оригинальным пакетом, обрабатывающим 2 месяца. Сделайте то же самое для 2 отдельных 6-месячных пакетов и одного 12-месячного пакета одновременно. Это должно запустить ваш сервер при полной загрузке ЦП.

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

И, наконец, я твердо убежден, что следует устранить проблемы с памятью и целевым вводом-выводом.

Пожалуйста, сообщите нам о ходе вашего дела.

Answer 2

ИспользоватьИсследователь процессовчтобы показать больше использования ресурсов (память и ввод-вывод). Обратите внимание, что график Disk-IO может быть немного обманчивым, поскольку пики на графике часто связаны с возможностями кэширования жестких дисков, поэтому, когда ввод-вывод на диске является узким местом, это не всегда сразу проявляется на графике.

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

Answer 3

(повторно публикую свой первоначальный ответ, не учел BDD)

В конечном итоге вся обработка ограничена одним из четырех факторов:

• Память
• Процессор
• Диск
• Сеть

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

Выбор компонентов

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

Когда асинхронный компонент выходит на арену, конвейер разделяется. Ведро, которое транспортировало эту строку данных, теперь должно быть очищено в новое ведро, чтобы завершить конвейер. Это копирование данных между деревьями выполнения является дорогостоящей операцией с точки зрения времени выполнения и памяти (может удвоиться). Это также снижает возможность для движка распараллеливать некоторые возможности выполнения, поскольку он ожидает завершения асинхронных операций. Дальнейшее замедление операций возникает из-за природы преобразований. Агрегат является полностью блокирующим компонентом, поэтомувседанные должны поступить и быть обработаны до того, как преобразование передаст одну строку в последующие преобразования.

Если это возможно, можете ли вы вынести pivot и/или агрегат на сервер? Это должно сократить время, затрачиваемое на поток данных, а также потребляемые ресурсы.

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

Если вы действительно хотите знать, где тратится ваше время в потоке данных, зарегистрируйте OnPipelineRowsSent для выполнения. Затем вы можете использовать этозапросчтобы разобрать его (после замены sysssislog на sysdtslog90)

Сетевая передача

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

Если это не вариант, попробуйте настроитьРазмер пакетасвойство менеджера подключений на 32767 и поговорите с сетевыми операторами о том, подходят ли вам кадры jumbo. Оба эти совета находятся в разделе Tune your Network.

Я не разбираюсь в счетчиках дисков, но вы должны быть в состоянии увидеть, связаны ли типы ожидания с диском.