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

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

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

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

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

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

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

Оптимизация по направлению:

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

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

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

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

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

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

Сообщите нам о своем прогрессе.

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

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

• объем памяти
• ЦПУ
• Диск
• Сеть

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

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

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

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

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

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

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

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

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

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

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

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

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