Повышение производительности потока данных
В этом разделе приведены советы по проектированию пакетов служб Integration Services, помогающие избежать общих проблем с производительностью. Кроме того, в этом разделе содержатся сведения по функциям и средствам, позволяющим устранить неполадки с производительностью пакетов.
Настройка потока данных
Повысить производительность задачи потока данных можно путем настройки свойств этой задачи, изменения размера буфера и настройки пакета для параллельного выполнения.
Настройка свойств задачи потока данных
.gif "Примечание") Примечание |
|---|
Свойства, рассматриваемые в этом разделе, должны устанавливаться отдельно для каждой задачи потока данных в пакете. |
Можно настроить следующие свойства задачи потока данных, влияющие на производительность.
Указать местоположение временного хранилища буферных данных (свойство BufferTempStoragePath) и столбцов, содержащих данные объектов типа BLOB (свойство BLOBTempStoragePath). По умолчанию эти свойства содержат значения переменных среды TEMP и TMP. Для хранения временных файлов можно указать другие папки на другом или более быстром жестком диске либо распределить их по нескольким дискам. Можно указать несколько каталогов, разделяя имена каталогов точками с запятой.
Определить размер буфера по умолчанию для задачи, установив свойство DefaultBufferSize, и задать максимальное количество строк для каждого буфера, установив свойство DefaultBufferMaxRows. Размер буфера по умолчанию равен 10 МБ, а максимальный размер буфера равен 100 МБ. Максимальное количество строк равно 10 000.
Установить число потоков, которые может использовать задача в процессе выполнения, установив свойство EngineThreads. Это свойство рекомендует подсистеме обработки потока данных использовать определенное количество потоков. По умолчанию оно равно 5, минимальное количество потоков равно 3. Однако подсистема обработки потока данных не использует потоков больше, чем требуется, независимо от значения этого свойства. Подсистема обработки потока данных может также использовать больше потоков, чем задано этим свойством, если нужно избежать проблем параллелизма.
Указать, следует ли выполнять задачу потока данных в режиме оптимизации (свойство RunInOptimizedMode). Режим оптимизации повышает производительность, удаляя из потока данных неиспользуемые столбцы, выходы и компоненты.
ПримечаниеСвойство с тем же именем, RunInOptimizedMode, может быть задано на уровне проекта в среде Business Intelligence Development Studio, чтобы указать, что задача потока данных запускается в режиме оптимизации во время отладки. Это свойство переопределяет свойство RunInOptimizedMode задач потока данных во время проектирования.
Настройка размера буфера
Подсистема обработки потока данных начинает задачу определения размера буфера, вычисляя предполагаемый размер одной строки данных. Затем она умножает предполагаемый размер строки на значение DefaultBufferMaxRows, чтобы получить предварительное рабочее значение размера буфера.
Если результат больше значения DefaultBufferSize, подсистема обработки потока данных уменьшает количество строк.
Если результат меньше внутренне вычисляемого минимального размера буфера, подсистема обработки потока данных увеличивает количество строк.
Если результат находится между минимальным размером буфера и значением DefaultBufferSize, подсистема обработки потока данных устанавливает размер буфера как можно ближе к предполагаемому размеру строки, умноженному на значение DefaultBufferMaxRows.
В начале тестирования задач потока данных используйте значения по умолчанию для свойств DefaultBufferSize и DefaultBufferMaxRows. Включите ведение журналов в задаче потока данных и выберите событие BufferSizeTuning для определения количества строк, содержащихся в каждом буфере.
Перед началом настройки размера буфера самым важным улучшением является уменьшение размера каждой строки данных путем удаления ненужных столбцов и соответствующая конфигурация типов данных.
При наличии достаточного объема памяти следует использовать меньшее число больших буферов, а не наоборот. Иначе говоря, производительность можно повысить, уменьшив общее число буферов, необходимых для хранения данных, а также установив как можно большее количество строк данных для одного буфера. Для определения оптимального числа буферов и их размеров поэкспериментируйте со значениями свойств DefaultBufferSize и DefaultBufferMaxRows, наблюдая за уровнем производительности и сведениями, получаемыми событием BufferSizeTuning.
Не увеличивайте размер буфер до такого размера, при котором он начнет записываться на диск. При записи буфера на диск производительность ниже, чем при неоптимизированном буфере. Чтобы определить, происходит ли запись на диск, наблюдайте за счетчиком производительности «Выгружено буферов» в оснастке «Производительность» консоли управления (MMC).
Настройка пакета для параллельного выполнения
Параллельное выполнение повышает производительность на компьютерах с несколькими физическими или логическими процессорами. Для поддержки параллельного выполнения различных задач в пакете в службах Integration Services используются два свойства: MaxConcurrentExecutables и EngineThreads.
Свойство MaxConcurrentExcecutables
Свойство MaxConcurrentExecutables является свойством самого пакета. Оно определяет число одновременно работающих задач. По умолчанию оно имеет значение –1, что означает число физических или логических процессоров +2.
Чтобы понять работу этого свойства, рассмотрим образец пакета, в котором есть три задачи потока данных. Если задать для свойства MaxConcurrentExecutables значение 3, то все три задачи потока данных смогут выполняться одновременно. Однако предположим, что каждая задача потока данных имеет 10 деревьев выполнения «источник-назначение». Если свойство MaxConcurrentExecutables имеет значение 3, то нельзя гарантировать, что деревья выполнения внутри каждой задачи потока данных будут работать параллельно.
Свойство EngineThreads
Свойство EngineThreads является свойство задачи потока данных. Это свойство определяет число потоков, которое подсистема обработки потока данных может создать и выполнять параллельно. Свойство EngineThreads применяется одинаково как к потокам источника, которые подсистема обработки потока данных создает для источников, так и к потокам исполнителя, которые подсистема создает для преобразований и назначений. Таким образом, если свойство EngineThreads имеет значение 10, подсистема может создавать до десяти потоков источника и до десяти потоков исполнителя.
Чтобы понять работу этого свойства, рассмотрим образец пакета с тремя задачами потока данных. Каждая задача потока данных содержит десять деревьев выполнения «источник-назначение». Если свойство EngineThreads для каждой задачи потока данных имеет значение 10, все 30 деревьев выполнения потенциально могут выполняться одновременно.
| Примечание |
|---|
Обсуждение работы с потоками выходит за пределы данного раздела. Однако общее правило — не запускать параллельно больше потоков, чем доступно процессоров. Запуск большего количества потоков, чем доступно процессоров, может снизить производительность из-за частого переключения контекста между потоками. |
Настройка отдельных компонентов потока данных
В этом разделе приведены общие рекомендации по настройке отдельных компонентов потоков данных для достижения лучшей производительности. Кроме того, существуют конкретные рекомендации по каждому типу компонентов потока данных: источнику, преобразованию и назначению.
Общие рекомендации
Независимо от компонента потока данных есть две общие рекомендации, которым необходимо следовать для повышения производительности: оптимизация запросов и отсутствие ненужных строк.
Оптимизация запросов
Многие компоненты потока данных используют запросы как для извлечения данных из источников, так и для операций поиска, чтобы создать ссылочные таблицы. По умолчанию запрос использует синтаксис SELECT * FROM <Имя таблицы>. Этот тип запроса возвращает все столбцы исходной таблицы. Так как все столбцы доступны во время разработки, можно выбрать любой столбец в качестве столбца поиска, сквозного или исходного столбца. Однако после выбора используемых столбцов следует изменить запрос таким образом, чтобы в него были включены только выбранные столбцы. Удаление неиспользуемых столбцов делает поток данных пакета более эффективным, так как чем меньше столбцов, тем меньше создаваемая строка. А чем короче строка, тем больше строк может быть загружено в один буфер и тем меньше усилий потребует обработка всех строк набора данных.
Запрос можно создать путем ввода или с помощью построителя запросов.
| Примечание |
|---|
При запуске пакета в среде Business Intelligence Development Studio на вкладке «Ход выполнения» конструктора служб SSIS отображаются предупреждения. Они включают обнаружение любых столбцов данных, которые источник делает доступными для потока данных, но которые впоследствии не используются нисходящими компонентами потока данных. Для автоматического удаления таких столбцов можно использовать свойство RunInOptimizedMode. |
Исключение ненужной сортировки
Сортировка является потенциально медленной операцией, поэтому исключение ненужной сортировки может повысить производительность потока данных пакета.
Иногда источник данных уже упорядочен до использования нисходящим компонентом. Такая предварительная сортировка может происходить при использовании запроса SELECT с предложением ORDER BY, а также при вставке данных в источник в порядке сортировки. Для подобных, предварительно отсортированных исходных данных можно предоставить подсказку о том, что эти данные уже отсортированы, чтобы избежать использования преобразования «Сортировка» в соответствии с требованиями определенных нисходящих преобразований. Например, для преобразования «Слияние» и «Соединение слиянием» требуются отсортированные входные данные. Чтобы предоставить подсказку о том, что данные отсортированы, необходимо выполнить следующие задачи.
Присвойте значение True свойству IsSorted на выходе из вышестоящего компонента потока данных.
Укажите ключевые столбцы, по которым отсортированы данные.
Дополнительные сведения см. в разделе Как отсортировать данные для преобразований «Слияние» и «Соединение слиянием».
Если данные должны быть отсортированы в потоке данных, производительность потока данных может быть повышена за счет использования минимального количества операций сортировки. Например, в потоке данных для копирования набора данных используется преобразование «Многоадресная доставка». Выполните сортировку набора данных один раз перед запуском преобразования «Многоадресная доставка» вместо сортировки выходных данных после преобразования.
Дополнительные сведения см. в разделах Преобразование «Сортировка», Преобразование «Слияние», Преобразование «Соединение слиянием» и Преобразование «Многоадресная доставка».
Источники
Источник OLE DB
При получении данных из представления с помощью источника OLE DB выберите режим доступа к данным «Команда SQL» и введите инструкцию SELECT. Обращение к данным с помощью инструкции SELECT дает более высокую производительность, чем при выборе режима доступа к данным «Таблица или представление».
Преобразования
Советы в этом разделе служат для повышения производительности при преобразованиях «Статистическая обработка», «Нечеткий уточняющий запрос», «Нечеткое группирование», «Уточняющий запрос», «Cоединение слиянием» и «Медленно изменяющееся измерение».
Преобразование «Статистическая обработка»
Преобразование «Статистическая обработка» содержит свойства Keys, KeysScale, CountDistinctKeys и CountDistinctScale. Эти свойства улучшают производительность, позволяя преобразованию предварительно выделять память в размере, необходимом для кэширования данных. Если известно точное или приблизительное число групп, которые будут являться результатом операции Группировать по, задайте свойства Keys и KeysScale соответственно. Если известно точное или приблизительное число различающихся значений, которые будут являться результатом операции Подсчет различных объектов, задайте свойства CountDistinctKeys и CountDistinctScale соответственно.
Если в потоке данных необходимо создать несколько статистических выражений, попробуйте использовать одно преобразование «Статистическая обработка» вместо создания нескольких преобразований. Такой подход, в частности, повысит производительность, если статистическая обработка является подмножеством другой статистической обработки, поскольку преобразование сможет оптимизировать внутреннее хранилище и просматривать входящие данные только один раз. Например, если статистическое выражение использует предложение GROUP BY и статистическое выражение AVG, объединение их в одно преобразование может повысить производительность. Однако выполнение нескольких статистических выражений в пределах одного преобразования «Статистическая обработка» сериализует операции статистической обработки, и таким образом производительность может не повышаться при независимом вычислении нескольких статистических выражений.
Дополнительные сведения см. в разделе Преобразование «Статистическая обработка».
Преобразования «Нечеткий уточняющий запрос» и «Нечеткое группирование»
Сведения по оптимизации производительности преобразований «Нечеткий уточняющий запрос» и «Нечеткое группирование» см. в технической документации Преобразования «Нечеткий уточняющий запрос» и «Нечеткое группирование» в службах SQL Server Integration Services 2005.
Преобразование «Уточняющий запрос»
Сделайте минимальным объем ссылочных данных в памяти. Для этого введите инструкцию SELECT, которая запрашивает только нужные столбцы. В этом случае производительность будет выше, чем при выборе целой таблицы или представления, поскольку в последнем случае возвращается большой объем ненужных данных.
Преобразование «Соединение слиянием»
Преобразование «Соединение слиянием» включает свойство MaxBuffersPerInput, которое предлагает максимальное количество буферов, которые должны быть активны для каждого входного потока одновременно. Однако значение, задаваемое для свойства MaxBuffersPerInput, является лишь рекомендацией. Преобразование «Соединение слиянием» почти всегда оказывается вынуждено увеличивать количество буферов до значения, превышающего рекомендованное. Обычно такое увеличение становится необходимым по следующим причинам.
Чтобы получить с каждого входа достаточно данных для слияния.
Чтобы устранить риск взаимоблокировки между потоками, обрабатывающими данные.
| Примечание |
|---|
Число буферов, используемых преобразованием «Соединение слиянием», может стать очень большим, что в свою очередь вызывает повышенный расход памяти компьютера службами Integration Services. |
Когда преобразование обладает достаточными данными с каждого входа и отсутствует риск взаимоблокировки потоков, преобразование продолжает использовать значение, рекомендованное свойством MaxBuffersPerInput.
Значение по умолчанию для свойства MaxBuffersPerInput равно 5. Такое количество буферов хорошо работает в большинстве сценариев. Чтобы повысить производительность, можно увеличить число буферов. Чтобы сократить нехватку памяти, можно уменьшить число буферов. Однако слишком маленькое количество буферов может отрицательно сказаться на производительности, а значение 0 (ноль) вызывает отключение всего регулирования, и его не следует использовать.
Преобразование «Медленно изменяющееся измерение»
Мастер медленно изменяющихся измерений и преобразование «Медленно изменяющееся измерение» являются средствами общего назначения, удовлетворяющими запросам большинства пользователей. Однако поток данных, формируемый мастером, не оптимизирован по производительности.
Как правило, самые медленные компоненты преобразования «Медленно изменяющееся измерение» — преобразования «Команда OLE DB», выполняющие инструкции UPDATE одновременно только для одной строки. Таким образом, самым эффективным способом повысить производительность преобразования «Медленно изменяющееся измерение» — замена преобразований «Команда OLE DB». Эти преобразования можно заменить целевыми компонентами, сохраняющими все строки для обновления в промежуточной таблице. Затем можно добавить задачу «Выполнение SQL», которая выполняет одну инструкцию Transact-SQL UPDATE одновременно для всех строк.
Опытные пользователи могут сконструировать пользовательский поток данных для обработки медленно изменяющегося измерения, оптимизированного для больших измерений. Обсуждение и примеры по решению этой задачи см. в разделе «Сценарий уникального измерения» в техническом документе Проект REAL. Рекомендации по разработке ETL-решений для бизнес-аналитики (на английском языке).
Назначения
Чтобы добиться максимальной производительности при работе с назначениями, рассмотрите возможность использовать назначения SQL Server и проверьте их производительность.
Назначение SQL Server
Когда пакет загружает данные в экземпляр SQL Server на этом же компьютере, используйте назначение SQL Server. Это назначение оптимизировано для высокоскоростной массовой загрузки.
Проверка производительности назначений
Сохранение данных может занять больше времени, чем ожидается. Чтобы определить, вызвана ли низкая скорость недостаточным быстродействием назначения в обработке данных, можно временно заменить назначение преобразованием «Счетчик строк». Если пропускная способность заметно возрастает, вероятнее всего, замедление скорости вызвано назначением, загружающим данные.
Наблюдение за производительностью пакета
Службы Integration Services включают функции и средства, позволяющие контролировать производительность пакета. Например, сохранение сведений времени выполнения о пакете и счетчики производительности позволяют наблюдать за подсистемой обработки потока данных. Чтобы определить, какие части пакета, в наибольшей степени влияют на производительность, используйте следующие советы.
Просмотр сведений на вкладке «Выполнение»
Конструктор служб SSIS предоставляет сведения как о потоке управления, так и о потоке данных при запуске пакета в среде Business Intelligence Development Studio. На вкладке Выполнение приводится список задач и контейнеров в порядке выполнения, в том числе время запуска и окончания работы, предупреждения и сообщения об ошибках каждой задачи или контейнера, включая сам пакет. На ней также представлены список компонентов потока данных в порядке выполнения и сведения о ходе выполнения в виде процентов завершения и количества обработанных строк.
Чтобы включить или отключить отображение сообщений на вкладке Выполнение, установите или снимите флажок Отчет о ходе отладки в меню Службы SSIS. Отключение отчетов о состоянии способствует повышению производительности выполнения сложных пакетов в среде BI Development Studio.
Настройка ведения журналов в пакете
Службы Integration Services включают несколько различных регистраторов, позволяющих пакетам вести журналы с данными времени выполнения в файлы различных типов или в SQL Server. Можно разрешить ведение журнала пакетам и объектам пакетов, таким как задачи и контейнеры. Службы Integration Services включают большое количество различных задач и контейнеров, и каждый контейнер или задача содержит собственный набор информативных журнальных записей. Например, пакет, содержащий задачу «Выполнение SQL», может создать запись журнала, содержащую список выполняемых задачей инструкций SQL, включая значения параметров инструкции.
Журнальные записи включают такие сведения, как время начала и завершения работы пакетов и объектов пакетов, что позволяет находить медленно выполняющиеся задачи и контейнеры. Дополнительные сведения см. в разделах Ведение журнала выполнения пакетов, Реализация ведения журналов в пакетах и Пользовательские сообщения для ведения журнала.
Настройка ведения журналов для задач потока данных
Задача потока данных предоставляет много пользовательских записей журнала, которые можно использовать для мониторинга и настройки производительности. Например, можно отслеживать компоненты, вызывающие утечку памяти, или следить за скоростью запуска отдельного компонента. Список пользовательских записей журнала и образец их вывода см. в разделе Задача потока данных.
Использование события PipelineComponentTime
Возможно, самой полезной записью журнала является событие PipelineComponentTime. Эта запись журнала сообщает о времени в миллисекундах, которое каждый компонент потока данных тратит на каждом из пяти основных шагов обработки. В следующей таблице приводятся эти шаги обработки. Разработчики служб Integration Services узнают их как преимущественные методы PipelineComponent.
Шаг |
Описание |
|---|---|
Validate |
Компонент проверяется на допустимость значений свойств и параметров настройки. |
PreExecute |
Компонент выполняет однократную обработку перед началом обработки строк данных. |
PostExecute |
Компонент выполняет однократную обработку после обработки всех строк данных. |
ProcessInput |
Компонент преобразования или назначения обрабатывает входящие строки данных, которые передает ему вышестоящий источник или преобразование. |
PrimeOutput |
Компонент источника или преобразования заполняет буферы данных, которые должны передаваться в нижестоящий компонент преобразования или назначения. |
При включении события PipelineComponentTime службы Integration Services записывают в журнал одно сообщение для каждого шага обработки, выполненного каждым компонентом. Следующие записи журнала показывают подмножество сообщений, которое регистрирует образец пакета CalculatedColumns служб Integration Services.
Компонент «Вычисление LineItemTotalCost» (3522) затратил 356 миллисекунд на шаге ProcessInput.
Компонент «Сложение Quantity и LineItemTotalCost» (3619) затратил 79 миллисекунд на шаге ProcessInput.
Компонент «Вычисление средней стоимости» (3662) затратил 16 миллисекунд на шаге ProcessInput.
Компонент «Сортировка по значению ProductID» (3717) затратил 125 миллисекунд на шаге ProcessInput.
Компонент «Загрузка данных» (3773) затратил 0 миллисекунд на шаге ProcessInput.
Компонент «Извлечение данных» (3869) затратил 688 миллисекунд на шаге PrimeOutput, заполняя буферы выхода «Выход источника OLE DB» (3879).
Компонент «Сложение Quantity и LineItemTotalCost» (3619) затратил 141 миллисекунду на шаге PrimeOutput, заполняя буферы выхода «Выход статистического выражения 1" (3621)».
Компонент «Сортировка по значению ProductID» (3717) затратил 16 миллисекунд на шаге PrimeOutput, заполняя буферы выхода компонента «Выход сортировки» (3719).
Эти записи показывают, что задача потока данных затратила наибольшее время на следующие шаги, представленные ниже в убывающем порядке.
Источник OLE DB с именем «Извлечение данных» затратил 688 мс на загрузку данных.
Преобразование «Производный столбец» с именем «Вычисление LineItemTotalCost» затратил 356 мс на вычисление входящих строк.
Преобразование «Статистическая обработка» с именем «Сложение Quantity и LineItemTotalCost» затратило в общей сложности 220 мс (141 на шаге PrimeOutput и 79 на шаге ProcessInput) на вычисление и передачу данных следующему преобразованию.
Мониторинг производительности подсистемы обработки потока данных
Службы Integration Services включают набор счетчиков производительности для наблюдения за производительностью подсистемы обработки потока данных. Например, можно отследить общий объем памяти в байтах, используемый всеми буферами, и проверить, достаточно ли памяти для компонентов. Буфер является блоком памяти, который используется компонентом для сохранения данных. Дополнительные сведения см. в разделе Наблюдение за производительностью подсистемы обработки потока данных.
Внешние ресурсы
Техническая статья, Службы SQL Server 2005 Integration Services. Стратегия повышения производительности, на сайте technet.microsoft.com
Техническая статья, Службы Integration Services. Методики повышения производительности, на сайте technet.microsoft.com
Видеоролик, Настройка потока данных пакетов служб SSIS в выпуске Enterprise, на сайте msdn.microsoft.com
Видеоролик, Основные сведения о буферах потока данных служб SSIS, на сайте msdn.microsoft.com
|
.gif "Значок служб Integration Services (маленький)")
Будьте в курсе новых возможностей cлужб Integration ServicesЖурнал изменений
Обновленное содержимое |
|---|
|
См. также